We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Display 0 in calc cell if cell is empty [closed]

asked 2018-01-25 19:15:00 +0200

bern gravatar image

In Calc, does it have a way to display a zero in a cell if the cell is empty?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-27 20:02:49.960391


So you want the entire spreadsheet to be filled with zeroes initially instead of empty? Try to think of an example of how this would work, or when it might be useful.

Jim K gravatar imageJim K ( 2018-01-25 20:25:51 +0200 )edit

No no, just a few ones! Not a whole sheet...

bern gravatar imagebern ( 2018-01-25 20:28:54 +0200 )edit

The cell may have any content/non-error result and you can use/misuse the 'Numbers' format codes to display whatever you want. It may be any kind of lie, including "I'm blank".

You cannot display any kind of string as long as the cell is empty.
But if there anyway is need to distinguish the cells showing 0 from the blank cells displaying nothing: Why not enter the 0 there? Simple and even truthful!

Lupp gravatar imageLupp ( 2018-01-25 23:41:02 +0200 )edit

@Lupp: I think this is the best answer. I'll upvote if you move it into an actual answer.

Jim K gravatar imageJim K ( 2018-01-26 15:18:08 +0200 )edit

Cell with 0s will show users of the spreadsheets where they have to enter data and if the cell is at 0 it will indicate something is very likely missing

bern gravatar imagebern ( 2018-01-26 19:44:10 +0200 )edit

To inform usrs of where input is expected I would by far prefer a conditional format overlaying a cell style with an alerting background color.

Lupp gravatar imageLupp ( 2018-01-26 21:00:31 +0200 )edit

In addition to the last wise Lupp's comment, it's possible allow the access to only those cells with 'cell protection' disable when 'sheet protection' is enable.

m.a.riosv gravatar imagem.a.riosv ( 2018-01-26 22:23:23 +0200 )edit

4 Answers

Sort by » oldest newest most voted

answered 2018-01-26 21:12:50 +0200

Lupp gravatar image

updated 2018-01-26 21:19:24 +0200

Regarding the comment by @bern (Original Questioner) on the Original Question:

To show users where input is expected I would by far prefer a conditional format overlaying a cell style with an alerting background color. The Formula to use in the 'Formula is' mode of CF might be ISBLANK(B5) then, if B5 is the leading cell of the range to define the CF for.
See this demo.

In addition: If only cells expecting input are set to 'not protected', and the sheet itself is protected, 'Tab' will jump to the next input cell.

edit flag offensive delete link more


Thanks Lupp, I'll go your way as I don't have much choices anyway.

bern gravatar imagebern ( 2018-01-29 15:57:56 +0200 )edit

answered 2018-01-25 20:50:59 +0200

erAck gravatar image

There is not. Empty is empty.

edit flag offensive delete link more

answered 2018-01-25 22:05:23 +0200

librebel gravatar image

Hello @bern,

You could use a macro for this. For example to set the value of cell A1 to zero if cell A1 is empty, you could call Cell_Blank_To_Zero( "A1" ):

Function Cell_Blank_To_Zero( strCellAddress$ )
    Dim oSheet As Object : oSheet = ThisComponent.CurrentController.ActiveSheet
    Dim oCell As Object  : oCell  = oSheet.getCellRangeByName( strCellAddress )
    If oCell.String = "" Then oCell.Value = 0
End Function

HTH, lib

edit flag offensive delete link more


It is close. It enters a 0 if the cell is empty when I setup the function in a spreadsheet and the cell is empty. If I enter something in the cell then delete it the cell stays empty, 0 is not coming back.

Thanks for your suggestion

bern gravatar imagebern ( 2018-01-26 17:24:46 +0200 )edit

answered 2018-01-26 09:17:33 +0200

gtomorrow gravatar image

Cells A1:A30 have numbers in some of its cells. Cell B1 has the following formula:


Fill down from B1 to B30. Obviously change the cell references to your needs. Hope this helps.

edit flag offensive delete link more


OUCH! Sorry!

gtomorrow gravatar imagegtomorrow ( 2018-01-28 09:21:05 +0200 )edit

Question Tools

1 follower


Asked: 2018-01-25 19:15:00 +0200

Seen: 4,000 times

Last updated: Jan 26 '18