“Excel Outside the Box” is for you if you’re ready to advance in Excel. The book “Outside the Box” by Excel MVP Bob Umlas takes a close look at 54 incredible strategies.
Unbelievable Excel Techniques from Bob Umlas
PDF e-Book: Excel Outside the Box
This manual, which was created with the Excel guru in mind, presents sophisticated and original fixes as well as workarounds for some of the program’s most difficult issues.
This manual describes procedures that may be applied to any Excel application, across all disciplines, including more than 50 approaches, tables, formulas, and charts.
A few of the many difficulties discussed are pivot tables, conditional formatting, creating formulae inside of formulas, and learning array formulas.
A worksheet’s cells can be cleaned and trimmed using VBA macro code, which can also be used to override cell calculations to find sums from text strings.
For experienced Excel users who wish to learn more skills to enhance and empower their use of Excel, this is the all-inclusive resource.
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
172 Pages
Publisher: Holy Macro! Books
ISBN: 978-1-61547-010-5
PDF ISBN: 978-1-61547-203-1Price: 9.99$
Be more productive at Excel and impress your BOSS!!
