Conditional formatting based upon absolute values

**grvs** · 10-10-2011, 11:42 AM

I am trying to format the error percentages. So if the value is closer to zero, cell background should be greener. If a value is far from 0, cell background should be more towards red side.

I have about 60 cells which I want to format and my errors percentage genally fall between -50% to +50%. So I dont want to create fixed rules like

Please Login or Register  to view this content.

How do i achieve this in excel 2007.

**MarvinP** · 10-10-2011, 12:03 PM

Hi grvs,

Find the attached where I used gradient CF with 2010 Excel. I wonder if it will look correct in your 2007 version? If not you may need to create a helper column with absolute values.

See http://chandoo.org/wp/2010/05/19/new...al-formatting/ for improvements to CF in 2010. Upgrade?

**grvs** · 10-11-2011, 06:49 AM

Thanks Marvin, In excel 2007, it fills background of my excel cells according to values. which does help in my case.

what I need is can be obtained in few steps by following.

take absolute values of the numbers in some other cells.
conditional format these cells by 2 color scale
and then paste these formatted cells to MS word. (it keeps the background but removes the conditional formatting).
Copy these cells from word again and paste to excel.
Put original error % in these new cells which already have desired background

I was looking for a single step which could achieve this.
Edit: may be not a single step, but if i can achieve this only by using excel, that would help also.