Return empty cell from formula in Excel

Question

I need to return an empty cell from an Excel formula, but it appears that Excel treats an empty string or a reference to an empty cell differently than a true empty cell. So essentially I need something like

=IF(some_condition,EMPTY(),some_value)

I tried to do things such as

=IF(some_condition,"",some_value)

and

=IF(some_condition,,some_value)

and assuming B1 is an empty cell

=IF(some_condition,B1,some_value)

but none of these appear to be true empty cells, I'm guessing because they are the result of a formula. Is there any way to populate a cell if and only if some condition is met and otherwise keep the cell truly empty?

EDIT: as recommended, I tried to return NA(), but for my purposes this did not work either. Is there a way to do this with VB?

EDIT: I am building a worksheet that pulls in data from other worksheets that is formatted to the very specific demands of an application that imports the data into a database. I do not have access to change the implementation of this application, and it fails if the value is "" instead of actually empty.

Luncheon

Can you explain why the cell needs to be blank? Depending on what "blankness" gets you, there may be a workaround. – Dalury

The cell contains the formula, doesn't it? How can it be empty or blank then? – Rainband

I have a similar problem, I am drawing a graph and do not want to show the value 0 for blank items on the graph. If the records are empty cells it omits them from the graph but any of the methods listed in the "Answers" below results in 0's being shown on the graph. :( – Jessi

To avoid zeroes from being shown on graphs, use the NA() function instead of the empty string/zero. This will put #N/A in the cell, which is ignored by the graphing routine. – Lavonnelaw

Rob's suggestion to use NA() function worked for me in the graphing situation described by @Jessi – Immortalize

Rob's suggestion to use #N/A has a different result to an empty cell. #N/A will result in the graphing routine interpolating over the cell whereas a truely empty cell will be treated as a gap in the line. If you want a GAP in the line rather than INTERPOLATION accross the gap you need the cell to be EMPTY and not #N/A as per the question. There are solutions below which do address this as asked. – Deaminate

Well - actually, I get quite close to requirement - instead of function displaying I can retrieve the result of the function under desired conditions - and a BLANK cell (completely empty otherwise)..This utilises Excel's list function - where the desired condition looks the function result then retrieves it in such a manner that it 'spills' over into an adjacent cell. It is this adjacent cell that solves the problem (in general terms). Slicers can achieve a similar effect. Find my solution somewhere at the bottom - coming late to this party! – Madder

Answer 1

You're going to have to use VBA, then. You'll iterate over the cells in your range, test the condition, and delete the contents if they match.

Something like:

For Each cell in SomeRange
  If (cell.value = SomeTest) Then cell.ClearContents
Next

Dalury

I would add to this: if you always have a particular range of cells you want to clear out the blank cells for, you could give that range a name, then modify Boofus' formula by changing SomeRange to Range("MyRange"). To set a name for your cells, select the cells, click Define Name on the Formulas tab of the ribbon, and enter "MyRange" in the Name field. (And of course you could replace MyRange with anything you want.) – Molar

I wound up using a slight modification to this solution. I then set it to be run before the file is saved and everything works wonderfully. – Luncheon

That's intense that there's no null constant in Excel. – Undercroft

It's worth looking at Gentle Knight's solution below, if you are looking for something that i) Does not use VBA ii) Works for graphs, iii) Leaves the cell looking empty iv) Doesn't require manual steps each time the data changes. (!!) – Obovoid

If you put the macro in the worksheet_calculate() function then it will automatically empty the cells. For specific detail you can see my answer below. – Deaminate

@Undercroft there is VbNullString – Zombie

@Zombie That's VBA, not Excel. We have lobbied long and hard for some kind of function like NULL() or BLANK(), to no avail. – Jillian

@JonPeltier yes, but comment was made with reference to a VBA solution rather than stating it could be made as a formula. – Zombie

@Zombie Actually, the original question offered a bunch of worksheet formulas that don't return empty cells. In VBA you can always clear a cell and maybe recreate the formula if it should no longer be blank, but that's kludgy and not really profound. Having a NULL() worksheet formula at our disposal would mean we wouldn't need to invoke VBA. – Jillian

@JonPeltier I'm fully supportive of having a null formula option. I'm also comfortable that my earlier post is clearly related to the VBA answer it was posted under. – Zombie

Answer 2

Excel does not have any way to do this.

