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.
- Set up a link to replicate a table on another worksheet.
-
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.
Strategy: Go back to the formula shown in Fig 410. Use one of two methods to force Excel to not display zero values.
- The IF solves one problem, but creates another.
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?
- 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.
- Use 0;-0;.