Home > Pivot Table > > Count Blanks

Pivot Table Count Blanks Source Data

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

Introduction

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.

Video Timeline

• 0:00 Introduction
• 0:12 Data with Blank Cells
• 1:30 Change the Pivot Table
• 2:12 Pivot Table #2
• 2:57 Fix the Pivot Table

Ex 1: Blank Cells in Product Data

In the product sales data shown below, cell C7, in the Qty column, is blank.

• There are 9 entries in the Product column, with 2 orders for Pens
• There are 8 entries in the Qty column, with 1 number for Pens

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:

1. Select a cell in the orders table
2. On the Excel Ribbon's Insert tab, click Pivot Table
   • The table name should automatically appear in the Table/Range box
   • For the location, choose New Worksheet
   • Leave the Data Model box unchecked
   • Click OK
3. In the PivotTable Fields list, check the Product field, to add it to the Rows area

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:

1. In the PivotTable Fields list, check the Qty field, to add it to the Values area (Data area)
2. Qty appears in the pivot table as Sum of Qty
3. Right-click a cell in the Sum of Qty column
4. Point to Summarize Values By, then click Count

Because one of the Qty cells is blank, it is not counted in the pivot table layout.

• There are 2 orders for Pens, but the count of Qty is 1.

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:

1. In the PivotTable Fields list, uncheck the Qty field, to remove it from the Values area
2. Drag another copy of the Product field into the pivot table, and place it in the Values area
3. Because Product is a text field, it will automatically summarize as Count.

Because none of the Product cells are blank, the count includes all the orders.

• There are 2 orders for Pens, and the count of Product is 2

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:

1. East
2. West
3. Central

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.

• How many records have a Blank cell for the District name?

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:

• First, create a new pivot table from the sales data.
• Next, to see the district names, put the District field into the Rows area of the pivot table
• Next, because you want to count District names, you might put that field into the pivot table's Values area.
• Because District is a text field, it will automatically summarize as Count.

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.

• To begin, remove the Count of District field from the pivot table

Then, follow these steps, to get a correct District count, including blank cells:

• First, find a different field in the source data, such as the Order ID, where none of the rows have a blank cell.
  • Tip: If there isn't a field you can use, see the steps in the next section, for adding a new column
• Next, drag that field into the pivot table's Values area
• If the field had text values, it will automatically show a Count
• If the text had all numeric values, it will automatically show a Sum:
  • In the pivot table, right-click a cell for the new field
  • Point to Summarize Values By, then click Count

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.

• In that situation, you could add a new column, with a unique heading, such as Count
• In that column, enter this simple formula, in the first data rows:
  • =1
• If the source data is formatted as a named Excel table, the formula should automatically fill down, to the last row in the table.

After you create the new column, follow these steps:

• Drag the new field into the pivot table's Values area
• Then, in the pivot table, right-click a cell for the new field
• Point to Summarize Values By, then click Count

Change Summary Function

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:

• If the field contains numbers, Sum will be the default
• If the field contains text or blank cells, Count will be the default

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:

• Right-click on a cell in the Value field that you want to change.
• In the pop-up menu, click Summarize Values By
• Next, click on the Summary Function that you want to use
• There are 6 function names showing, and a More Options command:
  • Sum, Count, Average, Max, Min, Product

Summary Functions - More Options

If you want a function that is not shown in the pop-up menu:

• Click the More Options commmand
• The Value Field Settings dialog box opens, showing the Summarize Values by tab
• Scroll down the list, and click on the function that you want to use
• The additional functions in this list are:
  • Count Numbers, StDev, StDevp, Var, Varp
  • Alos, for OLAP-based pivot tables, you might see Distinct Count
    • (Power Pivot, or pivot tables with data in Data Model)
• Click the OK button, to apply that function to the selected Value field

Count Function

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.

• Pivot Table Count is not like the COUNT worksheet function.

The pivot table Count function counts the following types of data:

• text
• numbers
• errors

NOT COUNTED: Blank cells in the source data are NOT counted.

Count Numbers Function

The Count Numbers summary function counts all the underlying numbers in the Values area.

• Result is the same as using the COUNT function on the worksheet.
• Blank cells, errors, and text are not counted.

In the screen shot below, you can see the source data for a small pivot table

• Count formula in cell C12 calculates count of the numbers in the Qty column (column C).
  • =COUNT(C2:C12)

In cell C4, the cell value is 20, entered as text

• Text in cell C4 is not counted in the formula result shown in cell C12
• Text in cell C4 will not be counted by a pivot table's Count Numbers function

Count Numbers vs. Count

In the pivot table shown below, the Qty field has been added twice to the Values area.

• In column B, the summary function is Count Numbers, which does not count text
  • Grand Total is 7
• In column C, the summary function is Count, which counts text and numbers
  • Grand Total is 8

Get the Workbook

Count Blanks: To follow along with the Count Blank Entries video, download the Count Blanks in Pivot Table sample file. The workbook is in xlsx format, and does not contain any macros.

More Tutorials

Summary Functions

Running Totals

Clear Old Items in Pivot Table