Excel Pivot Table Not Showing Blank Values? How to Fix It

Charts & pivot tables · Updated November 21, 2026

If an Excel PivotTable does not show blank values, first check the field’s filter for a hidden (blank) item, then refresh the PivotTable. If you mean empty cells in the values area, use PivotTable Options to display a chosen value in those cells; that setting does not add a missing category or change the source data.

What “blank” means in a PivotTable

There are several different blank-related behaviors in a PivotTable, and the right fix depends on which one you see. A blank in the source data can become a (blank) entry in a row or column field. An empty source value in the data field may contribute nothing to a sum or count. Separately, a PivotTable can have an empty result cell where no value is available for a particular combination of row and column labels.

Those situations are easy to confuse. For example, if a sales table has a blank Region for one transaction, the row field might include a (blank) category. If the Region field has been filtered to show only East and West, that category can be hidden. But if the PivotTable has East and West as rows and a product as columns, an empty intersection may simply mean there were no matching records for that combination.

Start by locating the problem: is a row or column label missing, is a (blank) item absent from a filter list, or is a result cell empty? The distinction matters because displaying a symbol in an empty result cell will not restore a missing row label.

Show a hidden (blank) row or column item

In Excel for Microsoft 365 on Windows, check the filter attached to the field that should contain the blank category:

  1. Click the drop-down arrow beside the row or column field label in the PivotTable.
  2. Look through the item list for (blank). If it is listed but unchecked, select it. You may need to clear the search box or choose Select All first to reveal every item.
  3. Select OK and check whether the blank category appears.

If you do not want other filtering choices changed, select only (blank) in addition to the items already selected. If the filter menu indicates that a filter is active, you can also choose Clear Filter From [field name] to remove that field’s filter entirely. This may bring back many other categories, not just blanks.

Report filters can also exclude blank items. Check the filter displayed above the PivotTable, as well as any row or column field filters. A slicer connected to the PivotTable may be filtering the same field, so clear or adjust the slicer selection if the ordinary field menu looks correct.

Refresh the PivotTable and verify its source

A PivotTable is a summary based on a source range or table, not a live display of every cell as it changes. If you added a blank record or corrected a field after creating the PivotTable, refresh it: click inside the PivotTable, then select PivotTable Analyze > Refresh. To update every PivotTable in the workbook, use Data > Refresh All.

Refreshing cannot include rows that fall outside the PivotTable’s source. To inspect or correct that source in Windows, click inside the PivotTable and choose PivotTable Analyze > Change Data Source. Confirm that the selected range includes the relevant rows and columns, then refresh again. This is a frequent cause when new records have been pasted below the original data range.

For data that will keep growing, converting the source range to an Excel table can make expansion easier. Select a cell in the source data and choose Insert > Table, confirming whether the table has headers. Then use the table as the PivotTable’s source. New rows added directly to the table become part of the table, but the PivotTable still needs a refresh to summarize them.

Also check for completely empty rows or columns that split the source data. If a PivotTable was created from a manually selected range, an apparent blank record below that range is not part of the source simply because it is nearby on the worksheet.

Check what the source cell actually contains

A cell that looks blank is not always empty. It may contain spaces, a formula returning an empty string, or a nonprinting character copied from another system. Those values can be treated differently from a genuinely empty cell and may create a category that looks blank, appear as an unexpected item, or prevent the PivotTable from grouping records as expected.

Inspect a suspicious source cell by selecting it and looking at the formula bar. If it contains a formula, the cell is not truly empty even if the formula displays no characters. If it contains spaces or unwanted characters, clean the source consistently before refreshing. For imported text, a helper column using TRIM can remove ordinary extra spaces; nonprinting characters may require additional cleanup, and the cleaned results should be checked before replacing source values.

Do not remove blanks automatically if they have meaning. A missing customer, date, or region may signal incomplete data that should be investigated rather than silently relabeled. If the source is an export, check the system that produced it to learn whether blank fields mean “unknown,” “not applicable,” or “not entered.”

Display a value in empty result cells

If the missing blank is an empty cell inside the PivotTable’s results area, you can show a placeholder without changing the source. In Windows, click inside the PivotTable, select PivotTable Analyze > Options, and open the Layout & Format tab in the PivotTable Options dialog. Under Format, select For empty cells show, type a display value such as 0 or —, and select OK.

