Count Blank Cells in Pivot Table Source Data

Count Blank Cells in Pivot Table

Sometimes there are blank cells in a pivot table’s source data. If you try to count blank cells in Pivot Table source data fields, you might run into a problem.

Here are the steps to follow, to show the count of blanks.

Count Missing Names

In this example there’s an Employee field in the source data, and some of the records have no employee name entered.

In the pivot table, you’d like to see how many records are missing an employee name.

No Count Appears

To find the count, your first instinct might be to:

  • add the Employee field to the pivot table’s row area
  • put another copy of the Employee field in the data area, as Count of Employee.

Although this approach seems logical, no count will appear for the blank employee names, as you can see below.

No pivot table count for blank entries
No pivot table count for blank entries

Video: Count Blank Cells in Source Data

[Update] This 3-minute video shows the steps for correctly counting blank items from the source data for a pivot table.

Get the sample Excel file, and written steps, on the Count Blank Cells in Pivot Table page on my Contextures site.

Why There’s No Count

Why doesn’t it work? There’s nothing in those blank Employee cells, so there’s nothing for the pivot table to count.

That’s why no count appears beside the (blank) employee name in the screen shot above.

Count a Different Field

Instead of using the field that has the blank cells, put a different field in the data area, and use it for the count.

For example, if the Units field will always contain data, add Count of Units to the data area, as shown below.

The count of (blank) employee names is now calculated, and you can see that six records are missing an employee name.

Count different pivot field
Count different pivot field

_______________________________

4 thoughts on “Count Blank Cells in Pivot Table Source Data”

  1. I also found that you can sort your name column (in this example) A-Z and then fill all the blanks with a “-” so your pivot has a value to count that still means blank. Obviously only works if you remember to update new blank entries to “-“.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.