MrExcel.com - Your One Stop for Excel Tips & Solutions

Replace Blanks in a Pivot Table with Zeros

December 05, 2022 - by

Replace Blanks in a Pivot Table with Zeros

Problem: When I have no sales of a particular product in a particular region, Excel leaves those cells in the pivot table blank. This seems like a really bad idea. I’ve learned in this book that if my data has blanks instead of zeros, Excel will assume that a column is a text column. It is really ironic that Microsoft would dare to use a blank cell in the middle of numeric results.

The blank cells in the Values area of the pivot table means that Air Canada did not buy any of product ABC.
Figure 846. Annoying and ironic that Excel uses blanks here.

Strategy: Follow these steps:

  • 1. Right-click any cell in the pivot table and choose Pivot Table Options.

  • 2. In the PivotTable Options dialog, select the Layout & Format tab. You can either uncheck "For Empty Cells Show", or fill in a 0 in the For Empty Cells Show text box. Click OK.

In the PivotTable Options, go to the Layout & Format tab, check the box: For Empty Cells Show and type a Zero in the box.
Figure 847. Add a zero to the For Empty Cells Show text box.

Results: Blanks in the values section of the pivot table are shown as zeros.

Additional Details: You can enter anything in the For Empty Cells Show text box. Some people like to use -- or n.a. in the formerly blank cells. Either works just as well as a zero.

This article is an excerpt from Power Excel With MrExcel

Title photo by Bernard Hermant on Unsplash