Excel Outside the Box
April 2012
Bob Umlas has been an Excel MVP longer than anyone has been an MVP. He is the guy who will come up with all of the obscure techniques. If someone says something can’t be done in Excel, Bob will find a way. This book collects 54 of his favorite advanced Excel Techniques.
Add to Cart: $9.99 »- 172 Pages
- Publisher: Holy Macro! Books
- ISBN: 978-1-61547-010-5
- PDF ISBN: 978-1-61547-203-1
Think that you know everything that there is to know about Microsoft Excel? Guess again. If you're ready for the next level of Excel, then "Excel Outside the Box" is for you! 'Outside the Box' is an in-depth look at 54 amazing techniques from Excel MVP Bob Umlas. Have a look at the table of contents.
-
Chapter 1 - Techniques 1
- A Dilemma with Relative References 1
- Build a Formula with a Formula 5
- Combining a List of Values into One 10
- Comparing Worksheets 12
- Creating a Series of the 15th of the Month and the Last Day of the Month 14
- Match Colors 19
- Reorganization of Data 21
- Scrolling Text 24
- Sequences 31
- Variable Linked Cell 33
-
Chapter 2 - Pivot Tables 37
- 11- Pivot Table Anomaly and Workaround 37
- Reverse Pivot Table 42
-
Chapter 3 - Features 46
- 13- Conditional Formatting 46
- Data Validation on Steroids 48
- The "Justify" Command 52
- Unusual Text to Columns 56
-
Chapter 4 - Formulas 59
- 17- Interesting Formula using MATCH and OFFSET 59
- Using LARGE (or SMALL, etc) on Non-contiguous Ranges 61
- Using SUMPRODUCT for Filtered or Hidden rows 62
- VLOOKUP with Multiple Answers 66
- Reversing a list 68
-
Chapter 5 - Array Formulas 70
- 22- Alphabetizing without Sorting (an Array-Formula) 70
- An Odd Combination 74
- Finding the Latest Date from a String with Embedded Dates 75
- Calendar with One Formula (Array Entered, of Course!) 79
- Comparing Lists 88
- Finding the Last Cell Using a Formula 91
- Getting Sums from a Text String 94
- Parsing Information Without Breaking in the Middle of a Word (an Array Formula) 95
- Using an Array Formula to Extract a Part Number 97
- VLOOKUP with Multiple Answers – Another Look 101
-
Chapter 6 - Charts 103
- 32- Dynamic Range Names and Charts 103
- More Dynamic Charting 105
- Show a Chart Only When the Data is Complete 110
-
Chapter 7 - VBA 115
- 35- Ensure a Cell Contains a Value Before Saving File 115
- A VBA User-Defined Function for Getting Sums from a Text String 117
- Delete Defined Names in One VBA Statement 120
- Ensuring Macros are Enabled 122
- Getting a List of Sheet Tabs in a "Table of Contents" 124
- Inserting Totals with VBA 125
- Limiting Cells a Person Can Select Without Protecting the Worksheet 130
- Many Buttons, One VBA Procedure 130
- Multiple Matches 134
- Overriding Cell Calculations 141
- Quick Way to Put Sequential Numbers into a Range Using VBA 150
- Trimming and Cleaning all Cells on a Worksheet 151
- Using a Demo of a File – Not Allowing it to be Used More than X Times 153
- Using Excel Ranges With the VBA Join Method 154
-
Chapter 8 - Miscellaneous 156
- 49- What Really is the Issue with Dates? 156
- Getting Sums From a Text String, Revisited 157
- Useful Filtering Technique 159
- Referencing in Long Worksheets 160
- Interesting Conditional Formatting 162
- Highlighting Cells Which are Values Which Should Be Formulas, Without Using VBA 167
- A Piece of Silliness 172
