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

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

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

( 2018-01-25 20:25:51 +0200 )edit

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

( 2018-01-25 20:28:54 +0200 )edit
1

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!

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

( 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

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

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

( 2018-01-26 22:23:23 +0200 )edit

Sort by » oldest newest most voted

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.

more

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

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

There is not. Empty is empty.

more

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

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

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

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

=IF(A1>0;A1;0)

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

more