OmniDocHow to deal with blank rows and zero values in pivot tables
"(Blank)" items appear in the pivot table, there are extra blank rows in the middle, or the sum is displayed as 0. This is mostly caused by the null value of the source data or the display settings. This article explains how to determine the cause, and gives troubleshooting steps for source data completion, refresh, and display settings.
Key points of this article
- The blank items in the pivot table usually come from the empty cells in the source data row fields. They will disappear after refreshing the source data.
- The blank rows in the middle are mostly caused by layout options. Changing it to table format and repeating project labels is more reliable than manually deleting rows.
- The sum is 0. It may be that the number is saved as text. Convert it to a numerical value first and then refresh it, so that the result will be correct.
- Empty cell display and zero value hiding only change the display, but do not change the total, so they must be treated separately
There are blank spaces in the pivot table, how can I delete it? This is a very common problem in Excel summary reports. There is an extra blank item in the row area, there is a blank row in the middle of the summary table, or some kind of summation is displayed as 0. It looks like a calculation error. In fact, most of it is caused by the null value of the source data or the display setting of the pivot table, and has little to do with the numerical calculation itself.
This article is written for people who often use Excel to do summarization and encounter uneven perspective results. After reading, you can distinguish the respective sources of blank rows and zero values, know whether to change the source data or pivot table settings, and can process them in batches without manually deleting rows one by one.
How to deal with blank and zero values in the pivot table? One sentence conclusion
First determine whether the blank comes from the source data or the layout settings, and then handle it separately: if it can be completed in the source data, complete it. If it is only a display problem, use the pivot table option or number format to process it. Manually deleting rows will be restored after refreshing and is not recommended.
| your situation | Recommended practices | reason |
|---|---|---|
| A blank item appears in the row label | Complete the categorical columns in the source data, or uncheck the blank items in the filter | If there are empty cells in this column of the source table, the pivot table will classify them into a separate category. |
| There are extra blank lines in the middle of the report | Turn off subtotals, change to tabular form and repeat project labels | This is caused by layout options and has nothing to do with data content |
| The null value position would like to be displayed as 0 or a dash. | Set the display content of empty cells in the pivot table options | Only changes the display, not the calculation results |
| Real 0 don’t want to see | Set the number format for the value field and leave the zero value field blank | The format only hides the display, the total still contains these zeros |
| The result is incorrect after the source data is updated. | Refresh the pivot table and convert the source range to a table if necessary | The pivot table saves the results of the last refresh |
First determine what kind of problem it is
| symptoms | Common causes | How to confirm |
|---|---|---|
| Blank items appear in row labels | The row field of the source data has a null value | Filter the column in the source table for blanks to see if there are records |
| Blank lines appear every other line between items | Subtotal or label settings in layout | Temporarily switch the report layout to see if the blank rows change |
| Some types of sums display as 0 | The value is indeed 0, or the number is actually text | Select the source data area and see if summation appears in the status bar |
| Empty cells appear blank instead of the desired content | Pivot table default display of empty cells | Open the pivot table options and check whether there is a setting for the item that displays empty cells. |
| The source data has been changed, but the result has not changed. | The pivot table is not refreshing, or the data source range is not extended | Compare the totals after manual refresh, and then check the data source range. |
Check and repair item by item
Deal with them in order from easy to difficult, and most problems can be solved in the first few steps.
- Check the source data. Open the data source of the pivot table and check whether there are empty cells in the column where the row field is located, and whether the numerical column is left-aligned or has a green triangle in the upper left corner. This usually means that the numbers are saved as text.
- Fill in the null values of the source data in batches. Select the data area of the column where the row field is located, press Ctrl+G to open the positioning dialog box, click the positioning condition, select a null value, then enter the required content (such as "Not filled in"), and finally press Ctrl+Enter to fill in all the spaces at once.
- Convert text to numbers. For left-aligned numbers, use the column split function to directly complete the conversion after selection to ensure that they can be included in the total.
- Refresh the pivot table. Right-click the pivot table and select Refresh, or press Alt+F5 to refresh all, and then observe whether the blank items disappear.
- Cancel the blank items in the filter. Click the filter button of the row label, uncheck the blank items, and retain only the required categories.
- Handle layout blank lines. Turn off subtotals in the pivot table's design options, change the report layout to table format, and turn on the repeating item label so that each row has a complete category name and there are fewer blank rows in the middle.
- Set empty cell display. Open the pivot table option, find the empty cell display in the layout and format related settings, fill in 0 or a dash, etc., it only affects cells with empty results.
- Hide true zero values. Right-click the value area, enter the value field settings, select Custom in the number format, enter #,##0;-#,##0;;@ and confirm. This way zero values are displayed as blank and positive and negative numbers are still displayed normally.
How to avoid it happening again next time
- Do not leave row fields blank for source data. Fill in the missing categories with fixed words, such as "not filled in", so that you will get a clear category in the pivot table instead of a blank item.
- Numbers are unified into numerical format. After importing the data, check the alignment first. Left-aligned numbers must be converted in time to avoid being ignored when summing.
- Convert source area to table. After selecting the data, use Ctrl+T to convert it to a table. Then the newly added rows will be automatically included in the range, and then use it as the data source to build a pivot table.
- Refresh and then check after updating. Every time the source data is updated, the pivot table should be refreshed first, and then compared with the original total of the source data to confirm that it is correct before delivering the report.
❓ Frequently Asked Questions (FAQ)
1. Can the blank items in the pivot table be deleted directly?
You cannot directly delete a single cell because it is part of the data classification. Uncheck the blank item or complete the null value in the source data to make it disappear.
2. The sum result is 0, but the source data clearly contains numbers. Why?
The common reason is that the numbers are actually text, left aligned or with a green triangle in the upper left corner as hints. Refresh the pivot table after converting the text to numeric values and the sum will be correctly accounted for.
3. Will the total change after hiding zero values?
Won't. The number format only changes the display, the total still contains those zeros. If you need to truly exclude certain records, you should filter or adjust the statistical range in the source data.
4. Why does the pivot table not change when the source data is changed?
The pivot table saves the results of the last refresh and needs to be refreshed manually. If the data source range does not contain the newly added rows, they will not be counted. It is recommended to convert the source data into a table before creating a pivot table.
📝 Summary
Blank rows in pivot tables mostly come from null values in the source data or layout settings. Zero values are divided into two categories: real 0 and display problems. First, identify the reasons. If you can complete the source data, deal with it from the source. If the problem is only for display, use the pivot table option or number format to solve it. Remember to refresh and check the total after each modification.
