Stop Showing Zeroes in Cell Links
March 24, 2022 - by Bill Jelen

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.


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.
Strategy: Go back to the formula shown in Figure 398. 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?

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.

This article is an excerpt from Power Excel With MrExcel
Title photo by the blowup on Unsplash