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.

Can you explain why the cell needs to be blank? Depending on what "blankness" gets you, there may be a workaround. — J.T. Grimes, Jul 13 '09 at 15:10
The cell contains the formula, doesn't it? How can it be empty or blank then? — Sinan Ünür, Jul 13 '09 at 18:10
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. :( — Cobusve, Apr 29 '11 at 15:46
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. — Rob, Feb 13 '14 at 10:56
Rob's suggestion to use NA() function worked for me in the graphing situation described by @Cobusve — Jacob, May 09 '14 at 16:15
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. — Mr Purple, Aug 11 '14 at 21:04
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! — James, May 05 '21 at 03:29

Joe Erickson · Answer 1 · 2009-07-13T15:06:16.497

84

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).

edited Jul 13 '09 at 15:06

answered Jul 13 '09 at 14:09

Joe Erickson

6,836
1
29
31

22

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 ... – Cobusve Apr 29 '11 at 15:53
4

There is no perfect solution. NA() is one good option. Another is the empty string of (depending) '' or "" – Jason May 13 '11 at 11:28
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()) – EKI Nov 08 '13 at 20:13
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! – theJerm Feb 03 '15 at 19:52
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 – Nick.McDermaid May 21 '15 at 02:02
14

**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. – Jon Peltier Jul 11 '15 at 16:01

score 52 · Accepted Answer · edited Mar 31 '15 at 07:50

52

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

edited Mar 31 '15 at 07:50

brettdj

52,701
15
109
170

answered Jul 13 '09 at 18:08

J.T. Grimes

4,084
1
25
31

6

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.) – devuxer Jul 14 '09 at 01:47
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. – Bryan Ward Jul 14 '09 at 11:37
33

That's intense that there's no null constant in Excel. – ted.strauss May 06 '13 at 19:13
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. (!!) – GreenAsJade Jun 20 '14 at 01:31
5

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. – Mr Purple Aug 11 '14 at 23:09
3

@ted.strauss there is `VbNullString` – brettdj Mar 29 '15 at 02:20
7

@brettdj That's VBA, not Excel. We have lobbied long and hard for some kind of function like NULL() or BLANK(), to no avail. – Jon Peltier Jul 11 '15 at 15:58
@JonPeltier yes, but comment was made with reference to a VBA solution rather than stating it could be made as a formula. – brettdj Jul 12 '15 at 12:58
2

@brettdj 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. – Jon Peltier Jul 13 '15 at 14:42
1

@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. – brettdj Jul 14 '15 at 01:23

Przemyslaw Remin · Answer 3 · 2021-02-10T21:57:58.573

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. I refer you to this solution at the end of my answer.

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.

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.

