MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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:

  1. 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.
  2. 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".

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.