How to replace zeros?

How to replace zeros with empty cells? Although this question seems simple, you may find yourself in a situation where you need something like this, and you will not have the right answer. When it comes to data in Pivot tables, it is logical to define this in the options. But they allow you to determine how to treat empty values, not zeros. Zero is not an empty value. You will find out how to replace zeros, regardless of the type of table, in the “recipe” that follows…

The first solution that comes up is to use the Find option, and to find all the zeros and replace them with an empty string using the Replace option. A legitimate choice! However, here you can mistakenly replace the zeros that are in the item name or in some other place where they should remain. Can we remove the zeros without replacing any vaues? The answer is: yes, using custom formatting options.

Let’s say there are zeros in the pivot table. Double-click the column header where you found them and the Value Field Settings window will open. If you click on Number Format, the Format Cells window will open. Go to the Number tab, select Custom, and set “#####” as the format.

As a result, you will get empty fields where there used to be zeros. If necessary, you can refine custom format a little bit, but the solution boils down to formatting the number and this rule applies to all types of tables in Excel.