Layout and values Updated November 2026

Blank cells in a pivot table

Right-click the pivot, choose PivotTable Options, and on Layout & Format tick For empty cells show and type 0. The (blank) label in Rows is different: it comes from empty cells in the source column, so untick it in the row filter or fill the source.

Syntax

PivotTable Options > Layout & Format > For empty cells show: 0

Part	What it is
`For empty cells show`	Text or number placed in value cells that have no data for that row and column combination
`(blank)`	Row or column item generated by empty source cells in that field
`Show items with no data`	Field Settings, Layout & Print; shows every item even if it has no rows after filtering

Examples

Rows: Region. Columns: Product. Values: Sum of Units
PivotTable Options > For empty cells show: 0

Result: Combinations with no sales show 0 instead of an empty cell.

Row Labels dropdown > untick (blank) > OK

Result: The (blank) row and its total vanish from the pivot.

Select the source column > Ctrl+G > Special > Blanks > type Unknown > Ctrl+Enter > Refresh

Result: Empty source cells now read Unknown, and the pivot shows Unknown instead of (blank).

Common mistakes

Zeros do not appear: the For empty cells show box is ticked but empty, so type 0 in it.
(blank) comes back after refresh: new empty rows arrived, so fix the source or use a Label Filter, Does Not Equal (blank).
The source range includes empty rows below the data: shrink the range or use a Table so trailing blanks are excluded.

Practice Blank cells in a pivot table

These drills check your answer on live data, free, no account needed.

Region by product gridShow total Revenue with Region down the side and Product across the top.
Marketplace by country and yearShow total Sales from the Marketplace channel only, Country down the side and year across the top.

Questions

Why does my pivot table show (blank)?

The field you put in Rows or Columns has empty cells in the source. Excel groups them under (blank).

How do I hide blank rows in a pivot table?

Open the field's dropdown and untick (blank), or fill the empty source cells and refresh.

Can I show a dash instead of 0?

Yes. Type a hyphen in For empty cells show, or use a number format like #,##0;-#,##0;"-" for cells that are genuinely zero.

Number format in a pivot tableFormat pivot table values so the format survives a refresh: use Value Field Settings, Number Format rather than the Home ribbon. Currency and percent.
Refresh a pivot tableRefresh a pivot table after the source changes with right-click Refresh or Alt+F5, refresh every pivot with Ctrl+Alt+F5, and set the file to refresh on open.
Text in the Values areaA pivot table cannot sum text, so the Values area shows a count. Ways to show text: put the field in Rows with tabular layout, or a Data Model CONCATENATEX.