Excel – Using Report Filters in Pivot Tables
In a previous blog I showed you how to create a basic Pivot table. We looked at how to add fields to columns, rows and values areas to quickly summarise information from a list of data.
One part of the grid I did not cover last time was the FILTERS segment. This works in exactly the same way as the other segments we use to build our Pivot table, i.e. click and drag field names into it.
As the name suggests, fields dragged into this area can be used as a filter. These can be very useful to keep your Pivot tables relatively simple and can help to reduce the overall size of the Pivot table.
Using some basic data, I will build up a Pivot table, but first without applying a FILTER field.
This produced a Pivot table 118 lines long, including totals and subtotals. It’s OK, it does its job, but perhaps we can improve it or at least simplify it a bit.
By moving the location and subject fields into the FILTERS area, we then get this;
We may have lost some of detail that was visible before but the table is now only 8 rows long. No more incessant scrolling up and down to see results. Now…to see the detail, I can be very specific in what I see by clicking on the drop downs next to my FILTER fields.
I can now see and select any one or more of the locations that appear in my location field. Note that I have ticked the SELECT MULTIPLE ITEMS option at the bottom of the list in order to be able to pick more than one location. If I don’t tick it, then I can only pick one location at a time. Personally, I would always tick this option whether I am picking one or several items.
Once you’ve made your choice, click on OK.
Here, I have picked just one location (Birmingham) and below I have chosen three;
Note that when multiple items are selected that’s all it tells you in the drop down – MULTIPLE ITEMS. If you need reminding of which ones you picked just click on the drop down again.
Also note that with each FILTER applied the size of my Pivot table remains unchanged. This won’t be the case every time, but at least you shouldn’t end up with the table being 10 rows and then jumping to 150 rows with the next filter, but this will be entirely dependent on your data and how you structure your base Pivot table.
If you are going to apply lots of filters, you can control how these are laid out above your Pivot table. If I keep on adding more and more FILTERS I get this;
There’s nothing really wrong with this, but if you prefer you can distribute the filters over several columns and rows which might suit you better.
To customise this, click in your Pivot table and then go to the OPTIONS or ANALYZE tab (depending on which version of Excel you have) in the PIVOTTABLE TOOLS tabs and click on OPTIONS.
In the OPTIONS window, select the LAYOUT & FORMAT tab. Here, there are two settings you can use;
- Display fields in report filter area
- Report filter fields per row
The first option determines whether it fills rows before moving across to the next column (DOWN, THEN OVER) or fill across the columns first, then move to the next row (OVER, THEN DOWN).
Use the second option to set the number of fields you want to see per row. So going back to our earlier example, with five FILTERS, if I set the options to OVER, THEN DOWN using three fields per row we get this;
As with any settings, there is no right or wrong, only what suits you and the number of FILTERS you want to create.
As a word of advice, rather than anything else, try to stick to top level fields to have in your FILTERS. Although I have put date in the example above, this is generally not a good field to use in FILTERS as it is too granular. I used to manage inventory across a number of warehouses across Europe, and good top level FILTERS were things like product line, warehouse number, consumable/non-consumable flags, rather than part number. It meant I could quickly narrow down my output to consumable items for a specific product line, in a specific warehouse. As with everything, the choice is yours.
So, other than enable you to filter your Pivot table results, what else can you do with FILTERS?
Lurking under the OPTIONS button, is something called SHOW REPORT FILTER PAGES.
To get to this, make sure you click on the arrow next to OPTIONS rather than on the OPTIONS button itself. Note that this option is greyed out if no fields are present in the FILTERS area.
You should then see a window with a list of any FILTERS you have in place.
(I’ve reverted back to the original Pivot table I created….simpler to view)
Pick any one of the FILTERS shown and click on OK.
Then check out the new tabs that appear in your workbook!
What this does is create a separate worksheet for each item in the selected filter. In this example, it has created a separate sheet for each course. Had I selected location, then a separate sheet would have been created for each location.
Each sheet contains a Pivot table in its own right. But why do this, create a whole load of new sheets when the information is already nicely packaged in a single Pivot table? Let me ask you this…are the people you send your Pivot tables to happy using Pivots and do they know what they are doing when using drop downs etc. within those Pivots? Probably not. By sending the information like this, all your users have to do, is go to the tab that interests them and view it. No need to apply filters, or click on anything that is likely to cause panic or confusion. This may seem like a damning indictment of the average Excel user, but you don’t know what you don’t know, and if Pivots are a mystery, it’s easy to click or even worse, double click on something and a new sheet appearing or alter the structure of the Pivot with the user not knowing why something happened or how to correct it.
So that’s FILTERS for you. Useful to quickly narrow down output in a Pivot and also making Pivot data more accessible to non-Pivot users.