- 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.
Figure 402 Set up a link to replicate a table on another worksheet.
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.
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?
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.
Figure 406 Use 0;-0;.
For more resources for Microsoft Excel: