laptop with code on the screen and books, paper and pens beside

November 16, 2018 - By James French

Hello everyone, this is my second blog about Pivot Tables in Excel, if you missed the first one you can find it here. In this second edition of the blog, I would like to talk about using the “Slicer” tool with Pivot Tables in Excel.

Note: This is where it is useful to have built your pivot table on a table of data, so all you need to do is right click your table and refresh to get the updated information.

Excel Slicer Example

We have added a few extra items and some higher quantities to the previous data set so we have a little more to work with.

We are going to add a slicer to our Table. To add a slicer, click anywhere in the pivot table so the “Pivot Table Tools” section appears on the ribbon. Click on the Analyze tab and insert slicer. Which will create the insert slicers dialog. For now, select Item and we will see a new slicer appear.

Pivot Table Tools

(An example of the Item Slicer is below)

Excel Slicer Example

Now by clicking on any of the items, you can view the item, the customer who bought the item, and how many of the item you have on hand. Another benefit of these slicers is you can have as many of them as you want, and they all work together, for example: say an item was sold by 10 or 20 customers, and you only wanted to see how many 500ML Reusable bottles were purchased by Dollarworld and no one else, or any 2 or 3 customers.

Extra Tip: If you double click on a value in the pivot table, for example, the sum of Qty On Hand it will drill down and show you each line that makes up the sum. This is useful when your data isn’t adding up the way you thought it should, or if you’re having difficulty with formula outputs.

Table Example

So, the next time you’re looking at your data see if you would benefit from a Pivot table, and how a slicer can work for you! Thanks for checking out my blog and I will continue the series with another simple to use but powerful Excel tool next time!






Share this story: