Excel Outside the Box

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

1- A Dilemma with Relative References 1
2- Build a Formula with a Formula 5
3- Combining a List of Values into One 10
4- Comparing Worksheets 12
5- Creating a Series of the 15th of the Month and the Last Day of the Month 14
6- Match Colors 19
7- Reorganization of Data 21
8- Scrolling Text 24
9- Sequences 31
10- Variable Linked Cell 33

Chapter 2 - Pivot Tables 37

11- Pivot Table Anomaly and Workaround 37
12- Reverse Pivot Table 42

Chapter 3 - Features 46

13- Conditional Formatting 46
14- Data Validation on Steroids 48
15- The "Justify" Command 52
16- Unusual Text to Columns 56

Chapter 4 - Formulas 59

17- Interesting Formula using MATCH and OFFSET 59
18- Using LARGE (or SMALL, etc) on Non-contiguous Ranges 61
19- Using SUMPRODUCT for Filtered or Hidden rows 62
20- VLOOKUP with Multiple Answers 66
21- Reversing a list 68

Chapter 5 - Array Formulas 70

22- Alphabetizing without Sorting (an Array-Formula) 70
23- An Odd Combination 74
24- Finding the Latest Date from a String with Embedded Dates 75
25- Calendar with One Formula (Array Entered, of Course!) 79
26- Comparing Lists 88
27- Finding the Last Cell Using a Formula 91
28- Getting Sums from a Text String 94
29- Parsing Information Without Breaking in the Middle of a Word (an Array Formula) 95
30- Using an Array Formula to Extract a Part Number 97
31- VLOOKUP with Multiple Answers – Another Look 101

Chapter 6 - Charts 103

32- Dynamic Range Names and Charts 103
33- More Dynamic Charting 105
34- 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
36- A VBA User-Defined Function for Getting Sums from a Text String 117
37- Delete Defined Names in One VBA Statement 120
38- Ensuring Macros are Enabled 122
39- Getting a List of Sheet Tabs in a "Table of Contents" 124
40- Inserting Totals with VBA 125
41- Limiting Cells a Person Can Select Without Protecting the Worksheet 130
42- Many Buttons, One VBA Procedure 130
43- Multiple Matches 134
44- Overriding Cell Calculations 141
45- Quick Way to Put Sequential Numbers into a Range Using VBA 150
46- Trimming and Cleaning all Cells on a Worksheet 151
47- Using a Demo of a File – Not Allowing it to be Used More than X Times 153
48- Using Excel Ranges With the VBA Join Method 154

Chapter 8 - Miscellaneous 156

49- What Really is the Issue with Dates? 156
50- Getting Sums From a Text String, Revisited 157
51- Useful Filtering Technique 159
52- Referencing in Long Worksheets 160
53- Interesting Conditional Formatting 162
54- Highlighting Cells Which are Values Which Should Be Formulas, Without Using VBA 167
55- A Piece of Silliness 172

About the Author

Bob Umlas works for a major tax and accounting firm, using Microsoft Excel® eight hours a day, writing custom applications for staff and clients; he has been using Microsoft Excel since version 0.99 - on a Macintosh Computer! Bob was a Contributing Editor for 'Inside Microsoft Excel' - a monthly publication packed with tips, tricks and techniques showing folks how to work smarter and more efficiently with Microsoft Excel - for many years where he has had more than 300 articles published on subjects ranging from Beginner Topics to Advanced Macros, using virtually every aspect of Excel. Bob Umlas is currently a Microsoft MVP and has been, each year since 1995!

Most recently, Bob has led five sessions at an Excel User Conference in Atlantic City on Tips & Tricks, Array formulas, VBA, Formulas, and Userforms and is also the author of “This isn’t Excel, it’s Magic!” which is available from http://www.iil.com/publishing as well as from Amazon.com.

Are you ready to step to the challenge?

Product Details
Paperback: 172 Pages
Publisher: Holy Macro! Books
Print ISBN: 978-1-61547-010-5
Electronic ISBN: 978-1-61547-203-1
Print Price: $24.95 USD
Electronic Price: $14.95 USD
 

Purchase the Print Version $24.95