Pivot Table Count Blanks? How to Show Them

Yes, pivot tables can count blanks, but they do not do it automatically. By default, a pivot table ignores empty cells when counting, so a blank value simply does not appear in the Count of a field. To see and count blanks, you must either replace the empty cells with a placeholder text (like “(blank)”) or use a calculated field that converts blanks to a countable value.

Formula

The core idea is that a pivot table counts non-empty cells only. If you want to count blanks, you need to make them non-empty. The simplest formula approach is to create a helper column in your source data with a formula like:

=IF(A2=””, “(blank)”, A2)

This replaces every blank in column A with the text “(blank)”. When you then drag that helper column into the pivot table’s Values area, it counts each “(blank)” cell as a regular value. The formula works for text, numbers, and dates alike. For a calculated field inside the pivot table, you could use something like =IF(FieldName=””, 1, 0), but calculated fields in pivot tables cannot reference empty cells directly, so the helper column is the more reliable method.

Worked Example

Suppose you have a list of 10 sales orders, and the “Region” column has two blank cells. You want a pivot table that shows how many orders are in each region, including the blanks.

1. Add a helper column to your data, say column D, with the formula =IF(B2=””, “(blank)”, B2) and copy it down.
2. Create a pivot table from the full data range (including the helper column).
3. Drag the helper column (D) to the Rows area.
4. Drag the Order ID (or any non-blank field) to the Values area. It will default to Count.

Result: you will see a row for each region, plus a row labeled “(blank)” with a count of 2. That is the count of blanks.

Why Pivot Tables Ignore Blanks by Default

Pivot tables are designed to summarize data, and empty cells are generally treated as “no data” rather than a category. When you drag a field into the Values area, Excel or Google Sheets counts the number of non-empty cells. A blank cell is not a value, so it is excluded. This is consistent with how COUNT functions work in spreadsheets: COUNT ignores blanks, while COUNTA counts non-empty cells. The pivot table’s Count function behaves like COUNT, not COUNTA.

This behavior is useful when you want to avoid counting missing data, but it becomes a problem when a blank is meaningful, such as a missing response in a survey or an unassigned category.

How to Count Blanks in Excel and Google Sheets

The exact steps differ slightly between Excel and Google Sheets, but the principle is the same.

In Microsoft Excel

• Add a helper column next to your data with the formula =IF(A2=””, “(blank)”, A2).
• Refresh the pivot table (right-click > Refresh).
• Drag the helper column to the Rows area and another field to Values (Count).
• Alternatively, use the “Show items with no data” option (right-click the pivot table > Field Settings > Layout & Print > check “Show items with no data”) – but this only shows items that exist in the field’s list, not true blanks.

In Google Sheets

• Create a new column with the formula =IF(A2=””, “(blank)”, A2).
• Build the pivot table with the helper column in Rows and any field in Values (COUNTA).
• Google Sheets also has a “Show as” option, but the helper column remains the simplest.

Common Problems and Mistakes

One common mistake is trying to use a calculated field to count blanks. Calculated fields in pivot tables operate on the aggregated values, not individual cells, so they cannot check for empty cells. Another mistake is forgetting to refresh the pivot table after adding the helper column – the new column won’t appear until you refresh. Also, be careful with spaces: a cell containing a space character (” “) is not blank to Excel, so it will be counted as a non-blank. To catch those, use =IF(TRIM(A2)=””, “(blank)”, A2).

Another issue: if your blank cells are in a numeric field, the helper column will convert them to text, which may affect sorting or grouping. You can mitigate this by using a placeholder that sorts as you want, or by using a separate “blank flag” column with 1/0 values and then summing that.

Alternatives to a Helper Column

If you cannot modify the source data, you can filter the pivot table to show only blanks by using the row label filter: set it to “(blank)” if you have already replaced blanks. But without a helper column, there is no direct way to make a pivot table count blanks.

Another alternative is to use the COUNTBLANK function outside the pivot table, but that gives a single total, not a breakdown by category. For a breakdown, you would need to combine COUNTIFS with a list of categories, which is essentially building your own pivot table.

When Counting Blanks Is Useful

Counting blanks is valuable in data cleaning and quality checks. For example, you might want to know how many customer records are missing a phone number, how many survey responses skipped a question, or how many products in a catalog lack a category. By counting blanks, you can quantify missing data and decide whether to fill it, drop it, or treat it as a separate group in your analysis.

It is also common in reporting to show “(blank)” as a legitimate category, so stakeholders can see that the data is incomplete. This is often better than silently omitting blanks, which can mislead readers into thinking the data is complete.

Frequently Asked Questions

Why doesn’t my pivot table show blanks?

By default, pivot tables ignore empty cells entirely. They do not appear in the row labels or in the count. To show them, you must replace blanks with a placeholder text or use a helper column.

Can I count blanks without adding a helper column?

No, not directly. A pivot table cannot count empty cells as values. You could use a calculated field with a formula like =IF(Field=””,1,0), but calculated fields work on aggregated data, so they won’t see individual blanks. A helper column is the standard workaround.

Does the placeholder text affect my other calculations?

Yes, it can. If the field is numeric, converting blanks to text will prevent SUM or AVERAGE from working on that column. To avoid this, keep the original column for calculations and add a separate helper column for counting.

How do I count blanks in a pivot table in Google Sheets?

Same as Excel: add a helper column with =IF(A2=””,”(blank)”,A2), then use that column in the pivot table. Google Sheets also has a “Count unique” option, but it still ignores blanks.

What if my blank cells contain a space or formula that returns “”?

Cells with a space or a formula that returns an empty string are not truly blank. Use =IF(TRIM(A2)=””,”(blank)”,A2) to catch both spaces and empty strings.

Can I count blanks in a pivot table using Power Pivot or DAX?

Yes, in Power Pivot you can use DAX measures like =CALCULATE(COUNTROWS(Table), Table[Column] = BLANK()). This is more advanced but gives you a true blank count without altering the source data.

Related

• CMOS Image Sensors: Pixels, Shutters & Designs
• Windows 10 Start Menu Folders & Tiles Guide
• Car Value Estimator: How It Works & How to Use It

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2