Background:
This week I created a couple of Excel Dashboard’s for a project that I am working on. As a part of the that, I decided to include slicers for these Excel Dashboards. And the reason I did that was because data in multiple pivot tables needed to be sliced with one click. In this blog post, I’ll show you steps to connect an Excel slicer to multiple pivot tables:
Steps for Excel 2010:
1. Based on your requirements, decide if multiple items on a dashboard (excel sheet) need to be affected with one click
2. Now, for the purpose of this blog post, let’s assume that you have two Pivot Tables like shown below:
Note: the Two Excel Pivot Tables are created by connecting to same cube. And the slicer comes from the same cube.
3. And what if you need to slice data in both pivot tables by one common slicer? Let’s say we want to slice the data in the two pivot tables that we have by Product Category – here’s what you do (assuming that the tables are related in your data source and slicing makes sense)
4. click on any of the pivot table and you’ll see PivotChart Tools in the toolbar
5. Switch to Analyze Tab & click on insert slicer & Select the field that you want as slicer & click OK
6. you’ll see a slicer on your sheet now:
7. Let’s format the report to make it look better:
#1: move the slicer such that it does not overlap any of the pivot tables.
#2: In this case, I want to move the slicer to the top of the sheet and change the slicer to have 5 columns. Here’s how you can do that:
select the slicer > from the toolbar > slicer tools > column
#3 I also changed the slicer style from the slicer tools to match its color palette with that of the Pivot Tables:
8. Note that the slicer is connected to just ONE pivot table. Let’s connect it to both pivot tables
9. From Slicer Tools options > Pivot Table connections > check all pivot tables that you want the slicer to be connected to:
10. Done! Test your slicers, with one click you should be able to see that the data gets sliced in both pivot tables:
#1: Sliced by Bikes
#2: Sliced by Accessories
That’s about it for this post. Your comments are very welcome!
Related Posts:
Comparing Slicers in Excel 2010 to Standard PivotTable Filters
Hi, there is an good article.