Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Returning Zero when a Referenced Cell is Blank.
Written by Allen Wyatt (last updated September 3, 2021)
This tip applies to Excel 2007, 2010, 2013, and 2016
If you have a formula in a worksheet, and the cell referenced by the formula is blank, then the formula still returns a zero value. For instance, if you have the formula =A3, then the formula returns the contents of cell A3, unless cell A3 is blank. In that case, the formula returns a value of zero.
This seems to be related to the idea that it is impossible for a formula to return a blank value, when "blank" is used synonymously with "empty." You can, however, expand your formula a bit so that it returns an empty string. Instead of using =A3 as your formula, you would use the following:
=IF(ISBLANK(A3),"",A3)
This formula uses ISBLANK, which returns either True or False, depending on whether the referenced cell (A3) is blank or not. The IF function then returns an empty string ("") if A3 is blank, or it uses the value in A3 if A3 is not blank.
Regardless of what the formula returns, you can still use its result in other formulas, and it will work fine. Even if it returns an empty string, it is still treated by other formulas as if it contained zero. In areas where treating the cell as if it contained zero might be problematic (such as when you are charting the results of the formula), then you can modify the formula a bit, as shown here:
=IF(ISBLANK(A3),NA(),A3)
This formula returns the #N/A error if A3 is blank. This error propagates through other formulas that reference the formula, but the #N/A error is ignored completely when charting.
While the above solutions are satisfactory for most people, some people would really like to see a target cell be truly blank if the source cell is blank. For instance, you might want cell B7 to be blank if cell A3 is blank. If you put a formula in cell B7 (as already discussed), then cell B7 is not truly blank—it contains a formula.
If this is your goal—true "blankness"—then you can only achieve it through the use of a macro. The macro will need to check to see if the source cell was changed. If it was, then whatever is in the source needs to be copied to the target cell.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rMonitor As Range
Dim rTarget As Range
Set rMonitor = Range("A3")
Set rTarget = Range("B7")
If Not Intersect(Target, rMonitor) Is Nothing Then
rMonitor.Copy rTarget
End If
Set rMonitor = Nothing
Set rTarget = Nothing
End Sub
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10105) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Returning Zero when a Referenced Cell is Blank.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Formulas are made up of operands that separate a series of terms acted upon by the operands. You may want to know, for ...
Discover MoreIf you use serial numbers that include both letters and numbers, you might wonder how you can increment the numeric ...
Discover MoreDo you ever have a need to return just a few digits out of a number? This tip shows different formulas you can use to ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-09-03 13:10:49
David Bonin
I take a similar, though different, approach.
Most of my Excel work is for other people in a corporate environment -- often people I don't even know.
I never want to show an error value when a calculation doesn't have enough values to work correctly. Why? Because some users will assume the spreadsheet is broken.
I also never want to calculate an answer if the formula is missing some values. Why? Because some users will not notice the calculated value is not complete.
Here's an example... Suppose we put this formula in cell D1:
= ( A1 + B1 ) / C1
If C1 is blank or zero, we get an error.
If A1, B1 or both A1 and B1 are blank, we get a misleading answer.
Neither of these cases are good, so here's the alternate formula I would enter in D1:
= IF( COUNT( A1, B1, C1 ) = 3, (A1 + B1 ) / C1, "•" )
This formula will not give a numeric answer until A1, B1 and C1 all have values. The dot character is created by holding the Alt key down and typing 0149 (Alt+0149). I use the dot as it is unique and won't get confused with anything else. You can use whatever you like, such as "- -", "—", "n/a"...
Notice that I did not return a blank, ie: "". Why? Several reasons. Seeing a dot character tell me that there is indeed a formula in that cell and that I didn't accidentally delete it. On a large, complex spreadsheet, it's easy to accidentally delete something. (Ask me how I know.)
It also tells the user there is a formula in that cell and that he probably should not enter his own value there.
If there is a possibility the user might enter a zero in C1, then I might make the formula a bit more robust:
= IF( COUNT( A1, B1, C1 ) = 3, if( C1 <> 0, (A1 + B1 ) / C1, "Err: C1 = 0!" ), "•" ) << or >>
= IFERROR( IF( COUNT( A1, B1, C1 ) = 3, if( C1 <> 0, (A1 + B1 ) / C1, "•" ), "Err: C1 = 0!" )
You can put in whatever error message is most meaningful to your user.
To go a step further, an acre of dark dots can be distracting, so I usually apply a custom number format. If my usual number format would be "0.00;-0.00;0_._0_0;@", then I would use the similar "0.00;-0.00;0_._0_0;[COLOR16]@" so that the dots are light gray. Dots are still visible, but they're now unobtrusive.
There are other color codes available. Look them up. I use custom number formats instead of conditional formats when I can because custom number formats are more stable and they don't "breed".
(Back in my FORTRAN days, I always hated seeing a whole printed page of mostly 0.000 values with a few on-zero numbers sprinkled among them. I actually tweaked my formatting so that insignificant zeros just showed up as a decimal point " . " to make them less distracting.)
2020-12-02 00:12:45
SteveF
I've been using Excel since the early 90's. This is a recent change in how Excel handles blank cells and NOT a welcome change. Until just recently, you could always expect to get a "" returned from an empty cell when referenced from another cell. Now they are converting the data type from the STRING you expect to a DOUBLE numeric of value 0. THIS IS COMPLETE BS and it is breaking thousands if not millions of older spreadsheets.
I don't understand any possible rationale for making this change to Excel. A completely misguided step backwards.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2022 Sharon Parq Associates, Inc.
Comments