ms-excel

Introducing Slicers in Pivot Tables

We have already discussed how to create pivot tables. If you missed that, I would suggest you check out our previous post.

Slicers can make your Excel look very fancy, but that’s not the end of it. Slicers help you add a new dimension to your data and help you read and present your reports better.

Sample data

Let’s say I have this data of a particular sales team.

And this data ranges up to more than 50 rows.

From the previous post about pivot tables, we already know how to create a pivot table like this.

Now, let’s say the report needs to be split further according to the north, south, east or west zone that they operate from. Therefore, another vertical gets introduced in the report.

Now, any guesses where will the fourth element go?

There are two ways of handling this.

  • Pivot filter:

After having dragged the employee, month and total sales columns under the row label, column label and values respectively, there will be one more label called filters. Drag the sector column under this label.

Now, you will see that the pivot table also has the table filter added on to it in the cells A1 and B1.

Select the drop down menu in cell B1 and select the desired option to further filter your report.

Select the zone that you want to see and press the OK button. You will now see that the report is shown only for that particular zone.

  • Create slicer:

The function is pretty much the same, but a slicer will add a little more zing to your reports when you present them.

To add a slicer:

  • Click on any cell in the pivot table
  • Click on the insert tab
  • Click on slicer

A new window will open up from which you will have to select the column based on which the slicer has to be made. In this case, select “sector” and press the OK button.

Now, you will notice that it has created a new window with the elements of “sector” column as buttons.

Every button is the filter that will realign your report based on your selection. Right now all the sectors are selected. Click on a particular sector to see the revised sales report according to your selection.

So there you have it. Instead of selecting filters from the drop down menus in the regular old fashioned way, we bring in the filters in the form of selectable buttons called slicers. Just click on the filter that you need and the report will realign based on the filter. You don’t have to limit yourself to one slicer. If you have multiple filters you can add more than one slicer in your pivot table.

Looking to sharpen your financial functions in excel? Join our top-rated professional diploma in financial trading & investment today!

Join 12+ million students who already have a head start
Sign up today and get 4 weeks free!
No commitments. Cancel at any time.

Shaw Academy