OmniDocHow to count the number of blank cells in a pivot table
Pivot table counting only counts non-empty cells. Directly counting blank cells will result in incorrect results. This article explains three methods of using auxiliary columns to mark blanks, COUNTBLANK statistical result areas, and viewing "(blank)" groups, and explains how to check the results.
Key points of this article
- The "Count" of the pivot table only counts non-empty cells. Using it directly to count blank cells will get the opposite result.
- To count the number of blank fields in a certain field of the source data, you can add an auxiliary column on the right, use the IF formula to record the blanks as 1, and then sum the column.
- For the blank cells in the statistical pivot table result area, use the COUNTBLANK function to specify the area.
- When checking, the number of blank entries plus the number of non-empty entries should equal the total number of records; pay attention to cells that appear to be empty but contain empty strings.
When sorting out sales forms, customer registration forms or questionnaire data, many people want to know "how many unfilled records are there in a certain column?" Naturally, I would think of using a pivot table to do it, but I found that if I dragged a field into it to do "counting", the number obtained was the number of non-empty fields, which was exactly the opposite of what was expected.
This article is written for people who often use Excel to make summaries and need to count the number of blanks. After reading, you will be able to distinguish what can and cannot be calculated directly in a pivot table, and use auxiliary columns or functions to get the exact number of blanks. You can also use a simple method to check whether the results are correct.
It should be noted that this article talks about general Excel operations. If your form is a PDF, you need to turn it into an editable form first. The "Before You Begin" section at the end of this article will explain how to do this.
How to count blanks in a pivot table? One sentence conclusion
The "count" of the pivot table only counts non-blank cells, so to count blank cells, you must first mark them in the source data, and then summarize the marked results. Which method to choose depends on what your statistical objects are.
| your situation | Recommended practices | reason |
|---|---|---|
| Want to know how many blanks there are in a certain column of the source data | Add an auxiliary column next to the source data, mark it with an IF formula, and then drag the auxiliary column into the value area to calculate the sum. | Pivot tables cannot directly calculate blanks. The sum after marking is the number of blank bars. |
| Want to count how many blank cells there are in the pivot table result area? | Use the COUNTBLANK function to select a range of results | The result area is an ordinary cell, and the function can directly count the blanks. |
| Want to see the number of blanks grouped by region, department, etc. | Drag the grouping field into the area and use the auxiliary column to calculate the sum | After grouping, the number of blank bars in each group is clear at a glance. |
| I just want to see if a certain field has a "(blank)" group | Put this field in the progress area and observe the "(blank)" item and its count | Good for quick viewing, but only reflects the whitespace of the field itself |
Before you start: Confirm these points first
Before taking action, make some judgments about the following items to avoid statistical discrepancies with expectations.
- Confirm whether the blank is a vacuum or a "false blank". The cell may have only one space, or the formula may return an empty string. They appear empty, but the statistical rules may be different. When the IF formula determines
="", the empty string will be counted as a blank, but the spaces in the cell will not be counted. In this case, either clean the data or use TRIM in the formula. - Confirm that the source data is a regular table. Each column must have one and only one header. There should be no merged cells or summary rows in the middle. Otherwise, the fields in the pivot table will be messed up and the statistical results will be biased.
- Distinguish statistical objects. Whether to count the blanks in a certain field of the source data or to count the blank cells in the pivot table output area, the two methods are different. Think clearly first, then choose a method.
- Consider whether the data will increase. If you will add rows in the future, it is recommended to convert the source data to an Excel table first (press Ctrl+T after selecting the area), so that the formula and pivot table range will be automatically extended when adding rows, without having to reset it every time.
- Whether the table is from a PDF. If the form is just a PDF, first make it an editable form. Scanned PDFs can be recognized using OCR, and the recognition results must be checked one by one before being counted.
Steps to count the number of blanks in a certain field in the source data
Let's take a customer registration form as an example: Column A is the order number, column B is the customer name, and column C is the region. The goal is to count how many customer names in column B are empty and split them by region.
- Add a new column to the right of the source data, and write "Customer Blank Mark" in the header.
- Enter
=IF(B2="",1,0)in the marked cell corresponding to the first piece of data. The meaning of the formula is: if column B is empty, it is recorded as 1, otherwise it is recorded as 0. - Use the fill handle to copy the formula down to the row where the last data is located, and confirm that each row has a result.
- Select any cell in the data area, insert the pivot table, and place it in a new worksheet.
- Drag "Customer Blank Mark" into the "Value" area and set the summary method to "Sum". The number displayed at this time is the number of blank bars in column B.
- Drag "Region" into the "Row" area and you can see the number of blank bars in each region.
- If new data is added in the future, perform a refresh operation on the pivot table and let it read the source data again, and the results will be updated.
If you just want a total and no grouping, just skip step 6.
Use functions to count the blank cells in the pivot table result area
Sometimes the pivot table itself has been completed, and you want to know how many blank cells there are in the result area. For example, some areas have no data in certain months. At this time, it is simple to use the COUNTBLANK function directly in the ordinary cell:
- Find the range where the pivot table results are located and note the range, for example B3:E12.
- Enter
=COUNTBLANK(B3:E12)in any blank cell and press Enter. - The number obtained is the number of blank spaces in the area.
Note that the area of the pivot table changes as data increases or decreases. If the number of rows changes after you refresh, recheck whether this area still covers all results.
Check each item after completion
Do not use the calculated figures directly. It is recommended to check them one by one according to the table below.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Does blank plus non-blank equal the total number? | Use COUNTA to count the number of non-empty items. Added to the number of blank items, it should equal the total number of rows of data. | When not equal, check whether there are hidden rows, duplicate rows or empty rows that have been accidentally deleted. |
| Is the seemingly empty grid vacuum? | Select the grid to see if there is content in the edit bar; use the LEN function to see the length if necessary | When there are spaces or empty strings, use search and replace or TRIM to clean and recalculate. |
| Has the pivot table been refreshed? | Compare source data modification time with pivot table results | Right-click the pivot table and select Refresh, then recheck the numbers. |
| Is the grouping complete? | Check whether the sum of the number of blanks in each group is equal to the total number of blanks | The lack of grouping indicates that there are outliers in the region field in the source data. Recalculate after unified spelling. |
| Is the recognition result accurate? | Check a few lines against the original PDF | OCR recognition errors need to be corrected manually and then re-calculated. |
📊 Core comparison
The first three methods each have their own emphasis, and you can choose accordingly.
| method | What is suitable for statistics | advantage | Things to note |
|---|---|---|---|
| Auxiliary column addition sum | The number of blank entries in a certain field of the source data, which can be grouped by | Can be split into any dimension, and the results can be traced | After adding data, make sure that the auxiliary column formula has been extended. |
| COUNTBLANK function | Blank cells in the normal range or pivot table result range | Complete with one line of formula, easy to operate | The area range is fixed, and the range needs to be updated after the data changes. |
| View the "(blank)" group | Grouping of blank records in a field | No auxiliary columns are needed, observation is intuitive | Only group counts can be seen, which is not easy to combine with other fields. |
❓ Frequently Asked Questions (FAQ)
1. Why is the count of the PivotTable data less than the actual number of records?
The "count" of the pivot table only counts non-empty cells. If some rows have empty fields, they are not counted. To count blanks, you need to mark them with auxiliary columns and then sum them.
2. The cell looks empty, but COUNTBLANK does not count it. Why?
There may be a space in the cell, but it's not really blank. You can use the LEN function to check the length. If there are spaces, use find and replace or TRIM to clean them.
3. Is the empty string returned by the formula considered blank?
It depends on the statistical method. Writing ="" in the IF formula will count empty strings as blanks, and COUNTBLANK will also count empty strings. However, ISBLANK only counts real empty cells, and the results of the two may be different. Try to confirm before counting.
4. The form is a scanned PDF, how to count the blanks?
First use the PDF editor of the OmniDoc desktop client to perform OCR on the scanned document, then export it to Excel, and then follow the steps in this article to make statistics. There may be errors in the identification process, and key data must be checked against the originals before statistics.
5. After refreshing the pivot table, the results of the auxiliary columns are wrong?
Usually the new data is not covered by the auxiliary column. Convert the source data to a table, or extend the formula down to the last row, and then refresh the pivot table.
📝 Summary
The pivot table itself cannot directly count the number of blanks, but as long as the source data is marked with an IF formula and then the marked columns are summed, the accurate number of blanks can be obtained. When counting the pivot table result area, it is more straightforward to use the COUNTBLANK function. After the statistics are completed, remember to use "Blanks plus non-blanks equal the total number" to check.
