Cool Excel Sh*t

Foreword

I actually learned Microsoft Excel on the Macintosh in 1986 or so, using version 0.99! When the real version (1.00?) shipped, I read the manual (yes, Excel came with manuals then) from cover to cover. Six times. Especially in order to learn Data Tables. I just didn’t get it. (Okay, so I’m a geek. Actually, I once heard that you’re a geek if you double-click the TV remote)!

Around 1993 I received the nickname Excel Trickster from one Will Tompkins, a real Excel Guru. I got that because during a seminar he was hosting in Washington, I believe, for what he called the Excel SWAT team, he was showing his then famous Tompkins methodology – a macro structure using Excel 4 style macros (before VBA existed in Excel) which was quite sophisticated. He showed a line of code which used a range name, a label, and he wanted to show us the code at that label. So, he used F5(Goto), and we saw literally many hundreds of defined names which he had to laboriously scroll through to get to the one he was looking for. After he had done this about 4-5 times, I raised my hand and said, You know, you can get to that label directly. Just press Ctrl/[. He tried it and was flabbergasted! So, he tried it again. Then he remarked that I just saved him about 2 hours every day scrolling through his defined names!

When I also told him you could return to the location you were just at by using Ctrl/], he called me the Excel Trickster, and that nickname has stuck with me ever since. So, thank you, Will.

I’ve been an Excel MVP for 25 years – 1993-2018, and I currently lead an online Master Class in Excel which is 12 3-hour sessions and a VBA class which is 2 3-hour sessions. I have presented at various global events (EIEFreshTalk and GlobalExcelSummit) with 1750 and 9400+ participants, respectively!

Acknowledgments

I’d like to thank my wife, Judy, for her continued support of my pursuing my Excel studies, former and current Excel MVPs for their continued eye-opening ideas upon which I have built some of these ideas.

Several ideas in this book were first discussed in the 2007 book, This Isn’t Excel, It’s Magic, published by IIL Publishing in New York. That book features 111 Excel tips and is still available from the publisher.

FORMULAS

An amazing formula to SUMIF the visible rows

This isn’t so much a tip/trick (it’s a bit advanced), but worth visiting!

Let’s look at a worksheet containing the source data:

And let’s say this goes down to row 123. I inserted a slicer for this data:

…then I cut this slicer and pasted it to another sheet:

It’s fairly straightforward to get the right formula in cell B1 to add up all the values from the source sheet, depending on the choices in the slicer:

=SUBTOTAL(109,’Supporting Data’!C:C)

This is because the choices in the slicer will hide/show the appropriate rows and the SUBTOTAL(109,… will summarize the unhidden rows.

But how can you further break it down by service so the numbers in B5:B9 can be determined? The #VALUE! Error in B2 is a first attempt. We need to pretty much use the