Excel Slicers Trick

Controlling multiple reports hack

Slicers can control multiple pivot table reports in Excel. The problem is that if you use a slicer on one sheet to filter a report on another sheet it is difficult to see that a filter is in place. This technique also comes with a warning – see bottom of post.

To enable a slicer to filter multiple reports you right click the slicer and choose Report Connections.

In the dialog that opens tick the reports that you want the slicer to filter.

Now that the slicer is controlling multiple reports, the problem is the other reports do not have the slicer in place so it is not obvious that a filter has been applied to the report.

To get around this you can copy a slicer from one sheet and paste it in another. The slicers will synchronise, so changing one slicer will amend the other slicer. This allows you to place slicers on each of the sheets that you are filtering. See warning below.

This also makes it easier to amend the filter on any of the other report sheets and have that flow through to all the report.

This trick also applies to Timeline slicers.

Note: this applies to slicers used with power pivots as well.

WARNING:

Make sure the Multi-Select option (3 ticks icon – top right) is the same on all slicers – if not inconsistent filtering may occur. This icon is either on (line around it) or off (no line around it) see image below. If you don’t have the icon (older version) there is no issue.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.