conditional formating in Excel

sheriffwoody

Distinguished Member
Joined
Oct 7, 2010
Messages
8,091
Reaction score
1,747
Points
1,634
Age
39
having read a few of the replys to help another member with an excel problem i thought i would share my issue.

i need to put some conditional formatting into a spreadsheet, but haven't really used it before.

column a is budget column
column b is actual spend/income column
column c is a variance column (between B and A)

what i need are the cells in column c to turn red if the variance is either £500,000 OR 5% adverse to plan.

this is in old excel by the way (2003 version)

any ideas?
 
There is a 'conditional formatting' option in the format menu isn't there? Lets you add rules. That might not give you what you need if you need to drive the format in one cell off the value in another - think that needs VBA.
 
Actually it's easy here in 2010 from the Conditional Format button - not sure how powerful 2003 is in this regard.
 
You need to set up the rules shown in the attached screenprint. Make sure that you delete the $ signs that Excel adds in if you select the cells, because otherwise when you copy the formatting to the other rows (C3, D3 etc), it will still refer back to the first row.

Of course, you can set up the formatting as you like, and use the same colour or whatever. I picked two different colours so that I could check that both conditions were working.
 

Attachments

  • Conditional formatting.JPG
    Conditional formatting.JPG
    31.7 KB · Views: 36
having read a few of the replys to help another member with an excel problem i thought i would share my issue.

i need to put some conditional formatting into a spreadsheet, but haven't really used it before.

column a is budget column
column b is actual spend/income column
column c is a variance column (between B and A)

what i need are the cells in column c to turn red if the variance is either £500,000 OR 5% adverse to plan.

this is in old excel by the way (2003 version)

any ideas?

Conditional formatting in 2003 allows up to 3 different formats, any more than that and you would need VBA.

to do what you have asked would just be using a formula:

=IF(OR(C2>500000,C2>(A2/20)),1,0)

If you enter that formula into the formula bit on conditional formatting it should work altough my logic may be a bit out as i'm kind of guessing what the 5% is of...

I can't test it as i have 2010, but should be ok i think

EDIT: This will only turn the cells red when they are over budget (not if they are 500k or 5% under budget, not sure if it would need to do both.
 
Last edited:
=IF(OR(C2>500000,C2>(A2/20)),1,0)

This approach works - you can test it in 2010 :)

However just FYI the 'if' is not necessary - you can just say:

=OR(C2>500000,C2>(A2/20))
 
This approach works - you can test it in 2010 :)

However just FYI the 'if' is not necessary - you can just say:

=OR(C2>500000,C2>(A2/20))

Yep, just tested it in 2010...

didn't know that about the IF, however i don't use conditional formatting much, usually end up having to use VBA.
 
yep - your 'if' is basically saying:

if (this is true) -> true
if (this is false) -> false

so you can just replace that with the thing you are testing.
 

The latest video from AVForums

TV Buying Guide - Which TV Is Best For You?
Subscribe to our YouTube channel
Back
Top Bottom