Excel 2016 – How to exclude (blank) values from pivot table

If you need to create a pivot table chart on a range that includes blank rows you’ll end up with a (blank) label.

There are several suggested ways to remove this from pivot tables – but the most reliable I’ve found is to apply a filter on the labels to exclude (blank). The filter will stay applied even when the data is refreshed – automatically excluding (blank).

The steps below show how I do this.

How to filter pivot table columns by label

1. In your pivot table, click on the down down button next to ‘Row Labels’
2. 
3. Click on ‘Label Filters’ -> ‘Does Not Equal’
4. 
5. Enter (blank) in the box and click ‘OK’
6. 
7. The (blank) items will now automatically be excluded from the pivot table and pivot table chart.
8. 

Reference:

• https://superuser.com/questions/815798/have-pivot-chart-show-only-some-columns-in-pivot-table
• https://blogs.technet.microsoft.com/danstolts/2014/10/how-to-hide-replace-empty-format-blank-values-with-an-empty-field-in-an-excel-pivot-table-without-using-filters-step-by-step/