This option affects empty result cells in that PivotTable. It does not create source records, add missing row or column labels, or convert a blank in the source into zero. Use a dash when “no result” should remain visually distinct from a measured zero. Use 0 only when zero is an appropriate interpretation for the report; otherwise readers may assume a value was measured and found to be zero.

To return to empty-looking result cells, reopen PivotTable Options and clear the text in For empty cells show. Do not confuse this with changing the number format, which controls how actual numbers appear, or with filling blank cells in the original data.

Show categories with no records

Sometimes a category should remain visible even when it has no matching records in the current results. This is different from a blank category: it could be a known month, product, or department with no activity. For a row or column field in a standard PivotTable, right-click one of that field’s labels, choose Field Settings, open Layout & Print, and select Show items with no data if the option is available.

This setting can show items already known to the PivotTable’s field, but it cannot invent categories that are not present in the source or PivotTable cache. For example, if the data contains January and March but has never included February, this setting may not create February for you. To report a complete calendar or product list, include that list in the source model or use an appropriate data-model relationship and configuration.

Availability and behavior can vary with the PivotTable’s source and layout, including PivotTables built from external or OLAP data. If the setting is unavailable, check the connection or data model documentation and consider adding the required categories to the source.

When blank values affect totals and counts

Blank source cells do not all behave like zero. A blank in a numeric field is not a measured amount of zero, and a sum generally adds the numeric values that are present rather than treating each empty cell as a separate zero-valued transaction. Likewise, a count depends on the chosen summary: Count counts nonempty entries, while Count Numbers counts numeric entries. This difference can make totals and counts appear inconsistent if you expect every source row to contribute.

To review or change a value field’s summary, right-click a number in that field and choose Summarize Values By, then select the relevant calculation. You can also use Value Field Settings for additional options. Before changing the calculation, decide what the report is supposed to measure: number of records, number of numeric amounts, or total amount are not interchangeable questions.

If an empty value should count as zero for a business rule, decide that in the source data or in a deliberately designed calculation rather than assuming the display placeholder changes the math. A cell showing a dash or zero because of PivotTable formatting is not proof that the underlying record contains that value.

Windows, Mac, and Excel for the web

The directions above use Excel for Microsoft 365 on Windows. Excel for Mac has comparable PivotTable filtering and refresh controls, but ribbon labels or dialog placement can vary by release; select the PivotTable first and look for its PivotTable Analyze tab and PivotTable Options or Field Settings controls. If a command is not visible, check the ribbon’s contextual PivotTable tab rather than looking in ordinary worksheet formatting.

Excel for the web supports many common PivotTable tasks, including interacting with filters and refreshing supported data, but some creation, layout, and options controls may be limited compared with the desktop app. If you cannot find the empty-cell display or field-layout setting in the browser, open the workbook in Excel for Microsoft 365 desktop and make the change there. Avoid assuming that a missing browser command means the workbook’s data is damaged.

Frequently Asked Questions

Why does my PivotTable leave out blank rows?
A PivotTable summarizes records; it does not normally create a separate result for every empty worksheet row. If a source record has a blank field, check that the record is inside the source range, refresh the PivotTable, and confirm that the field’s (blank) item is not filtered out.

How do I show 0 instead of blank in a PivotTable?
Open PivotTable Analyze > Options > Layout & Format, enter 0 beside For empty cells show, and select OK. This changes the appearance of empty result cells; it does not fill blank source cells or change the calculation.

Why is (blank) missing from the filter list?
The source may have no genuinely blank values in that field, the relevant records may be outside the source range, or a filter or slicer may have removed the item. Check the source cells, review the source range, clear relevant filters, and refresh.

Does a formula that returns an empty string count as a blank?
It is not the same as an unused cell: the cell contains a formula whose displayed result is an empty string. PivotTable behavior can differ from a genuinely empty source cell, so inspect the formula and test the field after refreshing rather than relying only on what the worksheet displays.

Can I show a month or category with no data?
Try Field Settings > Layout & Print > Show items with no data for the relevant row or column field. The item generally needs to be known to the PivotTable; this option does not automatically create every missing month or category.

A reliable way to diagnose the problem

Work from the source toward the display: verify that the record is included, inspect what the source cell contains, refresh, check field filters and slicers, and then choose whether empty result cells need a placeholder. This order helps distinguish missing data from hidden data and from a formatting choice. Keep a copy of the original source before cleaning values, and document whether blanks mean unknown, not applicable, or zero so that the PivotTable remains clear to anyone using the report.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.