In an Excel pivot table, discover how to count blanks from source data cells. Solutions for problem trying to count empty cells in data source, along with filled cells. Step-by-step video, written steps, screen shots, sample Excel workbook to download
When you add a field to the pivot table's Values area, 11 different functions, such as Sum, Count and Average, are available to summarize the data.
Just like the Excel COUNT function, that you can use on a worksheet, the Excel Count summary function does not count blank cells from the pivot table's source data.
This page shows how you can avoid this limitation, by choosing the best field from the source data, when you need to include a count of blank cells.
Video: Pivot Table Count Blank Cells
In a pivot table, the Count function does not count blank cells. So, if you need to show counts that include all records, choose a field that has data in every row.
This short video shows two examples, and there are written steps below the video.
Ex 1: Blank Cells in Product Data
In the product sales data shown below, cell C7, in the Qty column, is blank.
Count Product Orders
Using that product sales data, we want to create a pivot table that shows the number of orders for each product.
To get the pivot table started, follow these steps:
Test 1: Count of Qty Field
Usually, we put numeric fields into the Values area of a pivot table.
To see what happens with this data, follow these steps:
Because one of the Qty cells is blank, it is not counted in the pivot table layout.
Test 2: Use Different Field
Instead of using the Qty field for the count, use a different field, to fix the problem
To get the count of all orders, even if the Qty cells are blank, follow these steps:
Because none of the Product cells are blank, the count includes all the orders.
TIP: Rename the Count of Product column as "Orders"
Ex. 2: Blank Text Cells in Source Data
This is the second example from the video, and the goal is to show a count of orders from each District
There are 3 Districts where orders are sold:
In the source data, most records show the name of the District where the order was sold. However, for some of the order records, the District name cell has been left blank.
In the pivot table, we want to see a count of those records too.
Add District to Pivot Table
Here are the steps for counting the orders from each district, including any orders with a blank cell in that column:
In the pivot table, you can see a count for each district name, but the (blank) district doesn't show a count. The Count function in a pivot table can't count blanks.
Get Correct Count for Blank Data Cells
To get the correct count for district orders, we can’t use the Districts field.
Then, follow these steps, to get a correct District count, including blank cells:
Now, with a different field in the pivot table, the (blank) district shows the correct count.
Excel is counting items in the other column, for every row where the district name cell is blank
(optional) Add a New Column
In some data sets, there might not be any existing column that you can trust, to have no blank cells.
After you create the new column, follow these steps:
When you add a numerical field to the pivot table's Values area, Sum or Count will be the default summary function.
The default function can't be changed -- it is applied based on the field's contents:
More Summary Functions
After a field has been added to the pivot table values, you can follow these steps, and select a different summary functions:
Summary Functions - More Options
If you want a function that is not shown in the pop-up menu:
In a pivot table, Count is the default summary function when fields with non-numeric or blank cells are added to the Values area.
The pivot table Count function is like the COUNTA function, that you'd use on an Excel worksheet.
The pivot table Count function counts the following types of data:
NOT COUNTED: Blank cells in the source data are NOT counted.
The Count Numbers summary function counts all the underlying numbers in the Values area.
In the screen shot below, you can see the source data for a small pivot table
In cell C4, the cell value is 20, entered as text
Count Numbers vs. Count
In the pivot table shown below, the Qty field has been added twice to the Values area.
Last updated: September 13, 2022 2:49 PM