Show Zero Instead of Blank in a Pivot Table

To make a pivot table show zero instead of blank, open PivotTable Options, tick For empty cells show and type 0. It takes ten seconds, applies to the whole pivot table and stays after every refresh. This guide explains why the blanks appear, walks through the setting, and covers the related problems people often mix up with it: "(blank)" labels, error values and missing rows.

All the examples use this sales data in A1:C8:

Why does a pivot table show blank cells?#

A pivot table shows a blank when no source rows match that combination of labels. Put Region in Rows, Month in Columns and Sales in Values, and you get this:

East has no January sale and South has no February sale, so those two cells are empty. Excel is being honest: there is nothing to add up. But in a report, a blank can look like missing data or a mistake, and it breaks formulas that read the pivot table. A 0 says clearly "we checked, and the total is zero". If you are new to building these, start with the beginner's guide to pivot tables.

How to show zero instead of blank in a pivot table#

The setting lives in PivotTable Options. On Windows:

1. Right-click any cell inside the pivot table.
2. Choose PivotTable Options. (You can also click PivotTable Analyze > Options on the ribbon.)
3. Open the Layout & Format tab.
4. Under Format, tick For empty cells show.
5. Type 0 in the box next to it.
6. Click OK.

The East–Jan and South–Feb cells now show 0. On a Mac, right-click the pivot table and choose PivotTable Options as well. The dialog looks a little different, but the For empty cells show box is in the same dialog.

The setting belongs to this one pivot table. If you have several, repeat the steps for each, or set up one pivot table the way you like and copy it.

Show a dash or text instead of zero#

The box accepts any short text, not just 0. Common choices:

• 0 for a report where totals are read or charted.
• - (a dash) for a cleaner printed table, where zeros would add clutter.
• n/a or none when you want readers to notice the gap.

Keep in mind that text in the box is only for display. If another formula reads the cell, a dash or "n/a" is text, not a number. To keep a true number but show it as a dash, type 0 in the box, then change the number format: open Value Field Settings > Number Format, choose Custom and use #,##0;-#,##0;"-". The third part of the format controls how zeros look.

Why the pivot table still shows blank after the fix#

If some cells stay blank after you tick the box, check these:

1. The box is empty. Ticking For empty cells show without typing anything still shows blanks. Type 0.
2. You ticked the wrong box. For error values show sits just above it and only changes cells with errors.
3. You are looking at a different pivot table. The setting applies only to the one you changed.
4. The source data changed but the pivot table didn't. New rows only appear after a refresh, so a cell can stay empty until you refresh.

How to remove (blank) labels in a pivot table#

A (blank) row or column label is a different problem. It means some source rows have an empty cell in that field, for example a sale with no region typed in. The fix is in the data, not in the options:

• Fill in the missing values in the source data, then refresh. The refresh guide shows how (Windows: Alt+F5; on a Mac, right-click and choose Refresh).
• If the blanks are genuine, click the label's drop-down arrow and untick (blank) to hide it.
• Or click the (blank) cell and type a clearer label, such as "No region". Excel renames that label in the pivot table.

Show zero for errors and for missing items#

Two more settings sit close to For empty cells show:

• For error values show is on the same Layout & Format tab. Tick it and type 0 or a dash to hide errors such as #DIV/0! in calculated fields.
• Show items with no data is in Field Settings > Layout & Print for a row or column field. It shows a label even when the current filter leaves it with no rows, so your layout stays the same size. Combined with For empty cells show, those rows fill with zeros.

The same blank rule applies when you count rows, which the guide to counting with a pivot table covers. And if you sort by a column with zeros in it, the pivot table sort guide explains where they land.

Check a blank cell with SUMIFS#

A formula is a quick way to confirm that a blank really means zero. SUMIFS adds the values that match every condition, and returns 0 when nothing matches:

=SUMIFS(C2:C8,A2:A8,"South",B2:B8,"Feb")

The result is 0: there is no South row for February, which matches the blank cell in the pivot table. Change "Feb" to "Mar" and you get 650, the same as the pivot table. This kind of cross-check is also handy in data tests, where you may need to prove a pivot table total.

Practise it#

Practise pivot tables in a live spreadsheet: you build each summary step by step and get instant feedback. Then try the SUMIFS cross-check on this page to confirm what a blank cell really means.