When you set up a pivot table, there might be a few blank cells in the Values area. In the example below, no Banana Bars were sold at stores 3062 or 3659.
Instead of leaving these cells empty, you can change a setting in the pivot table options, to show a zero in those cells.
Video: Change Empty Cells to Zero
To see the steps for changing empty pivot table cells to zero, or other characters, please watch this short video. The written instructions are below the video.
Change the Pivot Table Options
To show zero, or another character, in empty Value cells, follow these steps:
- Right-click a cell in the pivot table, and in the context menu, click PivotTable Options.
- Click the Layout & Format tab in the PivotTable Options dialog box
- In the Format section, add a check mark to For Empty Cells, Show
- In the text box, type a zero, and then click OK to close the dialog box.
Use a Different Character
Instead of showing a zero in the empty cells, you can use other letters or numbers. For example, in the screen shot below, the empty cells show “N/A”
You could also use a dash character to fill the empty cells, because it is sometimes used to represent a zero.
Whatever you decide to use, try to make it clear that there is no data for those cells – don’t enter something that will confuse people!
_______________