How to show zero in empty cells in pivot table in Excel?
In some cases, there are some empty cells in a pivot table which may be not good-looking. Now I will talk about showing zeros in these empty cells in pivot table in Excel.
Show zero in empty cells in pivot table
Show zero in empty cells in pivot table
In Excel’s pivot table, there is an option can help you to show zeros in empty cells.
1. Right click at any cell in the pivot table, and click PivotTable Options from the context menu. See screenshot:
2. In the PivotTable Options dialog, under Layout & Format tab, uncheck For empty cells show option in the Format section. See screenshot:
3. Click OK. Now you can see the empty cells shown as zero.
 |
 |
 |
Note: Also you can type 0 or – into the textbox beside For empty cells show option and click OK, then it will show empty cells as zeros, too.
Tip:
1. To show empty cells again, you just need to check For empty cells show option back with nothing filled in the text box besides this option.
2. You can check For empty cells show option and type the contents you need in the text box beside For empty cells show option, then the empty cells will be shown with your typed text.
Relative Articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
