Empty cell show: "None"

Question

When creating a pivot table I enter "None" under pivot table options/Layout & Format/Format/For empty cells show: "None". It still shows (blank).

Answer 1

Hi @Tracie Currier,

Thank you for posting your question in the Microsoft Q&A forum.

According to your concern, the steps you have taken are correct and Excel should correctly show the blank cells as None instead of blanks. I have also tested in my testing environment (Excel version 2601 build 19628.20106) and the feature is working properly without any problems:

Therefore, there is a possibility that the source data is not truly blank cells, but it can be "" or spaces cells. You can quickly verify by using the formula =ISBLANK(an empty cell in the Pivot table). If the result is FALSE, it means that the cell is not completely blank and causing the format is not working for the blank cells in the Pivot Table.

Please understand that my initial response may not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution. 

Thank you for your understanding and cooperation. I look forward to hearing from you

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

If you have set the option to show "None" for empty cells in your PivotTable but are still seeing blank cells, it may be due to the default behavior of the PivotTable. The DisplayNullString property controls whether a custom string is displayed in cells that contain null values. By default, this property is set to True, which means it should display the NullString value (which is an empty string by default).

To ensure that the custom string "None" is displayed instead of a blank cell, you should check the following:

1. Ensure that the DisplayNullString property is set to True.
2. Set the NullString property to "None".

If these properties are configured correctly and you are still experiencing issues, it may be worth checking if there are any other settings or filters applied to your PivotTable that could be affecting the display of empty cells.