Excel conditional format to zero

  stubacka2 12:22 24 Oct 2007

Is there a way to conditionally format cells so that if the data entered is greater than zero, the cell displays zero.

I know this sounds strange, but I have been asked if this is possible by a colleague at work - I don't know his reason for wanting this.

  bstb3 12:42 24 Oct 2007

Hi Stubacka2

The easiest way I can think of is to change the number format of the cells in question, rather than using the conditional formatting tool.

Using the custom format in the format cells dialog box set the format of the cells to be:


then any positive values will always be displayed as 0, however the cell retains the actual value that has been input. Negative values will be displayed as their normal value. You can add additional formatting to the negative side of the format as per normal.

Hope this helps (and I must admit to being curious as to the reason why)


  silverous 13:15 24 Oct 2007

Perhaps I shouldn't speculate but I can imagine uses for this.....

Perhaps positive figures indicate a target was beaten and are not of interest whereas negative figures are.

Maybe a refund is given if targets aren't met but if they are exceeded no effect should happen.

Having said that I believe any operations (e.g. a sum) on these cells will still take place as if they had the full value. If that is not desired then a separate column with a formula that does this may help.

  stubacka2 16:23 24 Oct 2007

Thanks to both replies.

The custom format from bstb3 worked a treat but I still don't know why it is needed - sorry.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Honor 9 Lite review

How Social Media has Propelled Political Graphic Design and Art in the Last Decade

The best kids apps for iPhone & iPad 2018

HomePod d’Apple : date de sortie, prix et fiche technique