Excel conditional format to zero

  stubacka2 12:22 24 Oct 2007
Locked

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:

"0";-#

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)

bstb3

  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

OnePlus 5 review

50 best online Adobe XD tutorials

iPad Pro 10.5in (2017) review

Comment connecter un MacBook à une TV ?