Number Format to Hide Zeroes
October 22, 2001 - by Bill Jelen
Gary writes:
When I copy cell(s) to a another page, then I link them, I get "0" in the empty cells were there is no data. All the cells are formatted the same. Do you know what the problem could be?
Gary is using Edit Copy, then Edit - PasteSpecial - Link Cells feature. The problem is not with the Paste Link. It is behaving by design. If you have a range of cells, say A1:G10 and you copy, paste link, it pastes links for all the cells, even the blank ones on the theory that you may someday fill in the blank cells.
The real problem is that if you enter this simple formula in Excel, =Z1 and cell Z1 is empty, the answer to that formula is 0 instead of a blank. This can be horribly frustrating.
There is no perfect solution to this problem, but here are two ideas:
- Make the original cells with no data contain a blank cell instead of an empty cell. For each cell that had no data, put a single apostrophe - this character: ' in the cell. It will not show up in the cell, but it will signal Excel that this is a blank cell, not an empty cell. The result of the link will then be blank.
-
One problem could arise: This causes the cell to no longer be considered numeric. If that empty cell is used in any formulas, you may find that the result of the formula is now #VALUE!. If this is the case and you need to leave the cell as empty, then you can use this formatting trick on the copied cells. If your current numeric format is 0.00, then set up a custom number format of
0.00;0.00;
The last semi-colon will cause cells with zero values to appear as blanks. To set up a custom number format, highlight the cells, then Format - Cells. Click the Number tab. In the Category listbox, select Custom. In the Type: box, enter the custom number format shown above.
Adam Strange writes with another method. He notes that using the Tools > Options > View, you can clear the checkbox near "Zero Values".
