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.


Related Articles

8 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?

    1. Go to the pivot table options and select from the totals & filters tab the “allow multiple filters per field” option. Then you can do both 😉

  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 to Mark Martin Cancel reply

Your email address will not be published. Required fields are marked *