To hide zeros in selected cells, press Ctrl + 1, choose Custom and enter #,##0.00;-#,##0.00;;@. The third section, left empty, controls zeros, so they show as blank while other numbers keep their format.
To show a dash instead of a blank, use #,##0.00;-#,##0.00;"-";@. To hide zeros across a whole sheet, go to File → Options → Advanced → Display options for this worksheet and untick Show a zero in cells that have zero value.
Match the first two sections to your data rather than using 0;-0;;@ as it is. That version also rounds 1,234.56 to 1235 and drops the thousands separator.
Hiding changes the display only. The zero is still in the cell, in the formula bar and in any chart. Blanking zeros with a formula such as =IF(B2=0,"",B2) returns empty text instead, and any formula that adds or multiplies that cell returns #VALUE!.
Avoid conditional formatting with a white font. The zeros reappear on any shaded row or filled cell. If you use conditional formatting, set the rule’s Number format to the custom code ;;; instead.
In Excel, you can hide the zero values in a data set and make the cells look blank if you don’t want to display them.
And you can do it for a selected range only or for the entire worksheet, as Excel has multiple ways to hide the zero values.
In this tutorial, we have explained the multiple ways to hide the zero values.
Steps to Hide Zeros in the Worksheet Using Excel Options
Excel has a built-in feature that can be used to hide all the zero values automatically within the entire worksheet using the below steps.
- First, open the workbook and then go to the File tab.
- After that, click on Options.
- Now, click on “Advanced” in the left pane and then scroll down to “Display options for this worksheet”.
- From here, click on the drop-down icon and select the worksheet in which you want to hide all the zero values.
- In the end, uncheck “Show a zero in cells that have zero value” and click OK.
- Once you click OK, all the zero values in the worksheet will get hidden, and those cells will look blank.
Now, whenever you enter a zero anywhere in this worksheet, or a formula returns zero, Excel will hide it. The option only applies to the worksheet you selected, not to other sheets.
Hide Zero Values in the Selected Cells Using Format Cells
- First, select the cells or the data range from where you want to hide the zero values.
- After that, right-click and click on the “Format Cells” option (or press Ctrl + 1), and you will get the “Format Cells” dialog box.
- Now, under the “Number” tab, click on the “Custom” option, then enter the custom type 0;-0;;@ and click OK.
- Once you click OK, all the zero values within the selected range will get hidden.
The above-used custom formatting string “0;-0;;@” consists of four parts.
The first 0 is for positive numbers and shows them as whole numbers, the 2nd part -0 is for negative numbers and shows them with a minus sign, the 3rd part is empty, which hides the zero values, and the 4th part @ displays the text as is: <Positive Numbers>;<Negative Numbers>;<Zeros>;<Text>
As 0 is a whole-number format, it rounds decimals on the screen, so 2.5 shows as 3. If your data has decimals, use General in the first two parts instead, or the number of decimals you want, like 0.00;-0.00;;@:
General;-General;;@
And if you want to show a dash instead of a blank for zeros, put it in the third part in quotes:
0;-0;"-";@
Hide Zeros in the Selected Cells Using Conditional Formatting
- First, select the cells or the data range from where you want to hide the zero values, go to the “Home” tab, and then click on the “Conditional Formatting” icon.
- After that, click on the “Highlight Cells Rules” option and then on the “Equal To” option.
- Now, in the “Equal To” dialog box, enter 0 (zero) in the left field, and then click on the drop-down on the right side and choose “Custom Format”.
- From here, you will get the “Format Cells” dialog box, and under the “Font” tab, choose the white color and click OK.
- Once you click OK, the font color of the cells that contain a zero will become white, which will make the cells look empty. This only works when the cells have a white fill; for colored cells, pick the same color as the fill.
Hiding zero (0) values only means hiding the value in the cells, not removing it, so the cells still contain the value even if they look blank. You can still see it in the formula bar, and formulas still use it.
Use a Formula to Return a Blank Instead of Zero
If the zeros come from a formula, you can also make the formula return a blank. In the following formula, IF checks if the value in A1 is zero and returns an empty text; otherwise, it returns the value:
=IF(A1=0,"",A1)
Replace A1 with your own formula when the zero is a result of a calculation. Just keep in mind that is text, so a cell with it isn’t really empty, and some formulas that use it, like a simple + or -, give the #VALUE! error.
Frequently Asked Questions
How do I show zeros again?
For the Excel Options method, tick “Show a zero in cells that have zero value” again. For the custom format, apply the General format, and for conditional formatting, go to Home ➜ Conditional Formatting ➜ Clear Rules.
Does hiding zeros change my totals?
No. All these methods (except the IF formula) only change how the cells look. The zeros are still there, so SUM, AVERAGE and other formulas give the same results.
How do I hide zeros in a pivot table?
Select the values in the pivot table, and apply a custom number format like 0;-0;;@ from Value Field Settings ➜ Number Format, so it stays when you refresh the pivot table.