Change All Pivot Charts With One Filter

Slicers were introduced in Excel 2010, and they make it easy to change multiple pivot tables with a single click. In earlier versions, you can use programming to change the report filters in multiples pivot tables.

AlexJ has shared a nice technique for using a single Report Filter to update multiple pivot tables. This technique uses slicers, but they are stored on a different worksheet, so they don’t take up room on the dashboard.

Single Report Filter

Here is a screen shot of AlexJ’s dashboard, with two filters at the top of the sheet. The pivot charts are all connected to the same Date slicer, so when you select a date, all the charts change.

pivotonefilteralexj01

You can use more than one filter with this technique, and I’ve added a Market filter too. The third chart is not connected to the Market filter – it always shows the results for all markets.

pivotonefilteralexj02

Note – If you’re using more than one master filter, AlexJ warns us to leave a few blank rows between them. Otherwise, you’ll see a message that pivot tables can’t overlap one another.

Show Details Feature

Just remember that there is a problem with the Show Details feature, when you use slicers. If you don’t have the slicer fields in the pivot tables, you might see all the data, instead of the filtered data, when you double-click on a value cell.

With this technique though, the pivot tables are stored on a separate sheet, which could be hidden from the users, so that reduces the problem.

Watch the Video

To see the steps for creating the pivot charts, and connecting them through a slicer, please watch this short video tutorial.

__

Download the Sample File

To see how AlexJ’s technique works, you can visit my Contextures website, and download the sample file. On the Sample Excel Files page, go to the Pivot Tables section, and look for PT0031 - Change All Pivot Charts With One Filter.

The technique will work in Excel 2010, and later versions, where slicers are available.

____________________

2 comments to Change All Pivot Charts With One Filter

  • Mani

    Hi Con-textures Team ,

    I Want to Know that there is any Formula is There for Adding Auto Number after two blank Rows .
    For Best Understanding i have given a link of image ..

    Image Preview :- http://s2.postimage.org/k509mbfhl/sshot_1.png

    As you have noted the numbers are on the url cells after two blank cells ..like that i want the forumal to enter it auto..Thank You in Advance ..

  • Yvonne

    Hi!

    When I use this slicer filter for all my pivot charts, it causes the chart formatting to change removing even the titles of the graphs. How can I stop this from happening?

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>