34

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.

Improve this question
2
  • My formula is very long, for me also the IF construction is hardly acceptable, good question but I too did not find good enough answer. {= IFERROR( INDEX(NEPŘÍMÝ.ODKAZ("EsZkouska");SMALL(KDYŽ((INDEX(NEPŘÍMÝ.ODKAZ("EsZkouska");;1;1)="ČSN721180")*(INDEX(NEPŘÍMÝ.ODKAZ("EsZkouska");;9;1)="RC_P_B");ŘÁDEK(NEPŘÍMÝ.ODKAZ("EsZkouska"))-MIN(ŘÁDEK(NEPŘÍMÝ.ODKAZ("EsZkouska")))+1;"");1);17;1);"")}
    – Vojtěch Dohnal
    Jul 25, 2014 at 11:32
  • It is possible to use IF construction combined with this approach: stackoverflow.com/questions/22359452/…. Like this you can create named formulas and use them in your simple IF statement...
    – Vojtěch Dohnal
    Jul 25, 2014 at 11:42

20 Answers 20

Reset to default
62

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.

Improve this answer
3
  • 4
    Wow!  I can't believe that it turned out to be so simple!
    – Scott - Слава Україні
    Apr 29, 2015 at 22:03
  • 1
    How does the solution deal with formatting of number input? It seems to me as if this answer destroys numeric data. Sure - you dont get the 0 when the cell is empty, but you also get textual data instead of numeric... Is this an issue in practice or do you have any reassuring words?
    – LudvigH
    Mar 16, 2017 at 9:45
  • 1
    It stores it as text. However, when you use it in a math equation, Excel should convert it back to a number. In practice though, you would only deploy this solution on a report where you have already done the work behind th scenes on other worksheets or hidden columns.
    – wbeard52
    Mar 16, 2017 at 13:46
20

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.

Improve this answer
6
  • 1
    The first option is sub-optimal, as it requires entering (or copying) your formula twice within the cell. It can be especially nasty when you have a really long formula, and then need to edit (or worse, debug) that formula later. The second option doesn't seem to be working for me, but maybe I'm doing it wrong. The third is definitely not ideal since the scope of its effect is much broader than one might truly desire. Are there really no better options?
    – Iszi
    Sep 18, 2013 at 18:32
  • @Iszi: Regarding the first option: I’m not talking about entering a formula twice. As per the original question, I’m talking about a situation where Q1 contains a (really long) formula and A1 contains =Q1; we want to change A1 to =IF(Q1<>"", Q1, ""), which you should never need to change again. “Are there really no better options?” Well, this question has been idle for nine months, and this is all that has been posted. If these answers aren’t good enough for you, post a new question, or put a bounty on this one.
    – Scott - Слава Україні
    Sep 18, 2013 at 19:08
  • That's more or less the same thing. Though it does resolve some of the cumbersomeness of having to troubleshoot two instances of one formula within one cell, it also complicates things a bit by adding another (otherwise unnecessary) cell or group of cells to the mix. Unfortunately, I suppose there really isn't another "nice and clean" answer for this, though.
    – Iszi
    Sep 18, 2013 at 19:52
  • I did post a related question awhile back and the only worthwhile answer that's different from what's here involved a VBA script.
    – Iszi
    Sep 18, 2013 at 19:52
  • This formula IF(xx <> "", xx, "") is broken for cells that are longer than 255 characters in microsoft excel. Thanks microsoft.
    – Cyril Duchon-Doris
    Aug 10, 2020 at 13:53
5

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.

Improve this answer
2
  • Interesting.  Why do you think you need "" & A1 & ""?  When does that produce different results from A1 & "" or "" & A1?
    – G-Man Says 'Reinstate Monica'
    Apr 28, 2015 at 6:24
  • @G-man : correct, and preferable. Thanks I have updated answer.
    – rayzinnz
    Apr 28, 2015 at 22:48
5

There is another trick: set the soucre empty cell to formula ="". See the detailed description here.

Improve this answer
1
  • This is the perfect solution for me since the cells I'm referencing are fewer than the ones that reference them. Thanks!
    – Kit
    Jul 19, 2019 at 14:13
2

I too did not find a better solution than the Scott's one.

But combined with the approach from here it could be almoste bearable, I think:

http://www.ozgrid.com/Excel/named-formulas.htm

Let's say I have formula like this

= IFERROR( INDEX(INDIRECT("EsZkouska");
  SMALL(IF((INDEX(INDIRECT("EsZkouska");;1‌​;1)="ČSN721180")*(INDEX(INDIRECT("EsZkouska");;9;1)="RC_P_B");
  ROW(INDIRECT"EsZkouska"))-MIN(ROW(INDIRECT("EsZkouska")))+1;"");1);17;1);"")