I have come across the examples of getting a trueblank as a formula result revealed by The FrankensTeam here: https://sites.google.com/site/e90e50/excel-formula-to-change-the-value-of-another-cell

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. — Some_Guy, Jan 26 '17 at 17:45
Before you attempt to use the sweet workaround, please note it [may backfire](https://stackoverflow.com/a/3622544/11683). — GSerg, Oct 26 '19 at 19:35
@PrzemyslawRemin No, I'm saying various random things may happen, including [crashing Excel](https://stackoverflow.com/q/52698777/11683). — GSerg, Oct 26 '19 at 21:45
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. — jqning, Jan 18 '21 at 04:20

score 20 · Answer 4 · edited Apr 10 '18 at 06:49

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.

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 — Mr Purple, Aug 11 '14 at 21:47
@GreenAsJade 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. — ZX9, Jul 01 '15 at 15:50
It works fine in Excel 2010 (=14.0): empty and N/A cells are ignored by chart. — jumpjack, Apr 06 '21 at 11:48

score 12 · Answer 5 · edited Jun 02 '11 at 06:05

12

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"@
```

edited Jun 02 '11 at 06:05

ib.

25,324
10
71
97

answered Jun 01 '11 at 06:39

ET-X

129
1
2

4

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 @). http://office.microsoft.com/en-gb/excel-help/create-or-delete-a-custom-number-format-HP005199500.aspx – cvoinescu Apr 02 '13 at 09:52
You are a god sir. You saved me from visual basic. You rule – CommaToast May 20 '16 at 19:21

score 11 · Answer 6 · edited Jun 02 '11 at 06:02

11

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.

I did a "copy" of all the data, and pasted the data as "values".
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.
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.

edited Jun 02 '11 at 06:02

ib.

25,324
10
71
97

answered Jan 16 '11 at 18:28

jeramy

111
1
2

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. – Bryan Ward Jan 17 '11 at 20:36
5

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! – rickcnagy Feb 04 '14 at 05:23

score 8 · Answer 7 · edited Jan 22 '12 at 20:31

8

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.

edited Jan 22 '12 at 20:31

Alex

30,696
10
74
131

answered Sep 23 '09 at 16:30

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 "". – Technium Sep 24 '12 at 16:55
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. – Chris Aug 07 '19 at 10:52

score 7 · Answer 8 · answered Jun 24 '13 at 19:55

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.

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. — Mr Purple, Nov 08 '13 at 00:01

score 7 · Answer 9 · answered Apr 07 '16 at 09:33

7

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

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

answered Apr 07 '16 at 09:33

Kevin Vuilleumier

509
7
13

Unfortunately it seems Excel rejects =COUNTBLANK(IFNA(VLOOKUP(...),"")) as invalid (!) – NetMage Oct 12 '17 at 21:30

Mr Purple · Answer 10 · 2014-08-11T23:33:00.977

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

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). — Mr Purple, Feb 09 '16 at 20:34

score 4 · Answer 11 · answered Oct 22 '16 at 19:15

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

score 2 · Answer 12 · edited Sep 30 '13 at 07:17

2

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...

edited Sep 30 '13 at 07:17

kleopatra

49,346
26
88
189

answered Sep 30 '13 at 07:02

Big Z

21
2

1

This is not a solution to the problem. The sheet looks clean, but the value "" is not "empty" as far as graphs are concerned. – GreenAsJade Jun 20 '14 at 01:21

score 1 · Answer 13 · edited Dec 29 '12 at 19:29

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.

The problem with this is that you then have to propagate this check to every other cell using this result. — guthrie, Mar 16 '13 at 04:15

score 1 · Answer 14 · answered Aug 18 '19 at 14:55

1

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)

answered Aug 18 '19 at 14:55

Ahmed Shahid

191
2
9

5

Creates a zero length string (ISBLANK(CELL) = FALSE). – vinh Oct 11 '19 at 13:41
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. – Charlie Reitzel Feb 17 '20 at 23:17

score 1 · Answer 15 · edited May 16 '11 at 20:06

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.

score 0 · Answer 16 · answered Oct 07 '20 at 05:35

I was stripping out single quotes so a telephone number column such as +1-800-123-4567 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,""))

score 0 · Answer 17 · answered Apr 10 '21 at 09:47

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.

score 0 · Answer 18 · answered May 05 '21 at 03:42

♣ SOLVED (kind-of:) ♣

Following on from my comment to main Q - see application of lists under scenarios 1 and 2 in google sheets below. Not robust, but 'does the job' if the function output is of primary interest (that, and having ability to show completely blank cell under different set of condition(s)).

Screenshots:

Notes: never say never! ☻

Link to Google Sheets sample

Enjoy.

score 0 · Answer 19 · answered Oct 04 '11 at 15:35

0

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.

answered Oct 04 '11 at 15:35

Ken

17
1

If you are prepared to use VBA you can. As per some other answers. – Mr Purple Sep 13 '16 at 21:00

score -1 · Answer 20 · answered Feb 07 '17 at 22:48

-1

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), "")

answered Feb 07 '17 at 22:48

hamish

1,019
1
11
19

Return empty cell from formula in Excel

20 Answers20

♣ SOLVED (kind-of:) ♣

Linked

Related