Filters and Slicers

Filters

Filters, filter data in pivot tables, so that the pivot table only ‘sees’ data in line with the filter settings that we as the user have set. As it can only ‘see’ certain data, it can only aggregate the information that it sees.

In previous lessons we filtered the pivot table to only include data for 2016 data only and then added in 2017 data later on, so let’s remind ourselves of how that filter setting works.

First, open the pivot table field list by left clicking on the pivot table to make it active and then right clicking and selecting “Show Field List”. Then, let’s say that we only want to look at the sales from a certain Country of the world and then from a certain Sales Segment.

Firstly, move the Country field to the Filters pane and then move the Segment field to the Filter pane as well. We’ve now got both of those options above the pivot table as drop down boxes and we can select the option that we’d like.

Let’s say we’d then like to look at Mid tier sales in New Zealand. I’d click on the drop down box for Country above the Pivot Table and then select New Zealand from the list. To select Mid Tier, click on the Segment drop down box and select Mid Tier from the list. The pivot table then shrinks to show all of the sales that have been made in New Zealand in the Mid Tier segment. You can put in as many filters as you have fields so don’t think you’re restricted to one or two.

Other Filters

When your Pivot Table is in a tabular form rather than a compact form, you can also see in the top left hand corner of the table a number of drop down arrows next to each of our Rows and Columns fields. These are also all filters. These can also be use to filter the data that the Pivot Table is ‘seeing’ to perform its aggregation calculations.

However, if you’re using a compact form, then the Pivot Table will only show a filter against one of the Fields in your Pivot Table; not all of them.

Slicers

The second type of filter is called a “Slicer”. These are essentially buttons that we can put into the report around the Pivot table for people who have not produced the report, who don’t perhaps create pivot tables and are less familiar with how they work. This type of person sometimes doesn’t know how the filter drop down options work but still wants to be able to filter the pivot table to only show certain information.

 The Slicers are simply buttons that allow the filtering process without the pivot tables filter dropdowns.  We can put slicers in instead of filters and then those people can use the pivot tables themselves with little guidance.

To insert a slicer, click on the Pivot Table and select the PivotTable Analyze tab. In the Filter section of the ribbon, then select Insert Slicer.

A menu then pops up showing us all of the fields in our dataset, from which the Pivot Table can be created.

Let’s imagine that we wanted to put in options for any reviewer to filter the data by the Country and the Segment, as we did earlier, as well as the year. I’ll select Insert slicer and then check the boxes for segment, country and year and then click “Ok” That then brings up three boxes with buttons labelling the possible options for each field. The slicers can then be easily resized.

Now any reviewer can simply click these buttons to review any combination of settings that they like.

To clear filter selections, just click the button in the top right hand corner of the slicers and the pivot table will then return to the initial Pivot Table settings.