This formula reads cell value from a data sheet using conditional select and presents them on another sheet. I have no control over cell formating on the data sheet.

I go to Insert > Name > Define and in the "Names in workbook" I create new Name "RC_P_B". Then into the "Refers to" field I copy my formula (without {} characters - it is array formula).

Then you can use Scott's formula whithout having to repeat the whole formula text:

 {=IF(RC_P_B<>""; RC_P_B;"---")}

I believe this is better than to copy the whole formula.

Improve this answer
2

I simple don't use a formula to overcome this problem. All I do is to conditionally format cells to font color white if cell value equals 0.

Improve this answer
2
  • 5
    This has the obvious issue with cells which have a legit 0 value.
    – Dmitry Grigoryev
    Oct 2, 2015 at 22:57
  • And it also has the downside of slowing down calculations, especially for large spreadsheets, as conditional formatting formulas are "super" volatile. (They are also evaluated every time the screen is repainted.)
    – robinCTS
    Jun 4, 2018 at 11:01
1

If the linked cell is non-numeric you can use an IF statement with ISTEXT:

=IF(ISTEXT(Sheet1!A2), Sheet1!A2, "")
Improve this answer
2
  • 1
    But if the referenced cell (Sheet1!A2, in your example) contains a number, this will display a null string.
    – Scott - Слава Україні
    Dec 6, 2012 at 21:35
  • Yeah it wasn't clear from the question what data is in the referenced cell. I figured if it was numeric he would want the zeros.
    – Brad Patton
    Dec 6, 2012 at 21:36
1

I have long searched for an elegant solution to this issue.

I have used formula =if(a2="","",a2) for years, but I find it to be a bit cumbersome.

I tried above suggestion =a2&"" and although it does appear to work it shows a number that is actually text so number formatting cannot be applied and no statistical operations work, such as sum, average, median, etc., so if it's workable numbers you're looking for this doesn't fit the bill.

I experimented with some of the other functions and found what I think is the most elegant solution to date. Continuing the above example:

=CELL("contents",A2)

returns a numerical value that can be formatted as a number and it returns a blank when the referenced cell is blank. For some reason, this solution doesn't seem to appear in any of the online suggestions I've found, but it

Improve this answer
1
  • 1
    I just tried this in Excel 2013.  When A2 is blank, this displays 0.  Are you sure you're not doing anything with the formatting?
    – Scott - Слава Україні
    Jun 11, 2015 at 4:09
1

Solution for Excel 2010:

  1. Open "File"
  2. Press "Options"
  3. Click "Advanced"
  4. Under "Display Options" for this worksheet "BBBB"
  5. Untick the box "Show a zero value for cells that have a zero value."
Improve this answer
1
1

If you have a newer version of Excel that supports lambdas, you can write one as follows:

  1. Define a new name in the Name Manager, say, Ditto.
  2. In the Refers to: field, type =LAMBDA(x, IF(x <> "", x, "")).
  3. Hit OK to save the lambda.
  4. Use Ditto as a regular function; e.g., =Ditto(A5) will copy the contents of A5 but leave a blank as blank.

This method appears to work regardless of cell type or formatting. It's similar to =IF(A5 = "", "", A5), a method I've used for years, but shorter and less prone to typos.

Writing a lambda in the name manager

Improve this answer
0 
Public Function FuncDisplayStringNoValue(MyCell As Variant) As String

Dim Result As Variant

If IsEmpty(MyCell) Then

   FuncDisplayStringNoValue = "No Value"

Else
   Result = CDec(MyCell)

   Result = Round(Result, 2)

   FuncDisplayStringNoValue = "" & Result

   End If

End Function
Improve this answer
0

I have a solution working for me:

IF(cell reference="","",cell reference)

Explanation:

"" is equal to blank or empty cell. Now I only turn the if around. If cell is blank, make it blank, otherwise use the cell reference.

Improve this answer
1
0

I used conditional formatting to format the font to be the same color as the cell background where the cell value was equal to zero.

Improve this answer
2
0

You can use the expression =IF(ISBLANK(Ref Cell),"",Ref Cell)

If you have a long expression, use the following format: =IF(Ref Cell<>"",Ref Cell, "")

Improve this answer
1
  • 1
    Welcome to Super User! Before answering an old question having other answers ensure your answer adds something new or is otherwise helpful in relation to them. Here is a guide on How to Answer. There is also a site tour and a help center.
    – help-info.de
    Jan 12 at 9:48
0

I'm surprised no one has mentioned this method:

=IF(ISBLANK(A1),TEXT(A1,"#"),A1)

