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.
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.