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.

The original data is mostly empty cells with a few numeric cells. You want to copy that range to a new worksheet. The problem will be that =Original!B2 will return a zero when the original cell is empty. You don't want to show the zeroes. — Figure 397. Set up a link to replicate a table on another worksheet.

As predicted, a lot of zeroes show up in the new sheet. — Figure 398. 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 Figure 398. Use one of two methods to force Excel to not display zero values.

One solution is an IF statement: =IF(ISBLANK(Original!B2),"",Original!B2) — Figure 399. 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?

But an easier way could be File, Options, Advanced, Display Options For This Worksheet and unselect the checkbox for Show a Zero In Cells That Have a Zero Value. — Figure 400. 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.

A third solution: a Custom number format of 0;-0; — Figure 401. Use 0;-0;.

This article is an excerpt from Power Excel With MrExcel

Title photo by the blowup on Unsplash