conditional formatting comparing 2 columns

[purplelife.co.uk]

Hello,

I'm trying to do some conditional formatting on a pivot table.
Category 2006 2007
aerials 100 200
batteries 100 200
bluetooth 100 200
data suites 200 150


I'd just like to compare at a basic level 2006 to 2007. If It's higher
than 2006, green, if not then red.
Ideally would like 3 states - if 30% or more over 2006 then green,
0-30% normal / yellow, if less than 2006 then red.

Although I seem to be able to do conditional formatting for 2007 v.
2006 one line at a time (just aerials for example), I have absolutely
no idea how to do the entire column.

It would be fantastic if it just compared the profit value, so that
anything could be put down the left column (just top 10 categories,
branches / areas etc).

In summary, how can i do conditional formatting on the whole column,
comparing 2006 profit to 2007?

Thanks

jamie

 

[Roger Govier]

Hi

Just mark the range of cells within the PT that represent 2007 values.
Format>Conditional Formatting>Formula is and set 3 Conditions
=C4>B4*1.3 Format Green
=AND(C4>B4,C4<=B4*1.3) Format Yellow
=C4<B4 Format Red

 

[Erny]

Hi Jamie,

Let's say your categories are in column A, 2006 values are in column B and
2007 values in column C.
If you select columns A, B and C with your mouse and then use Conditional
formatting, you could use the following conditional formulas:

for condition 1 (specify green background):
=$C1>=(1,3*$B1)

for condition 2 (specify red background)
=$C1<$B1

for condition 3 (specify yellow background)
=($C1<>"")*($B1<>"")=1

The last one ensures that you don't colour rows where there are missing
values in columns B or C. You could adapt this depending on what you wish to
not colour.
The formatting should automatically be valid for the whole columns. Note
that of course the title row will now be coloured in yellow for the three
cells A1, B1 and C1.
You could remove this by selecting these 3 cells and remove the conditional
formatting on those.

Kind regards,
Erny

 

[purplelife.co.uk]

Wonderful stuff!
Turns out that using relative values (A1, B5 etc) rather than absolute
($C2, $F5) solved the problem, as the conditional format then applies
to each row item in turn, thanks! :-D