Wiring Slicers

Learn Excel from MrExcel podcast, episode 2011 - Wiring Slicers!

Hey, I'm podcasting the entire book, go ahead click the “i” up in the top-right hand corner for the playlist!

Welcome back to MrExcel netcast, I'm Bill Jelen. Well we've been building this dashboard a week, I just added this extra Pivot table here. The beautiful thing about slicers is, so initially the slicer only controls the first Pivot table but, unlike Report Filters which used to require a Macro to control Pivot tables, slicers can be tied to all Pivot tables in the workbook. Alright, so Slicer, Options, we can go to Report Connections, now these words might have been different in Excel 2010, it might've been called Slicer Connections or something like that. So we go into Report Connections, say that this slicer region is tied to all of these Pivot tables. If you only have 2 Pivot tables, you could also do it by going to the Pivot table and Analyze, again, I know it was called Slicer Connections in 2010, it was actually under the Slicer dropdown. You would open to find Slicer Connections, and say that you want this Pivot table also hooked up to that slicer. Alright, so either way in this case, since I have more Pivot tables and slicers, I'm going to use Report Connections to make sure that they are all hooked up.

Right, this is a beautiful thing now, I can choose from the slicers, the data is updating, I can see all 3 Pivot tables, and of course, just extend this, keep adding more Pivot tables over and over and over. To make sure that we don't get any zeroes there, Ctrl+1, will go into Custom, and for positive numbers we want nothing, for negative numbers we want nothing, for zero numbers we want nothing, and then for everything else we want text! Alright, and that should make sure that if we have everyone, they show up, if we have less than everyone, we don't get any zeroes there, right. So now, you see the concept, I have a dashboard, I have slicers, give this to your manager with the touchscreen, he can touch the slicers, and the reports get up, it's a beautiful, beautiful trick.

Now tomorrow, tomorrow we're going to take this dashboard, and publish this dashboard not as an Excel workbook, but publish it out to the web! My great friend Rob Collie from Power Pivot Pro says that Excel spreadsheets don't get enough respect, right? But that if you can, all of a sudden, turn yourself from an Excel Data Analyst, into a Web Application Creator, that looks great on your resume, and it's worth more money according to Rob, alright. So all of this stuff is in the “MrExcel XL” book, click that “i” in the top-right hand corner, great way to impress your boss. Short episode here today: one set of slicers can run multiple Pivot tables, it's beautiful for dashboards. You need to go to each Pivot table and choose Filter Connections, or you go to each slicer and choose Report Connections, right, either way, whichever makes sense, and things are great.

Alright, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!