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.
In my other blog about the ADVANCED FILTER I looked at how you set one up and build up your criteria.
Now I am going to take a step further and look at how to create criteria ranges i.e. a between…and… type filter. Everything from the other blog still stands in terms of basic set up and creating a variety of criteria. However, when you want to set a filter based on a range of values you need make a minor change to your criteria table.
Continuing with the example from the previous blog, I want to pick out people in the IT department who earn between 25000 and 28000. If you have used Access or other database systems you would use something like ‘Between 25000 and 28000’ but unfortunately this does not work in Excel.
Apply this and the filter will simply return an empty list. So we need a slightly different tactic. Copy the column heading you are basing your data range on. In this example, I want to select a range of salaries so I will copy the ‘Salary’ heading. You can either insert this to one side of the current column, or, and this is my personal preference, paste it to the end of the heading row of your criteria table.
Now it is just a case of building up your criteria, adding a start value in one cell, and an end value in the copied heading column. So in my example I would enter >25000 in one column, and on the same row I would enter <28000 in my new ‘salary’ column.
Once again, you can make these as complicated as you like, and this is the beauty of the ADVANCED FILTER, you can have multiple criteria row each with different values and ranges.
The filter above would be looking for the following;
- People in IT AND who earn between £25-£28,000 OR…
- People in IT AND who earn between £18-£21,000 OR…
- People in Training AND who earn between £23-£26,000
When you select your criteria range make sure you include the ‘new’ column. Don’t worry that the criteria table is wider than the original table, Excel will be searching for column headings, so as long as they match, no problem. Continue to add as many ‘new’ columns as required.
So that’s how to build range filters, but the ADVANCED FILTER also has an option to pick out UNIQUE values.
When extracting unique values from a list, there are two things to remember
- Only select the column you want to extract the unique values from – not the entire table.
- Ignore the criteria table – you don’t need it.
Let’s say I want to get a list of all the unique job titles:
Open the ADVANCED FILTER and from the dialog box, decide whether you want to filter in place or copy to another location. Again, my personal preference for this is to display the output in another location away from the data table.
Then select the data you want to filter out unique values from. In this case I want all the job titles. IGNORE the criteria range box.
If you selected COPY TO ANOTHER LOCATION then select a cell from where you want to display the filtered list.
Finally, make sure you tick the UNIQUE RECORDS ONLY option. And click on OK.
You will now see a list of unique values…
But not quite…..Notice we have administrator twice. So what happened?
When I selected the job titles I highlighted only the job titles…which would seem like a logical thing to do, but Excel treats the first value in the selected data as the column heading. SO in this case the first line contains ‘administrator’ so assumes this is a heading and displays it at the top of the list. To avoid this, simply include the column heading when you select your range and then you will get the correct answer;
And there you have it….the ADVANCED FILTER.
You are probably used to using AUTOFILTER and in the more recent versions of Excel, this has improved a lot with the option to filter or sort by colour or conditional format icon as well as give you a number of pre-set filter conditions that are data sensitive, so the wording of the filter conditions change depending on whether you are looking at dates, numbers or text.
Using AUTOFILTER you can build up some fairly clever filters but there are limitations. With each new filter you apply you are creating an AND type filter where ALL filter conditions have to be met. Yes, you can add in an OR condition within each filter, but you are limited to just the one per column filter.
For example, I want to filter out staff in either the IT or Training departments – easy enough. I then want to filter out people whose salaries fall between certain values. Again simple enough, but it applies the salary range to BOTH of the selected departments. What if I want to see people in IT who fall in one salary range and trainers who fall in a different salary range? I could run two separate filters but I want to see this in one table, not two separate outputs.
This is where your ADVANCED FILTER comes in.
First of all copy all your headings and paste them somewhere on the same worksheet. Personally, I prefer to paste them a few rows below my data table. The rows below your copied headings are where you are going to build the criteria for your ADVANCED FILTER.
Now type in your criteria: under each copied heading type text, dates numbers etc. which are the values you want to filter out. So, taking our example above again, I would type in IT in one cell and Training in the row directly below that.
Similar to our AUTOFILTER, each column I type a value into, equates to an AND condition. Values entered on subsequent rows represent OR. So by typing IT in one row, and Training in the next row I am creating an OR type filter – show me people in either IT OR Training.
Once your criteria are set up, it’s time to run the ADVANCED FILTER. Click back into your data table – this is a MUST. Go to the DATA tab, and click on the ADVANCED button in the SORT & FILTER group.
This will open the following window;
Decide whether you want to apply the filter directly to the data table or to show it as a separate output elsewhere in the SAME worksheet. Personally I prefer to do it in place, but your choice. Excel should automatically work out the LIST RANGE (that’s why you click into your data table before running the filter). Then select your CRITERIA RANGE i.e. the values and the column labels (you MUST include the column labels here) from the table you pasted below your data list. You only need to select the columns that contain criteria, although selecting the entire table will not cause any problems.
Click on OK, your table will now only display the filtered information.
So if I want to extend my filter to include salaries then I can add criteria in the SALARY column of my criteria table. Now, this is where you need to be a bit careful. If I enter a criteria such as <25000 on the same line as the IT department cell but don’t put anything against Training, it will filter anyone in IT with a salary under 25000 but will display all salaries for training as no criteria have been applied. By writing this I would be looking to filter the following information:
- People in IT AND who have salaries under 25000
- OR people in Training
If I want to set a salary criteria for the Training department then I would need to add something on that row too. This is where the possibilities start to dramatically increase with the ADVANCED FILTER because unlike the standard AUTOFILTER I can apply different criteria against each item filtered, or even multiple criteria.
In this example I am filtering for:
- People in IT with a salary greater than 25000 OR…
- People in Training who do not have the job title of “Trainer” OR…
- People in Finance who’s surname begins with “P”
So in your criteria you can use all the normal operators (>, <, >=, <=, <>) as well as wildcards (*, ?). The possibilities are endless!
A word of caution though. If you apply a filter and yet you still see the entire table, either your filter is not precise enough or, most likely, you have included a blank line in your CRITERIA RANGE. A blank line basically says show me data with no special criteria – in other words show me everything. If that happens, just modify the CRITERIA RANGE references and re-run the filter.
In another blog I’ll show you how to create filter RANGES as well as how to extract unique values out of a list using the ADVANCED FILTER.