MrExcel Publishing
Your One Stop for Excel Tips & Solutions

formatting a cell

Posted by andrew miller on January 08, 2002 1:58 PM

I want my data to zero in only two cells on a sheet. Is there any way to format a cell so it shows a zero if it is blank without doing it to the whole page?

Posted by Tom Dickinson on January 08, 2002 4:27 PM

One method would be an If/Then in those 2 cells, putting in a text 0.

=If(value = 0, "0", value)

Posted by Florizel on January 08, 2002 4:59 PM

How would you do that?

Posted by Tom Dickinson on January 08, 2002 5:08 PM

Do what?

If by your question you mean how to keep the zero in 2 cells, while having it disappear in other cells where the value is zero, go to your options and un-check the "show zeros". If your question is on the formula itself, then just foolow the formula, substituting the word "value" with the operation desired. Just one note; a text zero will align left, so you will need to do an align right to make it look like a regular number.

Posted by Florizel on January 08, 2002 5:28 PM

Re: Do what?

But this assumes that the two cells in question contain a formula.
What about keyboard inputs of 0, or spacebar, or delete ?

Posted by Chris D on January 09, 2002 12:03 PM

Re: Do what?

Could conditional formatting do the job ?

Conditional format your whole sheet so that any cell value of "0" is formatted to white text - this will blank out ALL cases of "0"

then goto the cells you want to SHOW and delete the conditional formatting from them

would that work ?


Posted by Florizel on January 09, 2002 4:31 PM

Re: Do what?

Yes. Thanks very much. That took care of the zeroes.

In addition, to take care of blank cells and cells containing space(s), I've added the following macro :-

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("C2:C3")) Is Nothing Then
If Target.Value = "" Or _
Len(Application.WorksheetFunction _
.Substitute(Target.Value, " ", "")) = 0 _
Then Target.Value = 0
End If
Application.EnableEvents = True
End Sub