The result of a formula in a cell in Excel must be a number, text, logical (boolean) or error. There is no formula cell value type of "empty" or "blank".

One practice that I have seen followed is to use NA() and ISNA(), but that may or may not really solve your issue since there is a big differrence in the way NA() is treated by other functions (SUM(NA()) is #N/A while SUM(A1) is 0 if A1 is empty).

Keener

The NA() method works 100% for graphs that are set to show empty cells as gaps, this will likely not work for your case where you are exporting to an application which needs the cell to be blank as it contains a formula ... – Jessi

There is no perfect solution. NA() is one good option. Another is the empty string of (depending) '' or "" – Singleminded

This works for the purpose of returning empty cells dinamically in data that is going to be plotted, so that you can effectively use the option of ignoring empty cells as gaps in the graphs. In my case, I was trying to plot 12 values to represent a monthly progression, but only up to previous month, with a formula like this: =IF(MONTH(TODAY())>C2;$C$11+C7;NA()) – Key

This worked for me. I have a chart with data that I add to every day - a burndown chart for scrum and I wanted to see the line only extend to the current day. This helped me do that - using NA() in an IF statement. Thanks! – Indispose

We've discovered when importing data via SSIS that if you press the 'delete' key on a call (i.e. that previously contained an empty string) then SSIS imports a NULL value. So there is some way for Excel to represent this. It just appears it can't be set via a formula – Jarret

NA() will not result in a gap. Everyone gets this wrong. Excel will not plot a marker if the value is NA() or #N/A, but it will draw a line interpolated between adjacent non-#N/A values. – Jillian

Answer 3

Yes, it is possible.

It is possible to have a formula returning a trueblank if a condition is met. It passes the test of the ISBLANK formula. The only inconvenience is that when the condition is met, the formula will evaporate, and you will have to retype it. You can design a formula immune to self-destruction by making it return the result to the adjacent cell. Yes, it is also possible.

All you need is to set up a named range, say GetTrueBlank, and you will be able to use the following pattern just like in your question:

=IF(A1 = "Hello world", GetTrueBlank, A1)

Step 1. Put this code in Module of VBA.

Function Delete_UDF(rng)
    ThisWorkbook.Application.Volatile
    rng.Value = ""
End Function

Step 2. In Sheet1 in A1 cell add named range GetTrueBlank with the following formula:

=EVALUATE("Delete_UDF("&CELL("address",Sheet1!A1)&")")

That's it. There are no further steps. Just use self-annihilating formula. Put in the cell, say B2, the following formula:

=IF(A2=0,GetTrueBlank,A2)

The above formula in B2 will evaluate to trueblank, if you type 0 in A2.

You can download a demonstration file here. It opens in Google Sheets and will not work there. Download it first to get xlsm file work in Excel.

In the example above, evaluating the formula to trueblank results in an empty cell. Checking the cell with ISBLANK formula results positively in TRUE. This is hara-kiri. The formula disappears from the cell when a condition is met. The goal is reached, although you probably might want the formula not to disappear.

You may modify the formula to return the result in the adjacent cell so that the formula will not kill itself. See how to get UDF result in the adjacent cell.

Oday

This is f***ing sweet workaround, although it's pretty crazy that there is no way to do this without physically emptying the cell. The only disadvantage here would be that you'd have to remember to re-drag the formula down when recalculating, although I guess you could set a workbook event to do that. – Eckel

Before you attempt to use the sweet workaround, please note it may backfire. – Otoole

@PrzemyslawRemin No, I'm saying various random things may happen, including crashing Excel. – Otoole

Seems like you can use a helper column to watch A2. And if A2 changes the helper column can dump that formula into B2, ready for A2 to change back to zero. – Yvonneyvonner

Answer 4

Maybe this is cheating, but it works!

I also needed a table that is the source for a graph, and I didn't want any blank or zero cells to produce a point on the graph. It is true that you need to set the graph property, select data, hidden and empty cells to "show empty cells as Gaps" (click the radio button). That's the first step.

Then in the cells that may end up with a result that you don't want plotted, put the formula in an IF statement with an NA() results such as =IF($A8>TODAY(),NA(), *formula to be plotted*)

This does give the required graph with no points when an invalid cell value occurs. Of course this leaves all cells with that invalid value to read #N/A, and that's messy.

To clean this up, select the cell that may contain the invalid value, then select conditional formatting - new rule. Select 'format only cells that contain' and under the rule description select 'errors' from the drop down box. Then under format select font - colour - white (or whatever your background colour happens to be). Click the various buttons to get out and you should see that cells with invalid data look blank (they actually contain #N/A but white text on a white background looks blank.) Then the linked graph also does not display the invalid value points.

Uranometry

NO!!! "show empty cells as Gaps" Requires that the cells are EMPTY. If they contain #N/A rather than NOTHING then the graphing routine will interpolate over the #N/A cells rather than leave them as gaps in the line. If you want the #N/A cells to be interpolated that's fine but in that case "show empty cells as Gaps" DOES NOT APPLY – Deaminate

@Obovoid This is not the real answer because it doesn't fit the OP's needs. There should really be a separate question for the graphing issue. – Dense

It works fine in Excel 2010 (=14.0): empty and N/A cells are ignored by chart. – Caithness

Answer 5

If the goal is to be able to display a cell as empty when it in fact has the value zero, then instead of using a formula that results in a blank or empty cell (since there's no empty() function) instead,

• where you want a blank cell, return a 0 instead of "" and THEN

• set the number format for the cells like so, where you will have to come up with what you want for positive and negative numbers (the first two items separated by semi-colons). In my case, the numbers I had were 0, 1, 2... and I wanted 0 to show up empty. (I never did figure out what the text parameter was used for, but it seemed to be required).

  0;0;"";"text"@
  

Powerboat

While this does not make the cell empty (there's no way for a formula to result in an empty cell), it makes it look empty if the number is zero. However, it's incorrect. It should be 0;-0;"" (note the minus sign) or even 0;-0; without any quotes at all. The third semicolon and the "text"@ part are not required. The meaning of the semicolon-delimited fields is: how to display positive numbers, negative numbers, zero, and text (the latter format must contain an @). office.microsoft.com/en-gb/excel-help/… – Sake

You are a god sir. You saved me from visual basic. You rule – Gastrotrich

This format does not append "text" to strings with text data: 0;0;"";@ – Eweneck

Answer 6

This is how I did it for the dataset I was using. It seems convoluted and stupid, but it was the only alternative to learning how to use the VB solution mentioned above.

1. I did a "copy" of all the data, and pasted the data as "values".
2. Then I highlighted the pasted data and did a "replace" (Ctrl-H) the empty cells with some letter, I chose q since it wasn't anywhere on my data sheet.
3. Finally, I did another "replace", and replaced q with nothing.

This three step process turned all of the "empty" cells into "blank" cells". I tried merging steps 2 & 3 by simply replacing the blank cell with a blank cell, but that didn't work--I had to replace the blank cell with some kind of actual text, then replace that text with a blank cell.

Rodrigo

While this may have worked in your case, I needed a solution which could be applied automatically. Ultimately, I think I used a VB macro to erase things which were determined to be empty. This macro was run right before saving the file. – Luncheon

this was best for me because I don't need it to be automatic, but I need it to be quick. Essentially, it means that Excel respects a blank value added via Find and Replace, which is good enough for me! – Casa

Answer 7

Use COUNTBLANK(B1)>0 instead of ISBLANK(B1) inside your IF statement.

Unlike ISBLANK(), COUNTBLANK() considers "" as empty and returns 1.

Peloria

Unfortunately it seems Excel rejects =COUNTBLANK(IFNA(VLOOKUP(...),"")) as invalid (!) – Helico

Answer 8

Wow, an amazing number of people misread the question. It's easy to make a cell look empty. The problem is that if you need the cell to be empty, Excel formulas can't return "no value" but can only return a value. Returning a zero, a space, or even "" is a value.

So you have to return a "magic value" and then replace it with no value using search and replace, or using a VBA script. While you could use a space or "", my advice would be to use an obvious value, such as "NODATE" or "NONUMBER" or "XXXXX" so that you can easily see where it occurs - it's pretty hard to find "" in a spreadsheet.

Apeldoorn

Yep thats what I did. I used a function to generate "#EMPTY" then poped some code in the "worksheet calculate" event subroutine. Check my answer for the full solution. – Deaminate

Answer 9

Try evaluating the cell using LEN. If it contains a formula LEN will return 0. If it contains text it will return greater than 0.

How

Although this doesn't help the original asker, this isn't actually a bad answer in many cases. It works well for cases where you've been forced to return "". – Ancient

Great answer but a bit confusing. The cell I want to test returns a number or "" or is empty. LEN(cell)>0 returns true if there's a number (including zero, and including if the number is calculated by a formula), and false if empty string or empty. – Sappington

Answer 10

So many answers that return a value that LOOKS empty but is not actually an empty as cell as requested...

As requested, if you actually want a formula that returns an empty cell. It IS possible through VBA. So, here is the code to do just exactly that. Start by writing a formula to return the #N/A error wherever you want the cells to be empty. Then my solution automatically clears all the cells which contain that #N/A error. Of course you can modify the code to automatically delete the contents of cells based on anything you like.

Open the "visual basic viewer" (Alt + F11) Find the workbook of interest in the project explorer and double click it (or right click and select view code). This will open the "view code" window. Select "Workbook" in the (General) dropdown menu and "SheetCalculate" in the (Declarations) dropdown menu.

Paste the following code (based on the answer by J.T. Grimes) inside the Workbook_SheetCalculate function

    For Each cell In Sh.UsedRange.Cells
        If IsError(cell.Value) Then
            If (cell.Value = CVErr(xlErrNA)) Then cell.ClearContents
        End If
    Next

Save your file as a macro enabled workbook

NB: This process is like a scalpel. It will remove the entire contents of any cells that evaluate to the #N/A error so be aware. They will go and you cant get them back without reentering the formula they used to contain.

NB2: Obviously you need to enable macros when you open the file else it won't work and #N/A errors will remain undeleted

Deaminate

If you need it to be fully automatic/handsfree/dynamic and you dont want to re enter the formula to generate the #NA error you could rewrite the macro code to duplicate the sheet in question leaving empty cells in place of #NA then generate graphs from the duplicate sheet. (I'll add the code if this comment is ever upvoted). – Deaminate

Answer 11

What I used was a small hack. I used T(1), which returned an empty cell. T is a function in excel that returns its argument if its a string and an empty cell otherwise. So, what you can do is:

=IF(condition,T(1),value)

Bordiuk

Creates a zero length string (ISBLANK(CELL) = FALSE). – Pierpont

This works and should be the accepted answer. Thanks much, Ahmed. My use case is a bit different. I want to leave cells empty so that aggregate functions like AVERAGE(), MIN(), MAX(), STDEV(), COUNT() and MEDIAN() will ignore values. – Fertilizer

@CharlieReitzel It is the same as just using "" instead. – Helico

Answer 12

This answer does not fully deal with the OP, but there are have been several times I have had a similar problem and searched for the answer.

If you can recreate the formula or the data if needed (and from your description it looks as if you can), then when you are ready to run the portion that requires the blank cells to be actually empty, then you can select the region and run the following vba macro.

Sub clearBlanks()
    Dim r As Range
    For Each r In Selection.Cells
        If Len(r.Text) = 0 Then
            r.Clear
        End If
    Next r
End Sub

this will wipe out off of the contents of any cell which is currently showing "" or has only a formula

Proliferous

Answer 13

I used the following work around to make my excel looks cleaner:

When you make any calculations the "" will give you error so you want to treat it as a number so I used a nested if statement to return 0 istead of "", and then if the result is 0 this equation will return ""

=IF((IF(A5="",0,A5)+IF(B5="",0,B5)) = 0, "",(IF(A5="",0,A5)+IF(B5="",0,B5)))

This way the excel sheet will look clean...

Intercross

This is not a solution to the problem. The sheet looks clean, but the value "" is not "empty" as far as graphs are concerned. – Obovoid

Answer 14

The answer is positively - you can not use the =IF() function and leave the cell empty. "Looks empty" is not the same as empty. It is a shame two quotation marks back to back do not yield an empty cell without wiping out the formula.

Letaletch

If you are prepared to use VBA you can. As per some other answers. – Deaminate

Two empty quotation marks shouldn't yield an empty cell, they should yield a zero length string, as they do. But it sure would be nice to have a BLANK() function. – Helico

Answer 15

Well so far this is the best I could come up with.

It uses the ISBLANK function to check if the cell is truly empty within an IF statement. If there is anything in the cell, A1 in this example, even a SPACE character, then the cell is not EMPTY and the calculation will result. This will keep the calculation errors from showing until you have numbers to work with.

If the cell is EMPTY then the calculation cell will not display the errors from the calculation.If the cell is NOT EMPTY then the calculation results will be displayed. This will throw an error if your data is bad, the dreaded #DIV/0!

=IF(ISBLANK(A1)," ",STDEV(B5:B14))

Change the cell references and formula as you need to.

Nissie

The problem with this is that you then have to propagate this check to every other cell using this result. – Draughts

Answer 16

If you are, like me, after an empty cell so that the text in a cell can overflow to an adjacent cell, return "" but set the cell format text direction to be rotated by a few degrees. If you align left, you will find this causes the text to spill to an adjacent cell as if that cell were empty.

See before and after:

=IF(RANDARRAY(2,10,1,10,TRUE)>8,"abcdefghijklmnopqrstuvwxyz","")

Note the empty cells are not empty but contain "", yet the text can still spill.

Gink

almost perfect - I also want to extend text into adjacent cells - however I have a formula returning an array in the top left corner - so the rest of the suggestions don't work for me - This does - with an angle of 1 degree its ok – Lorrinelorry

Answer 17

If you are using lookup functions like HLOOKUP and VLOOKUP to bring the data into your worksheet place the function inside brackets and the function will return an empty cell instead of a {0}. For Example,

This will return a zero value if lookup cell is empty:

    =HLOOKUP("Lookup Value",Array,ROW,FALSE)

This will return an empty cell if lookup cell is empty:

    =(HLOOKUP("Lookup Value",Array,ROW,FALSE))

I don't know if this works with other functions...I haven't tried. I am using Excel 2007 to achieve this.

Edit

To actually get an IF(A1="", , ) to come back as true there needs to be two lookups in the same cell seperated by an &. The easy way around this is to make the second lookup a cell that is at the end of the row and will always be empty.

Iona

Answer 18

I was stripping out single quotes so a telephone number column such as [phone] didn't result in a computation and yielding a negative number. I attempted a hack to remove them on empty cells, bar the quote, then hit this issue too (column F). It's far easier to just call text on the source cell and voila!:

=IF(F2="'","",TEXT(F2,""))

Myrna

Answer 19

This can be done in Excel, without using the new chart feature of setting #N/A to be a gap. But it’s fiddly. Let’s say that you want to make line on an XY chart. Then:
Row 1: point 1
Row 2: point 2
Row 3: hard empty
Row 4: point 2
Row 5: point 3
Row 6: hard empty
Row 7: point 3
Row 8: point 4
Row 9: hard empty
etc

The result is a lot of separate lines. The formula for the points can control whether omitted by a #N/A. Typically the formulae for the points INDEX() into another range.

Crosstie

Answer 20

I also found this through Google, and found a solution not in returning a true null value (because that can't be done) and not via VBA (because the web-based Excel365 doesn't support VB) but via IFERROR handling in the downstream function.

My own issue was in array operations over a range of formula cells where only a subset were "active". Each cell in the range returned a numeric value if it was active, and a ""empty string otherwise, e.g. each having some formula like this from my cell AK32 in it: = IF(ISNUMBER(AG32), MAX(AG32,MIN(12,AI32)), "") -- please don't critique this, it's an illustrative minimal version of the real one.

The problem is that if I then aggregate those conditionally calculated cells in row 32, and try to sum them, I get #VALUE! errors should there be any "" results due to empty optional inputs in e.g. AG32. If they'd been literal empty cells, that would have been fine, but a "" is neither empty nor numeric.

This issue can be handled in my downstream aggregation function by combination of VALUE() and ISERROR(): try to convert/cast the range to be numeric-valued, catch the error-cases, and replace them with zeroes: = SUM( IFERROR(VALUE(AK32:OQ32),0) ). [To anticipate "helpful" comments, I've elided a bunch of FILTER() stuff that picks the right cells out of that AK32:OQ32 range. Treat it as if I really want to sum everything in that contiguous range.]

I pine for some more modern, Numpy/Pandas type of array-operation language to be usable in (web) Excel formulae, but at least this one is possible.

Afterburner

Answer 21

Google brought me here with a very similar problem, I finally figured out a solution that fits my needs, it might help someone else too...

I used this formula:

=IFERROR(MID(Q2, FIND("{",Q2), FIND("}",Q2) - FIND("{",Q2) + 1), "")

Wasting