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".
I used to do this by setting the "label filter not equal to" and leaving the criteria blank but this is much better - thanks
ReplyDeleteIt's amazing what you don't spot in something you use daily!
jim
Pleased to be of assistance Jim. I come across things all the time that I've been doing the long way for years!
DeleteExcellent tip ! Thanks.
ReplyDeleteUnfortunately I have had this happen to me. You can add your new data to the data table, and refresh your pivot table, but you also need to address any pivot table filters to ensure your pivot is still accurate. If there are new/different entries in any columns you are using as pivot table filters you may very well have an inaccurate pivot table on your hands until you revisit the filter selections. Or, you can just tick this handy setting for each filter when you 1st set up your pivot...love!
ReplyDeleteGood and useful Tip !
ReplyDelete