This short video shows how to build an Excel pivot table that calculates a correct count, even if there are blank cells in the source data table.
Count Blanks in Pivot Table
If there are empty cells in the source data, a pivot table can’t count those cells correctly.In the short video below, I’ll show you how to avoid that problem, by choosing a pivot field that has data in every row.
There are 2 examples in the video, and the second example shows how to get a count when the item name says “(blank)”.
Video: Count Blanks in Pivot Table
Watch this short video to see two examples of counting in a pivot table, when there are blank cells in the source data. The video timeline, and more details, are below the video.
Video Timeline
Here’s the video timeline, so you can find the steps:
- 0:00 Introduction
- 0:11 Ex 1: Data with Blank Cells
- 0:24 Make a Pivot Table
- 1:23 Change the Pivot Table
- 1:51 Ex 2: Pivot Table Count (blank)
- 2:30 Fix the Pivot Table
Ex 1: Blank Cells in Data
A pivot table can’t count blank cells, so it’s important to choose a pivot field that has data in every row.
In the first example from the video, the pivot table should show a count of orders, for each of the products. One of the orders for Pens has a blank cell in the quantity column.
If the Qty field is used for the count in the pivot table, the blank cell is not counted. As a result, the Pens product shows only 1 order.
Fix the Count
Usually we put a numeric field in the pivot table values area, but that doesn’t count correctly in this case, because the numeric field has blank cells.
Instead, use a text field or numeric field that you know will not have any blank cells.
If we put the Product field into the Values area instead, the count of orders is correct, because that field doesn’t have any blank cells.
Ex 2: Get Count for (blank) Item
In the other example from the video, we need a count of Districts, even if the district name cell is blank.
To get the correct count, we can’t use the Districts field. Instead, use another field, such as the Order ID, where none of the rows have a blank cell.
Get the Workbook
To get the Excel workbook, with the Count Blanks in Excel Pivot Table example, go to the Pivot Table Count Blanks page on my Contextures site.
The zipped file is in xlsx format, and does not contain any macros.
_____________________________________
Count Blanks in Excel Pivot Table
_____________________________________