Software

Three ways to hide zero values in an Excel sheet

Although zero values are valid, you might need to hide them. Here are three methods for hiding zero values in an Excel sheet.

Zeroes are valid data, but for various reasons you won't always want to display them in an Excel sheet. When this is the case, you have at least three options. Your data and business requirements will dictate the method you choose.

Sheet setting

Perhaps the simplest way to hide all zero values in a sheet is to let the worksheet do it. Just remember, this setting works at the sheet level. You must set it for each sheet in the workbook, as follows:

  1. Click the File menu and then choose Options (under Help). In Excel 2007, click the Office button and then click Excel Options. In Excel 2003, choose Options from the Tools menu.
  2. Choose Advanced in the left pane. In Excel 2003, click the View tab.
  3. In the Display Options For This Worksheet section, uncheck the Show A Zero In Cells That Have Zero Value.
  4. Click OK.

Numeric format

The sheet setting will hide all zero values in the sheet. If you want to hide specific zero values, but not all, you can use a numeric format instead:

  1. Select the cells that contain the zero values that you want to hide.
  2. Click the Home tab and click the Number group's dialog launcher (the small arrow in the bottom-right corner. In Excel 2003, choose Cells from the Format menu.
  3. Click the Number tab (if necessary).
  4. Choose Custom from the Category list.
  5. Enter 0;-0;;@ in the Type field.
  6. Click OK.

Hidden 0s will still be visible in the Formula bar, or in the cell, if you edit in the cell. To undo this format, simply choose an alternate numeric format for the cells.

Conditional format

The numeric format shown above will hide literal zeroes and most returned by a formula. When you run into an exception, you can use a conditional format as follows:

  1. Select the cells that contain the 0s you want to hide.
  2. Click the Home tab and then click the Conditional Formatting option in the Styles group. Choose New Rule. In Excel 2003, choose Conditional Formatting from the Format menu, and skip to step 4.
  3. In the top pane, select the Format Only Cells That Contain option.
  4. From the second dropdown, choose Equal To.
  5. Enter 0 in the third control.
  6. Click Format.
  7. From the Color dropdown, choose White (or the color that matches the sheet's background).
  8. Click OK twice.

This format is easy to forget and can cause trouble down the road, so choose this method carefully.

Best choice

Of the three methods, the conditional formatting method is probably the least productive because of the potential to forget it; costs can run high with employing formatting tricks. The sheet and numeric format methods are more straightforward and require little effort.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

12 comments
hws1947
hws1947

If using a formula and you want the cell appear blank if the result is zero, build a IF statement around the formula. That way if I change the font or background color of the cell it does not affect the hidden zero. Example: =IF(SUM(A4:A5)=0,"",SUM(A4:A5))

sparent
sparent

The problem with displaying an empty string, instead of a zero, is that it will change the results of some range formulas (e.g., COUNT(), AVERAGE(), ...)

DLMc
DLMc

Is there a way to hide zero's by default in the default sheet setting (the check box checked by default), rather than having to go to the sheet seting window each time?

frroland
frroland

The conditional formatting method works well with light gray text color (on white) in a list of values. It's clear that the zeros are there while the non-zero values are easy to see.

mfa
mfa

Use conditional formatting to change the font color to white for zero values. You won't see the zero normally, but it will show up if you highlight a group of cells containing it.

whitehound
whitehound

=IF(X=0,"",X) where X is the formula you want to evaluate. If formula X is so long you don't want to have to insert it twice, stick formula X in a column on its own, hide that column and then stick the IF function in a visible column and tie it to the result of X in the hidden column.

ruralist
ruralist

I use the conditional "if" - if(a1>0,a1*b1,"")

heavener
heavener

In some cases, I have formulas that fill in as conditions/calculations change. But initially, they display the divide by zero warning -- #DIV/0!. Is there a way to hide that until the return changes?

hometoy
hometoy

While it doesn't remove the 0, using the Accounting format with no Symbol. This then shows a "-" instead of a zero. I use this to clean up the sheet very easily.

hws1947
hws1947

This is a basic example but I've used this method for years regardless of how complex the formulas. Let's say you will have D2 perform a division based on values in B2 and C2. Instead of a straight-forward formula in D2 like this: =B2/C2 (which initially causes D2 to show the #DIV/0!) I wrap an IF condition around D2's formula pointing to C2 (the last cell needing a value in order to perform the calculation) like this: =IF(C2="","",C2/B2) So when I plug the first value in B2, then the second value in C2, once C2 is no longer blank, D2 lights up with the result of the calculation. So you can use this method whether to hide zeros, negatives or the #DIV/0!. Just adjust the formula as needed.

jimbobmcgee
jimbobmcgee

I use conditional formatting across each sheet: 1. Select all cells in the sheet and click 'Conditional Formatting' / 'New Rule' from the ribbon 2. Use the option 'Use a formula to determine which cells to format' 3. Enter '=ISERROR(A1)' (without quotes) as the formula 4. Pick a format (I tend to use a pale grey text color, so I can still see the errors, but they don't look as garish) You can pick a smaller range than the whole sheet but, depending on the range you pick, you may need a different cell reference in the =ISERROR(brackets). If you don't want to do it using formatting, the ISERROR function can be used in an IF() formula, too: =IF(ISERROR(A1/B1), "", A1/B1) But, then again, if you can do that, you might be better off checking for null divisors with =IF(B10, A1/B1, "")

ssharkins
ssharkins

Thanks for adding to the list!