Excel 2010: Set cell value to same value as other cell, even if cell is empty

Question

In Excel 2010, I want cell A1 to have the same value as cell A2. So I set a formula in A1:

=B1

that works fine if B1 has a value. But, if B1 is empty, then A1 shows "0". I want A1 to be empty if B1 is empty.

As a workaround, I'm using the following formula in A1:

=IF(B1="", "", B1)

Is there an easier and more elegant way to set a cell to the same value as other cell? (without VBA)

Whats wrong with `=IF(B1="", "", B1)` ? Works fine right? – richie Apr 07 '14 at 13:21 — richie, Apr 07 '14 at 13:21
@Bathsheba's got a fine answer though – richie Apr 07 '14 at 13:41 — richie, Apr 07 '14 at 13:41

score 3 · Answer 1 · answered Apr 07 '14 at 13:22

3

That's the way Excel works I'm afraid. =B1 will display a zero if B1 is empty. Futhermore ISNNUMBER(B1) will return True. So it is a genuine zero, not something formatted as a zero.

A common workaround is to use something like:

=IF(ISBLANK(B1), "", B1)

which, I think, is more elegant than an empty string comparison.

answered Apr 07 '14 at 13:22

Bathsheba

231,907
34
361
483

Well it is very similar to me. I was thinking in something simpler, such as "=COPY(B1)", or even "==B1". – freesoft Apr 08 '14 at 09:20

score 0 · Answer 2 · edited Mar 07 '15 at 10:25

0

Google spreadsheet works exactly as you need. Using

=A1

in B1, you get the desired empty B1 when A1 is empty.

edited Mar 07 '15 at 10:25

Alexander Vogt

17,879
13
52
68

answered Mar 07 '15 at 09:54

J.Ty.

16
1

score 0 · Answer 3 · answered Mar 07 '15 at 10:26

Under Excel Options > Advanced > Display options for this worksheet, is a tick box called 'Show a Zero in Cells that have zero value'. If you un-tick it then your formula will display blank if they result in zero. Only downside is that all cells that have a zero value (not from a formula) will also display blank.

Excel 2010: Set cell value to same value as other cell, even if cell is empty

3 Answers3