conditional formating in Excel

Discussion in 'General Chat' started by sheriffwoody, Dec 7, 2011.

  1. sheriffwoody

    sheriffwoody
    Well-known Member

    Joined:
    Oct 7, 2010
    Messages:
    7,456
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    136
    Ratings:
    +1,533
    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?
     
  2. imightbewrong

    imightbewrong
    Distinguished Member

    Joined:
    Dec 6, 2005
    Messages:
    54,338
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    167
    Location:
    Romford-ish
    Ratings:
    +35,513
    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.
     
  3. imightbewrong

    imightbewrong
    Distinguished Member

    Joined:
    Dec 6, 2005
    Messages:
    54,338
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    167
    Location:
    Romford-ish
    Ratings:
    +35,513
    Actually it's easy here in 2010 from the Conditional Format button - not sure how powerful 2003 is in this regard.
     
  4. sheriffwoody

    sheriffwoody
    Well-known Member

    Joined:
    Oct 7, 2010
    Messages:
    7,456
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    136
    Ratings:
    +1,533
    it's crap :(
     
  5. GloopyJon

    GloopyJon
    Distinguished Member

    Joined:
    Mar 14, 2009
    Messages:
    8,879
    Products Owned:
    1
    Products Wanted:
    0
    Trophy Points:
    166
    Location:
    Brussels
    Ratings:
    +5,815
    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.
     

    Attached Files:

  6. Stuey1

    Stuey1
    Well-known Member

    Joined:
    Aug 14, 2009
    Messages:
    4,081
    Products Owned:
    1
    Products Wanted:
    0
    Trophy Points:
    136
    Location:
    Texas
    Ratings:
    +1,138
    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: Dec 7, 2011
  7. imightbewrong

    imightbewrong
    Distinguished Member

    Joined:
    Dec 6, 2005
    Messages:
    54,338
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    167
    Location:
    Romford-ish
    Ratings:
    +35,513
    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))
     
  8. Stuey1

    Stuey1
    Well-known Member

    Joined:
    Aug 14, 2009
    Messages:
    4,081
    Products Owned:
    1
    Products Wanted:
    0
    Trophy Points:
    136
    Location:
    Texas
    Ratings:
    +1,138
    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.
     
  9. imightbewrong

    imightbewrong
    Distinguished Member

    Joined:
    Dec 6, 2005
    Messages:
    54,338
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    167
    Location:
    Romford-ish
    Ratings:
    +35,513
    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.
     
  10. sheriffwoody

    sheriffwoody
    Well-known Member

    Joined:
    Oct 7, 2010
    Messages:
    7,456
    Products Owned:
    0
    Products Wanted:
    0
    Trophy Points:
    136
    Ratings:
    +1,533
    thanks for the help guys :)
     

Share This Page

Loading...