In Excel for Microsoft 365 on Windows, a PivotTable can show empty value cells as blank: right-click inside it, choose PivotTable Options, open Layout & Format, and leave the For empty cells show box empty. If you mean the literal “(blank)” item in a row or column label, that is a blank value in the source field; the empty-cells setting will not remove it, so filter that item out or correct the source data.
What “blank” means in a PivotTable
People use “blank” to describe several different things in a PivotTable, and the right fix depends on which one you see. A blank-looking result cell, the text “(blank)” in a label, a zero, and a category with no records are not interchangeable. Treating them as the same can hide useful information or leave the unwanted display untouched.
- An empty value cell: A result cell in the body of the PivotTable has no value to display for that combination of row and column fields. Excel may show a zero or another placeholder, depending on the PivotTable’s settings.
- A “(blank)” label: One or more records in the source data have an empty value in a field used for rows, columns, or filters. Excel displays that empty category as “(blank).”
- A zero: A calculation returned 0, or the values in that group add up to zero. Zero is a real result, not an empty cell.
- A missing combination: No source records exist for a particular row-and-column combination. This differs from a record that exists but has an empty value.
For example, imagine a sales list with Region, Product, and Sales columns. If a record has no Region, the row field can contain “(blank).” If a region has no sales record for a particular product, that product-region intersection may have no value cell. The first issue concerns source labels; the second concerns the report’s layout and display.
How PivotTables handle empty source data
A PivotTable summarizes records from a source range, Excel table, or supported external data source. It groups records according to the fields placed in the Rows, Columns, and Filters areas, then calculates fields placed in Values. If a source field used as a label is empty in a record, Excel can group that record under “(blank)” rather than discard it. This is often useful: it shows that the source contains records whose category has not been supplied.
Value fields behave differently. A sum, count, or other summary is calculated from the records in each group. Where a particular group has no applicable value, the resulting cell can be empty, while a group with numeric values that total zero has a zero result. A count may also return zero where a sum appears blank, depending on the fields and combinations represented. The summary type matters, so check the Values field’s settings before assuming that the source is missing data.
There is another wrinkle: “no data” does not always mean Excel will create a cell for that combination. A PivotTable normally summarizes combinations represented by its records. Field settings such as Show items with no data can affect whether certain items appear, but displaying an item with no records does not create a real source value. It is a reporting choice, not a way to fill missing data.
Display empty value cells as blank
To control what appears in empty cells in the body of a PivotTable in Excel for Microsoft 365 on Windows:
- Click a cell inside the PivotTable so the PivotTable tabs appear.
- Right-click a PivotTable cell and choose PivotTable Options. You can also open the options from the PivotTable Analyze tab, using the Options command.
- In the PivotTable Options dialog, select Layout & Format.
- Under Format, find For empty cells show. Clear the box or remove any text in it, then select OK.
This setting controls the display of empty value cells in that PivotTable. If the box contains a character such as a hyphen, Excel displays that character in empty cells; if it is left empty, those cells appear blank. It does not change the source data, remove records, or convert zero values into blanks. It also does not remove “(blank)” from a row or column field, because that text represents an empty category in the source.
On a Mac, select the PivotTable and look for its PivotTable options on the PivotTable tab or in the contextual controls; exact labels and placement can vary by Excel version. Excel for the web has a more limited set of PivotTable editing controls than the Windows desktop app. If you cannot find the empty-cell option in the web interface, open the workbook in the desktop version to change it.
Remove “(blank)” from row or column labels
If “(blank)” appears among the PivotTable’s categories, use the filter for that field to hide the category. Click the dropdown beside the row or column field heading, clear the checkbox for “(blank),” and apply the filter. Depending on the report layout, the control may be labelled with the field name rather than “Row Labels” or “Column Labels.” This removes the item from the current view; it does not repair or delete the underlying source records.
Filtering can be appropriate when the blank records are irrelevant to the question the report answers. For example, a temporary analysis of completed orders might exclude records that have not yet been assigned a sales region. But if those records represent real sales, hiding them could make totals incomplete or misleading. Before filtering, compare the report total with the unfiltered total and decide whether the missing category should be disclosed.
If the blank category should have a meaningful name, add one in the source data instead. You might replace empty region entries with “Unassigned” or “Not recorded,” depending on what the blank means. Refresh the PivotTable afterward. Use a label that describes the data accurately: “Unknown” is not the same as “Not applicable,” and an explanatory category is often more useful than a blank.
Fix the source data carefully
When source blanks are accidental, correct them in the source rather than relying on a report filter. First locate the records and determine why the field is empty. A missing customer name may need to be researched; a product category might be populated from a controlled list; and a field that genuinely does not apply may deserve a specific “Not applicable” value. Do not fill every blank with the same word until you know the blanks have the same meaning.
Check the source cells themselves, not only the PivotTable. A cell may look empty because it contains a formula that returns an empty string, spaces, or a formula result that is not visible under the current formatting. Those cases can behave differently from a truly empty cell in calculations or when the data is refreshed. If a PivotTable continues to show an unexpected blank category, inspect representative source records and verify that the field contains the intended values.
After editing the source, click inside the PivotTable and choose PivotTable Analyze > Refresh, or right-click the PivotTable and select Refresh. Refresh recalculates the report from its current source; it does not necessarily expand a fixed source range to include newly added rows outside that range. An Excel table usually grows when data is added directly below it, but a PivotTable based on a fixed cell range may need its source updated through PivotTable Analyze > Change Data Source. Confirm the source range before assuming a refresh has included new records.
Blanks, zeros, and formatting are different
A common misconception is that “For empty cells show” turns zeros into blank cells. It does not. If a value field calculates zero, Excel has a numeric result to display. You can decide whether zero should be visible through number formatting or by changing the report’s calculation, but that is a separate choice from setting the display for empty cells. Hiding zeros can make a report harder to interpret, especially when a zero means that a category was measured and had no activity.
Formatting can also make a value look absent. A custom number format, font color, conditional formatting rule, or narrow column may obscure what is actually in a cell. Click the cell and inspect the formula bar or the value shown in the grid; also check number formats and conditional formatting if values appear inconsistently. Do not change formatting to solve a data-quality problem: a hidden value still exists, while a genuinely empty result remains empty.
Likewise, replacing a source blank with zero changes the meaning of the data. For a sales amount, zero can mean a recorded amount of zero, whereas an empty cell can mean unknown, not entered, or not applicable. Choose a replacement based on the data’s definition, not simply because zero or a dash makes the PivotTable look tidier.
Common troubleshooting checks
If the display does not change after you make an adjustment, work through these checks before rebuilding the PivotTable:
- Confirm the location: Is the problem in a value cell or in a row/column label? The empty-cells option applies to the former, not the latter.
- Check the selected PivotTable: In a workbook with multiple PivotTables, make sure you opened options for the report you are trying to change.
- Refresh after source edits: A PivotTable may continue to show its previous summary until refreshed.
- Verify the source range: New records outside a fixed source range will not appear just because you refreshed.
- Inspect the field arrangement: A field placed in Rows or Columns can expose blank categories; the same field placed elsewhere may produce a different view.
- Check filters and slicers: Existing filters can hide categories or make it seem as if records are missing.
- Distinguish a missing combination from an empty record: One has no matching source rows; the other has a matching record whose value is empty.
If the PivotTable is built from the Data Model or an external source, the source’s treatment of blank and missing values can add another layer. Check the underlying query, model, or connection as well as the PivotTable display. A formatting option cannot repair a value that was transformed or omitted before the PivotTable received it.
Frequently Asked Questions
How do I make a PivotTable show blank instead of zero?
First verify that the cell is genuinely empty rather than a calculated zero. For an empty value cell, open PivotTable Options, choose Layout & Format, and leave For empty cells show blank. That setting does not hide numeric zeros.
Why does my PivotTable show “(blank)”?
At least one source record is empty in a field used for a row, column, or filter category, or the source value is being interpreted as blank. Filter the “(blank)” item to hide it, or correct the source if the category should have a meaningful value.
Will refreshing a PivotTable remove blank rows?
Not by itself. Refresh updates the summary from the source, but a blank source category remains until you correct the source or filter that category out. Also confirm that the PivotTable’s source includes any newly added records.
Can I replace “(blank)” with “Unknown” in the PivotTable?
The reliable approach is to enter “Unknown,” or a more accurate description, in the relevant source cells and refresh. Filtering or formatting the PivotTable does not change the underlying category, and a source correction preserves the meaning for future refreshes.
Why does the PivotTable still show a blank after I filled the source cell?
Refresh the PivotTable, check that you edited the correct source record and field, and confirm the source range includes that record. Also inspect for other empty records, formulas returning an empty string, or filters that affect what you see.
Related
- How to Enter Slide Master View in PowerPoint
- Excel Not Responding? Troubleshoot Common Causes and Fixes
- Excel Not Responding? Causes, Fixes, and Recovery Steps
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Combo Chart Not Available? Causes and FixesYes—Excel for Microsoft 365 on Windows includes combo charts. If the Combo option is missing, try selecting a…
- Radar Charts in Excel: Why and When to Use ThemA radar chart in Excel is used to compare several measures for one or more items across the…
- Excel Scatter Plot Linear Regression: How to Add a TrendlineTo show a linear regression on an Excel scatter plot, select your numeric X and Y data, choose…
- Excel Graph Not Showing 0 Values? Fix the ChartExcel normally plots a numeric zero as a value, but it may be hard to see: it can…
- X Values on a Graph: Meaning, Examples, and ExcelX values are the values plotted along a graph’s horizontal axis; each one tells you where a point…
- Excel Not Generating Charts? Fix the Common CausesIf Excel is not generating a chart, first select a clean range that includes the labels and values,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.