When I teach how to develop dashboards in Excel, I’m often asked if the same visualizations and interactivity can be recreated for PowerPoint. My answer is always the same: use Excel as your PowerPoint (why? because the answer is also no). In other words, rather than pasting reports onto slides, use the spreadsheet instead as the presentation. Allow managers and stakeholders to ask questions and change inputs to see results in real time (PowerPoint can’t do that). I talk a lot about this in my Excel Dashboards Pro course, which I’m releasing in January of next year.
Click Here to Join the Waitlist or go to Excel.TV/Training
For now, I wanted to show you a really neat trick that can help you put together a presentation in Excel. You can see how it works below. The entire thing is driven by slicers, and best of all, it can be made quickly without any macro coding.
Notice I’ve clicked on the first, “Intro,” slice. I can click on the second and third slices to bring up other views for my custom report.
Again, because this uses built-in Excel features, the lift in creating this is actually pretty small. And, for the coders out there, this method presents the advantage of not having to show and hide ranges of cells with code.
How it’s done
So let’s investigate how this is made. I’ve moved my spreadsheet out of “production” mode and back into “development.” That means I’ve unhidden everything. You can see a birds eye view of the mechanics in the spreadsheet below.
Notice I’ve inserted an Excel table on the far left. Excel 2013 and later allows us to slice on values within the table. So, above, I’ve created a column filled with repeating values. These repeating values create the “view” upon which the slicer slices. (Say “slicer slices” five times fast.) I’ve created three sets of repeating values which you can see manifest into the three slicer options.
The effect of this slicing filter is to hide Excel rows that don’t match the selection criteria. So, for instance, we can repeat something like “1 – Intro” and “2 – Sales Chart” multiple times (I have a formula create this caption, but you could just as easily hand-jam it in yourself). I can then place associated content next to these repeating labels. The first and last of these repeating values always demarcates where the view begins and ends.
When that slice is selected, the Excel table is told to only show those values that match the selection criteria–everything else is hidden. In the image below, you can see that the selected slices matches that those values show in the Label column of the table.
The final task then is hide that Excel table and place the associated details where you would expect them to be. Some shapes, by default, do not move and size with cells. That means they won’t hide when you select a different slicer. For those shapes, you’ll have to change their associate Size & Properties options.
Advantages and Disadvantages
This method is easily put together. It also affords you some interesting design considerations. For instance, you’re able to create a header, content space, and footer. The header and footer stay the same no matter what content you’re looking at. If this were interactive, you could use the footer to display the real time results of changes made to inputs. You can also use this mechanism for an input wizard.
On the other hand, Slicers do not allow you to limit users to only one slice at a time (at least, not without some macro code). This could get awkward if the user attempts to select the first and third slices, for instance. That’s not a big deal for me. Still, as I often say, all Excel design choices (aka “tips” like the one here) come with costs. You’ll need to weigh the value of design-speed with the Excel experience and data sophistication of your audience.
Click here to get the download file
Want more?
You’re in luck. I’m proud to announce that I’ve been working on an Excel course over the last year, which I will be releasing in January called Excel Dashboard Pro. This course has tips like this and so much more! My courses are not canned check-the-box items you don’t need. Rather, I’ve curated my best, time-saving tips on using Excel to make complicated models and dashboards. That’s right: I show you the easy way to create hard stuff.
My work has been used in banking, professional services, and even by NATO Training Command and the Pentagon. I’ve written two books on Excel development and have held the Micosoft MVP designation for four years. In my day job, I run data science projects for non-profits and social organizations.
You can sign up now to get on the wait list. We’re doing something really cool for people who join the waitlist. First, you’ll get a free download that will show you how to make an interactive dashboard immediately. Next, you’ll be able to sign up to the course before it launches officially. Finally, you’ll receive additional tips and downloads not publicly available. The waitlist is free to join, and I’d love to hear your thoughts on the tips we provide.
- All Excel LOOKUPs Explained - May 26, 2020
- How to: Power Query File From Folder - April 21, 2020
- Oz’s Excel Tip: Keep a Workbook for Random Data in Excel - January 23, 2020
