Excel conditional format to zero

  stubacka2 12:22 24 Oct 07

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 07

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 07

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 07

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.

The Legend of Zelda Breath of the Wild review: Five hours with Zelda on the Nintendo Switch

1995-2015: How technology has changed the world in 20 years

How the painting-like animated sequences in A Monster Calls were created by Glassworks Barcelona

The 22 best Safari extensions | Best Safari plugins: Improve Apple's Safari web browser with these…