From the Not Just Numbers blog:

Regular readers and clients will know that I’m a big fan of PivotTables. If you format your data into a table format, you can create all sorts of reports quickly, easily and flexibly using them.

In this (short) post, I want to highlight a little trap to watch out for when you filter a PivotTable – particularly if you are filtering out items, such as blanks for example.

On any of the Row, Column or Report Filter fields you can apply a manual filter by ticking (or unticking) individual entries.

In this field I have filtered out the blanks by unticking “(blank)” and leaving everything else ticked. This is exactly the kind of situation that can lead you to fall into the trap.

To understand the problem, you need to think about how a PivotTable works.

The entries in the PivotTable come from the data itself. So this list only includes the Job Descriptions that currently appear in the data (and possibly some that used to, depending on your settings), so the list above may not be comprehensive in future, as new job descriptions appear in the data.

Let’s say a new job description of “Clean Fridge Freezer” appears in the data.

Our intention above was to exclude (blank), but Excel doesn’t know this. We could just as easily have meant to only include “Clean Oven”, “Clean Oven and hob”, “Clean Range” and “Clean Range and Fridge Freezer”.

So should “Clean Fridge Freezer” be included in the filter or not? We have not given Excel any clue on this, but Excel needs to do something with it, so there is a default position.

By default, Excel assumes that we have pro-actively ticked the things we want to include – which seems a perfectly reasonable assumption, but if that is true, then “Clean Fridge Freezer” hasn’t been ticked, so should not be included.

In our example where we were essentially “unticking to exclude”, as opposed to “ticking to include”, we need to tell Excel this. Fortunately, this is a simple process.

We just need to change a setting in the Field Settings for this field (Job Description in our example), which can be accessed by right-clicking on the Field itself in the PivotTable (or left-clicking on the field in bottom section of the Field List) and choosing Field Settings.

We then need to tick the box to “Include new items in manual filter”:

Once this is ticked, new items will be included by default (just for this field). Note that this setting will not change the selection currently in place, so if you hadn’t ticked it when you set the filter up and only discover this when a new item isn’t included, you will still need to manually tick the new item. However, all future new entries will be automatically ticked.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.