Count Blank Entries in Pivot Table

It’s easy to count things with a pivot table – just drop a field into the pivot table’s Values area, and change the summary function to count.

Sometimes you don’t even need to change the function to Count – it automatically uses the Count function if the field contains text cells or blank cells.

Watch for Blanks

If you’re using a pivot table to count items, be careful to use a field in which there is an entry in every row in the source data. Excel doesn’t count empty cells when summarizing in a pivot table, so you might not get the result that you expected.

For example, if the source data has the District name missing in some records, we can use a pivot table to count those records. Put the District field in the Row area, and another copy of the District field in the Values area.

The pivot table correctly counts the records where there is a District name, but can’t count the records where the District name field is blank.

Video: Count Blank Entries in Pivot Table

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.

Fix the Blanks Problem

To correctly count records where the District field is blank, use another field in the Values area. In this example, there is a date field in the source data, and it has an entry in every row.

When you add the Date field to the Values area, you can see that it counts correctly, and shows how many records have a blank District field.

Next, we can remove the District field from the Values area, and change the heading for the Count of Date field.

The pivot table shows the correct counts, and the headings are easy to understand.

_________________