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.
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.
_______________________________
Very helpful, thank you.
Solved my problem, thanks.
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 “-“.
Thank you so much! A straight-forward explanation that solved my problem