Sometimes there are blank cells in a pivot table's source data, and you might want to show a count of those blank cells in the pivot table. 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 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.
_______________________________
