Excel Outside the Box Unbelieveable Excel

Designed with the Excel guru in mind, this handbook introduces advanced and creative solutions, and hacks to the software's most challenging problems. Through a series of more than 50 techniques, tables, formulas, and charts, this guide details processes that may be used in any Excel application, across all disciplines. Creative approaches for building formulas within formulas, pivot tables, conditional formatting, and mastering array formulas are just some of the numerous challenges explained. Other higher level solutions discussed include using VBA macro code to override cell calculations, solve for sums from a text string, and trimming and cleaning all cells on a worksheet. This is the all-encompassing resource for advanced users of Excel wanting to learn more techniques to broaden and empower their use of Excel.

Bob Umlas is an Excel maven who has been awarded the title of Microsoft Excel MVP in recognition of his ongoing contributions to various online news groups. He is the author of This Isn’t Excel, It’s Magic and a contributing editor to Inside Microsoft Excel who has written more than 300 articles on all aspects of the program. He was a coleader of New York City’s PC Excel Special Interest Group for more than a decade. He lives in Palisades, New York.

Introduction	p. xiii
Techniques	p. 1
A Dilemma with Relative References	p. 1
Build a Formula with a Formula	p. 5
Combining a List of Values into One	p. 10
Comparing Worksheets	p. 12
Creating a Series of the 15th of the Month and the Last Day of the Month	p. 14
Match Colors	p. 19
Reorganization of Data	p. 21
Scrolling Text	p. 24
Sequences	p. 31
Variable Linked Cell	p. 33
Pivot Tables	p. 37
Pivot Table Anomaly and Workaround	p. 37
Reverse Pivot Table	p. 42
Features	p. 46
Conditional Formatting	p. 46
Data Validation on Steroids	p. 48
The "Justify" Command	p. 52
Unusual Text to Columns	p. 56
Formulas	p. 59
Interesting Formula using MATCH and OFFSET	p. 59
Using LARGE (or SMALL, etc) on Non-contiguous Ranges	p. 61
Using SUMPRODUCT for Filtered or Hidden rows	p. 62
VLOOKUP with Multiple Answers	p. 66
Reversing a list	p. 68
Array Formulas	p. 70
Alphabetizing without Sorting (an Array-Formula)	p. 70
An Odd Combination	p. 74
Finding the Latest Date from a String with Embedded Dates	p. 75
Calendar with One Formula (Array Entered, of Course!)	p. 79
Comparing Lists	p. 88
Finding the Last Cell Using a Formula	p. 91
Getting Sums from a Text String	p. 94
Parsing Information Without Breaking in the Middle of a Word (an Array Formula)	p. 95
Using an Array Formula to Extract a Part Number	p. 97
VLOOKUP with Multiple Answers - Another Look	p. 101
Charts	p. 103
Dynamic Range Names and Charts	p. 103
More Dynamic Charting	p. 105
Show a Chart Only When the Data is Complete	p. 110
Vba	p. 115
Ensure a Cell Contains a Value Before Saving File	p. 115
A VBA User-Defined Function for Getting Sums from a Text String	p. 117
Delete Defined Names in One VBA Statement	p. 120
Ensuring Macros are Enabled	p. 122
Getting a List of Sheet Tabs in a "Table of Contents"	p. 124
Inserting Totals with VBA	p. 125
Limiting Cells a Person Can Select Without Protecting the Worksheet	p. 130
Many Buttons, One VBA Procedure	p. 130
Multiple Matches	p. 134
Overriding Cell Calculations	p. 141
Quick Way to Put Sequential Numbers into a Range Using VBA	p. 150
Trimming and Cleaning all Cells on a Worksheet	p. 151
Using a Demo of a File - Not Allowing it to be Used More than X Times	p. 153
Using Excel Ranges With the VBA Join Method	p. 154
Miscellaneous	p. 156
What Really is the Issue with Dates?	p. 156
Getting Sums From a Text String, Revisited	p. 157
Useful Filtering Technique	p. 159
Referencing in Long Worksheets	p. 160
Interesting Conditional Formatting	p. 162
Highlighting Cells Which are Values Which Should Be Formulas, Without Using VBA	p. 167
A Piece of Silliness	p. 172
Index	p. 173
Table of Contents provided by Ingram. All Rights Reserved.

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

FREE SHIPPING

Rent More, Save More! Use code: BBRENTAL

5% off 1 book, 7% off 2 books, 10% off 3+ books

Excel Outside the Box Unbelieveable Excel Techniques from Excel MVP Bob Umlas

Summary

Author Biography

Table of Contents

Supplemental Materials