Essentially the same trick as changing the data format, but this method is formulaec and thus you can preserve the cell data format type and only output a blank cell in the case that the source cell is blank; therefore, having a legitimate "0" in the source cell will still output a "0" as the output.

Improve this answer
-1

I had a similar problem, and I think I found a method for you. It fixed my issues.

If you want column C to reference column A, and only write a formula for the reference cell, you would use this and drag down the column.

=A1

However, there may be source cells in column A that are blank and need to remain blank in the reference cells in column C, you can use this and drag the down column.

=T(A1)

As far as cell formatting goes, the reference (columnn C) will need to be general.

Improve this answer
5
  • Alas, this was a drive-by question — the OP came to Super User (almost three years ago), asked this one question, and never came back.  So, we may never know the details of his situation.  But, a major (if not the major) function of Excel is to crunch numbers.  If the source data (column A, in your example) contains numbers, your answer fails to populate the target cells with the numeric data.
    – Scott - Слава Україні
    Sep 1, 2015 at 11:36
  • Make sure your cell formating in the cells containing the =T () formula is General rather than Text. Because where I've used it, it totally shows numbers.
    – Will Prater
    Sep 2, 2015 at 20:39
  • I checked.  It's General, and it's coming up blank.  (Excel 2013)
    – Scott - Слава Україні
    Sep 2, 2015 at 21:01
  • The problem could be that you're using 2013, my answer I'd for 2010.
    – Will Prater
    Sep 3, 2015 at 22:38
  • There was an answer (now deleted; you can see it only if your rep is ≥10000) that reported that this didn’t work in Excel 2010 either.
    – Scott - Слава Україні
    Jun 23, 2017 at 20:24
-2

What worked for me in Office 2013 was: 

=IF(A2=""," ",A2)

Where on the first set of quotations there is no space, and on the second set there is a space.

Hope this helps

Improve this answer
1
  • Why do you need the space in the second set of parentheses? Doesn't =IF(A2="", "", A2) work just as well? How would that be different from =IF(A2<>"", A2, "")? Then how is that different from the highest-voted answer, which was given over two years ago?
    – G-Man Says 'Reinstate Monica'
    Mar 17, 2015 at 22:44
-2

Go to Format Cells.

Then select custom format, and enter the following: 0;-0;;@

Improve this answer
3
  • 2
    Can you explain what this does? Thanks.
    – fixer1234
    Sep 25, 2015 at 20:18
  • 1
    This does pretty much the same thing as the second option of my answer to this question («Set the “Number” format of your linked cells to “Custom”: General;–General;. ») — namely (a) if the value is a positive number, display its integer absolute value; (b) if the value is a negative number, display its integer absolute value, preceded by -; (c) if the value is zero, display nothing; and (d) if the value is not a number, display its text value.  … (Cont’d)
    – Scott - Слава Україні
    Sep 25, 2015 at 21:26
  • 1
    (Cont’d) …  However, this version has the drawback (i.e., error) that it rounds non-integer numbers to the nearest integer.  (General gives you the default format for numbers, including decimal digits as necessary (but not including the - sign).)  Also, the final ;@ seems to be unnecessary (i.e., it seems to be the default), inasmuch as my answer correctly displays text values without modification.
    – Scott - Слава Україні
    Sep 25, 2015 at 21:26
-2

Solution:
=IF('Wk1 Data-replace w dt bgn & end'!C2>0, 'Wk1 Data-replace w dt bgn & end'!C2, "")

Explanation:
To solve this problem I used two sets of If commands.

Part one of command:
I told the cell to display the contents of the reference cell if the reference cell contained data (Excel interprets this as the cell having a value larger than 0)

Part two of command:
I told it how to behave if there was no data; "" means leave blank

Note: Wk1 Data-replace w dt bgn & end'!C2 is my reference cell.

Improve this answer
1
  • (1) This will display blank even if the referenced cell contains 0 or a negative number.  The question wants the linking cell to be blank only if the referenced cell is blank.  (2) If you make the obvious change and change this to =IF('Wk1 … bgn & end'!C2<>"", 'Wk1 … bgn & end'!C2, ""), it becomes equivalent to an answer that has been given approximately four times already.
    – G-Man Says 'Reinstate Monica'
    Nov 30, 2015 at 6:41
-2

All these solutions are way too complicated for me and I realize this may not be a feasible for all applications but I just fill the source field with a blank and i get a blank in the destination field.

Improve this answer
2
  • 1
    How do you fill a cell with a blank, and what do you mean by a blank?
    – fixer1234
    Aug 13, 2016 at 21:53
  • Excuse me I should have said a space character. That way it cell isn't actually empty. Tha source cell has a space in it and the space gets to the copied into destination cell.
    – John Alonso
    Aug 28, 2016 at 23:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .