Display Blank when Referencing Blank Cell in Excel 2010

I have an Excel 2010 workbook that contains a number of individual worksheets. The cells on one of the sheets are linked to individual cells on two other worksheets in the same workbook. I'm using a direct cell reference that essentially says that whatever value is entered into a particular cell on one sheet also populates cells on two other sheets. I used the (=) function with the cell reference to accomplish this.

The issue I'm running into is that, even when the primary cell is left blank, the cells that populate from that primary cell will display 0, rather than remaining blank themselves.

I want the subordinate cells to remain blank if the primary cell they're linked to is blank.

You need to force Excel to treat the contents of the cell as a text value instead of a number, which it does automatically with blank values.

=A2 & ""

This will force Excel into making that cell reference a text value, thus preventing the conversion of blanks into zeroes.

Here are three answers:

1) Letting other.cell.reference represent the reference formula that you currently have after the = (e.g., Sheet17!$H$42), replace that link reference with

=IF(other.cell.reference<>"",other.cell.reference, "")

2) Set the “Number” format of your linked cells to “Custom”: General;–General;.

3) In “Excel Options”, “Advanced” page, “Display options for this worksheet” section, clear the “Show a zero in cells that have a zero value” checkbox.  Warning: this will cause all zeroes in the worksheet to disappear.

IF your reference data is only either a numeric type (non-text) or empty, and you may have 0's, then this is my preferred approach, with only entering formula once. Admittedly, a slightly indirect way, but it is best I think because:

• you don't need extra cells to put the formula in and then reference the second cells
• you don't need to type the formula twice
• this method differentiates between a zero value and an empty cell
• doesn't require VBA
• doesn't require Named Ranges

Downfall: If you need text data returned this this will not work. For text data my preferred method is using number format as mentioned in other answers above.

=IFERROR((A1 & "") * 1,"")

A1 in this instance can be replaced by any cell, including another sheet, workbook, or INDIRECT().

Notes on how this works:
IFERROR() - second argument is set to empty string, so if an error occurs we get an empty string. So we need to make sure if the source cell is empty, an error is triggered.

Numeric method: Stringify the source value, then multiply by 1. Literal emtpy string * 1 = #VALUE, String with numeric is auto-converted to numeric and no error occurs.