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. Click on ‘Label Filters’ -> ‘Does Not Equal’
  3. Enter (blank) in the box and click ‘OK’
  4. The (blank) items will now automatically be excluded from the pivot table and pivot table chart.

Reference:

Related Articles

6 comments on “Excel 2016 – How to exclude (blank) values from pivot table

  1. This works BUT: I want to create a (top 10) AND (exclude blanks). You can’t select both at the same time in the filters. Any way to do this without deselecting the blank option in the filters? I can’t do this anyway if source data is empty as it automatically ‘selects all’. Any other way to exclude blanks?

  2. Hi

    This works but I want to create a table with:
    a) top 10
    b) does not contain blanks

    by this way I can only choose either.
    Any way to do both? (not by deselecting the blank)

  3. Hello my loved one! I want to say that this post is awesome, great written and include almost all vital infos. I’d like to see extra posts like this.

Leave a Reply

Your email address will not be published.