Note: This is my first Excel post! I have improved since then, both in my blogging and Excel skills. I’ve made some modifications to the post, but the first paragraph remains for sentiment’s sake.
Allow me to explore a new writing topic — Excel! This is my most-used tool at work, and I have acquired lots of tips and tricks that I want to share. It amazes me how there is always a new trick in Excel to discover. I even found one today, in this hiatus between the holidays. The process is detailed below.
Subscribe to the newsletter for more Excel tips and tricks. Plus, your free ebook!
Pivot Tables are my favorite tool in Excel. I use them so much that I often include multiple pivot tables per worksheet. This can be annoying to maintain when many filters are involved. To avoid multiple filters per worksheet, use the Slicer tool.
In our example, we have weekly sales at an ice cream shop. There are 3 pivot tables to analyze sales by store: by day, by product, and by sales associate.
This gives an easy overview of different sales metrics, but it is a pain to toggle between stores. If you are looking at Store 1’s sales and want to switch over to Store 2, you need to redo three filters.
The Slicer tool will let us filter all three pivot tables with the click of a button. To do this, select any of the three Pivot Tables, then select Analyze on the home ribbon. Click on “Insert Slicer.”
You will get an “Inster Slicers” menu. This is asking which of the attributes you would like to include in the slicer. The slicer is basically just a fancy filter. Because we want to filter all of our Pivot Tables by store, select store. Hit OK.
This slicer offers an easy way to toggle your filter selection between stores. But notice that it is only working on the Pivot Table you had assigned.
To use the slicer across multiple Pivot Tables, click inside your Slicer. Then on the home ribbon, go to Options and “Report Connections.”
A table will appear asking you which of the Pivot Tables should connect to the Slicer. Right now, only one is checked. Select all three, and hit OK.
The Slicer will now filter all Pivot Tables on this worksheet.
This is a great tool for streamlining multiple Pivot Tables. For more on Slicers, visit this Office Online tutorial.
How was my first post on Excel? Your thoughts are appreciated.
Leave a Reply