• Excel Book Excerpt

Excel Stop Showing Zeroes in Cell Links

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Stop Showing Zeroes in Cell Links

Problem: I have the data set shown below. I need live formulas that replicate this data set on another worksheet. When I set up the formulas, I get zeroes where the blank cells are located. I can use =IF(ISBLANK(A1),"",A1) to suppress the zeroes, but then if I try to do any math on A1 in the worksheet copy, I am getting #VALUE errors.

LE10000397.jpg

Figure 402 Set up a link to replicate a table on another worksheet.

LE10000398.jpg

Figure 403 The result is showing zeroes instead of blank cells.

If you change the formula to display nothing, the zeroes go away, but there is another problem. A formula such as =C2+B2 will display a #VALUE! error while it would have worked fine in the original data.

 

LE10000399.jpg

Figure 404 The IF solves one problem, but creates another.

Strategy: Go back to the formula shown in Figure 403. Use one of two methods to force Excel to not display zero values.

The first method is to suppress the display of zero for the entire worksheet. Go to File, Options, Advanced. Scroll down to Display Options For This Worksheet. Uncheck the box for Show a Zero In Cells That Have a Zero Value.

Gotcha: this setting affects the entire worksheet. What if you want zeroes to appear in another range on this worksheet?

LE10000400.jpg

Figure 405 Zeroes don’t appear. The formula in F2 works as expected.

In that case, you can use a custom number format to suppress zeroes in a particular range. Select B2:E11. Press Ctrl+1 (Ctrl+One). On the Number tab, choose Custom from the listbox on the left. Type a custom number format of 0;-0;. This code will display positive numbers and negative numbers, but suppress zero values.

LE10000401.jpg

Figure 406 Use 0;-0;.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel:

privacy policy