Ask Your Question

How to return empty cell from function in calc

asked 2014-05-14 11:07:11 +0200

denis.polkovnikov gravatar image

updated 2014-05-14 11:14:32 +0200

I want get a completely empty cell at return without using quotes, i.e. not like at previous question

edit retag flag offensive close merge delete


Ctrl + arrow is fetches an empty string as two double quotes. What I do not want as return of function. Is there a way to do so?

denis.polkovnikov gravatar imagedenis.polkovnikov ( 2014-05-14 11:20:19 +0200 )edit

Yet another case is pivot table. It adds (empty) result.

denis.polkovnikov gravatar imagedenis.polkovnikov ( 2014-05-28 06:47:41 +0200 )edit

5 Answers

Sort by » oldest newest most voted

answered 2014-05-14 13:10:27 +0200

Regina gravatar image

It is in principle impossible. The return value of a formula is shown in the very same cell, which contains the formula. And because this cell contains the formula it is never "empty". Otherwise a formula would erase itself.

If the empty string is a valid result and you need to indicate, that the formula result is not valid, then you can use the function NA(). It generates the special error value #N/A, which can be tested by the function ISNA().

If you need to erase the content of a cell dependent on the values of other cells, you need a macro to do that.

edit flag offensive delete link more


Thank you!

denis.polkovnikov gravatar imagedenis.polkovnikov ( 2014-05-19 12:09:55 +0200 )edit

There are comments and answers also discussing workarounds. (See my own answer of today e.g.)

Lupp gravatar imageLupp ( 2019-01-01 12:49:14 +0200 )edit

answered 2015-04-29 22:38:49 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

In order to provide an answer, here's my two cents that actually do exactly what is asked.

If you're doing a conditional on, let's say, cell B4:

then you can do this: =IF(B4=0;{};B4)

This results in a blank cell if the value of B4 is zero and returns B4 when it is not.

Best, L

edit flag offensive delete link more


The cell containing the formula will answer FALSE if asked =ISBLANK(TheCell). It will also return FALSE to =ISTEXT(TheCell) and to =ISNUMBER(). We may consider this "virtually blank". The fact, however, that the cell is containing a formula is unchangeable - and to =ISFORMULA(TheCell) we get a TRUE.

In my test with V4.2.2 the "pseudoblank" result of the suggested formula persisted when the value of B4 was changed to something <>0. I regard this as a bug.

Lupp gravatar imageLupp ( 2015-04-29 23:39:35 +0200 )edit

Coming next to reproducing B4 even if BLANK may be the array formula {=OFFSET(B4,0,0,1,1)} in TheCell. It will, however, also answer TRUE to =ISFORMULA(TheCell) and FALSE to =ISBLANK(TheCell).

Lupp gravatar imageLupp ( 2015-04-29 23:45:22 +0200 )edit

Still Ctrl + arrow is fetches cell with ={} although it looks empty. Interesting enough pivot table recognizes ={} as zero and ="" as second (empty) whereas totally empty as the first (empty).

denis.polkovnikov gravatar imagedenis.polkovnikov ( 2015-12-23 15:26:41 +0200 )edit

I wanted a way to make a cell, that contains a function to do calculations based on the content of another cell, look "empty" when that other cell didn't contain any value. This is the perfect answer for me. I wasn't aware of the {} construction to make a cell look "empty".
Great tip!

GeertVc gravatar imageGeertVc ( 2019-01-01 09:01:55 +0200 )edit

We can use {} in a function to get an empty cell as result. Brilliant! This is exactly was i was searching for. Thank You. If i could i would up-vote Your answer, especially as the other above is basically wrong.

josef64 gravatar imagejosef64 ( 2019-03-18 14:44:30 +0200 )edit

Excellent, {} works!

Toby gravatar imageToby ( 2019-09-28 12:09:22 +0200 )edit

answered 2019-01-01 12:46:02 +0200

Lupp gravatar image

This thread is very old, but having a subject that was clearly (though not fully consistent) worded, it is visited again and again. I originally contributed a soultion only by commenting on the answer posted as "wiki" on 2015-04-29.
Meanwhile there was opportunity to learn that this anwer was unnecessarily complicated. The usage of OFFSET() or similar constructs can be omitted if not the calculated reference is needed. Thus I give a new answer with one old and unchangeable part "-1-" and the simple way to get something like a soultion as far as possible in part "-2-".

To get an actually blank cell by a resident formula is strictly impossible because the formula itself is content of the cell.

The simple way to get what's next to blank if the referenced cell (A2 e.g.) is blank is the formula {=A2:A2}.
(This is =A2:A2 entered for array-evaluation either by Ctrl+Shift+Enter or from the formula wizard.)

The answer is exemplified in this attachment.

edit flag offensive delete link more

answered 2014-05-14 13:25:26 +0200

Pedro gravatar image

The short answer seems to be no.

Following on Regina's logic answer (a cell containing a formula is not empty because it contains the formula): because a cell returning "" is interpreted as containing Text you can use an IF(ISTEXT(A1);0;A1) condition to solve (some) problems.

edit flag offensive delete link more


I asked about returning empty cell because I want to use Ctrl + arrow. Not for using formula. Formula do well with empty cells.

denis.polkovnikov gravatar imagedenis.polkovnikov ( 2014-05-19 12:15:09 +0200 )edit

answered 2016-05-10 12:01:42 +0200

pierre-yves samyn gravatar image


My answer may be too late for this, sorry, but if the goal is:

Ctrl+Arrow should not find a cell displaying "" (result of a formula)

a solution could be to protect these cells (eg . apply a cell style) then ToolsProtectSheet▸uncheck Allow all users to Select protected cells.


edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2014-05-14 11:07:11 +0200

Seen: 16,960 times

Last updated: Jan 01 '19