Excel Conditional Formatting

    Share
    avatar
    Ev
    Franchise Player
    Franchise Player

    Number of posts : 20613
    Age : 26
    Location : We The North
    Favorite Team : Ottawa
    Registration date : 2010-02-09

    Excel Conditional Formatting

    Post by Ev on Fri Nov 29, 2013 3:01 pm

    Anyone good with this stuff?

    I'm trying to make the cells in a column turn red, green, or yellow based on other cells.

    For example, I want the cell to turn red if Cell A is -30% or lower, or if Cell B is < 12, or if Cell C is a negative value, etc.
    avatar
    Cap'n Clutch
    Co-Founder
    Co-Founder

    Number of posts : 13539
    Age : 44
    Location : Ottawa
    Favorite Team : Ottawa
    Registration date : 2008-07-31

    Re: Excel Conditional Formatting

    Post by Cap'n Clutch on Fri Nov 29, 2013 3:22 pm

    It should be easily found on the menu bar no?


    _________________
    "A child with Autism is not ignoring you, they are waiting for you to enter their world."

    - Unknown Author
    avatar
    Cap'n Clutch
    Co-Founder
    Co-Founder

    Number of posts : 13539
    Age : 44
    Location : Ottawa
    Favorite Team : Ottawa
    Registration date : 2008-07-31

    Re: Excel Conditional Formatting

    Post by Cap'n Clutch on Fri Nov 29, 2013 3:26 pm



    _________________
    "A child with Autism is not ignoring you, they are waiting for you to enter their world."

    - Unknown Author
    avatar
    wprager
    Administrator
    Administrator

    Number of posts : 47394
    Age : 55
    Location : Kanata
    Favorite Team : Ottawa
    Registration date : 2008-08-05

    Re: Excel Conditional Formatting

    Post by wprager on Fri Nov 29, 2013 3:35 pm

    Which version of Excel? Do you want the cell to change based on its own value or based on another cell? Do you want it based on the same cell for all cells where you want the change or based on same row/different column, or same column/different row?

    Lots of questions to be answered.

    P.S. I *am* good with this stuff.


    _________________
    Hey, I don't have all the answers. In life, to be honest, I've failed as much as I have succeeded. But I love my wife. I love my life. And I wish you my kind of success.
    - Dicky Fox
    avatar
    tim1_2
    Franchise Player
    Franchise Player

    Number of posts : 16085
    Age : 34
    Location : Ottawa
    Favorite Team : Ottawa
    Registration date : 2011-01-07

    Re: Excel Conditional Formatting

    Post by tim1_2 on Fri Nov 29, 2013 3:38 pm

    Excel has a pretty good built-in tool for conditional formatting, but I don't know how to make the value in one cell affect the formatting in a different cell, if that's what you're going for.
    avatar
    shabbs
    Hall of Famer
    Hall of Famer

    Number of posts : 31416
    Location : I'm sorry, I can't hear you over the sound of how awesome I am.
    Favorite Team : Ottawa
    Registration date : 2008-08-12

    Re: Excel Conditional Formatting

    Post by shabbs on Fri Nov 29, 2013 3:46 pm

    Hmmmm... you're looking for conditional formatting using three different values under three different conditions. You should be able to use a formula that evaluates those cells based on your conditions to determine the resulting colour of the cell you are setting this up in. The formulae would be a series of OR evaluations for each of your three tests.
    avatar
    Ev
    Franchise Player
    Franchise Player

    Number of posts : 20613
    Age : 26
    Location : We The North
    Favorite Team : Ottawa
    Registration date : 2010-02-09

    Re: Excel Conditional Formatting

    Post by Ev on Fri Nov 29, 2013 3:54 pm

    Here is what I am working with:



    I want the column Financial Status to be a certain colour based on the other cells.

    H is Balance October 31, K is %change, P is months until 0 funds

    I want it to show up green if all three statements are true: H > 0, K > 0%, P > 24

    Yellow if H > 0, K is less than 0% but greater than -30%, P is between 12 and 24

    Red if H<0, K less than -30%, P less than 12
    avatar
    wprager
    Administrator
    Administrator

    Number of posts : 47394
    Age : 55
    Location : Kanata
    Favorite Team : Ottawa
    Registration date : 2008-08-05

    Re: Excel Conditional Formatting

    Post by wprager on Fri Nov 29, 2013 4:06 pm



    Sorry, I did the above when I thought it was an "OR" of the three conditions. Just change the formula to AND.

    How you get to the rule editing depends a lot on the version of Excel you have but, essentially, configure it to use a formula and use the one I've got (changed to AND):

    Code:

    =AND($A2<0.3,$B2<12,$C2<0)
    The dollar sign in front of the A means to lock-in the column which is checked. That formula was entered (originally) for a cell in row 2, so the $A2 means that if I copy the formatting to a cell in row 4, the formula will change to check $A4. The conditional formatting is applied to the cells in column D.


    _________________
    Hey, I don't have all the answers. In life, to be honest, I've failed as much as I have succeeded. But I love my wife. I love my life. And I wish you my kind of success.
    - Dicky Fox
    avatar
    wprager
    Administrator
    Administrator

    Number of posts : 47394
    Age : 55
    Location : Kanata
    Favorite Team : Ottawa
    Registration date : 2008-08-05

    Re: Excel Conditional Formatting

    Post by wprager on Fri Nov 29, 2013 4:14 pm

    I assume if it doesn't match any of the three rules then it's no cell filling? Then you need three rules.

    rule 1 -- to set green:
    Code:

    =AND($H2>0,$K2>0,$P2>24)
    rule 2 -- to set yellow:
    Code:

    =AND($H2>0,AND($K2<0,$K2>-0.3),AND($P2>=12,$P2<=24))
    rule 3 -- to set red:
    Code:

    =AND($H2<0,$K2<-0.3,$P2<12)
    And I'd be stunned if I didn't make a mistake, so you'll likely have to tweak.


    _________________
    Hey, I don't have all the answers. In life, to be honest, I've failed as much as I have succeeded. But I love my wife. I love my life. And I wish you my kind of success.
    - Dicky Fox
    avatar
    wprager
    Administrator
    Administrator

    Number of posts : 47394
    Age : 55
    Location : Kanata
    Favorite Team : Ottawa
    Registration date : 2008-08-05

    Re: Excel Conditional Formatting

    Post by wprager on Fri Nov 29, 2013 4:15 pm

    So set those rules for a cell in row 2, then do a format copy to all other cells. If the cell you are setting it to is not in row 2 then adjust the formulas accordingly.


    _________________
    Hey, I don't have all the answers. In life, to be honest, I've failed as much as I have succeeded. But I love my wife. I love my life. And I wish you my kind of success.
    - Dicky Fox
    avatar
    Ev
    Franchise Player
    Franchise Player

    Number of posts : 20613
    Age : 26
    Location : We The North
    Favorite Team : Ottawa
    Registration date : 2010-02-09

    Re: Excel Conditional Formatting

    Post by Ev on Fri Nov 29, 2013 4:23 pm

    oh sorry it's 2010...and I might have different conditions, but I'll check on Monday.

    Thanks W.P. Rager and all
    avatar
    wprager
    Administrator
    Administrator

    Number of posts : 47394
    Age : 55
    Location : Kanata
    Favorite Team : Ottawa
    Registration date : 2008-08-05

    Re: Excel Conditional Formatting

    Post by wprager on Fri Nov 29, 2013 4:30 pm

    shabbs wrote:Hmmmm... you're looking for conditional formatting using three different values under three different conditions. You should be able to use a formula that evaluates those cells based on your conditions to determine the resulting colour of the cell you are setting this up in. The formulae would be a series of OR evaluations for each of your three tests.
    Conditional formatting doesn't work like that. You cannot have a single rule with multiple outcomes. You could do it with VBA, of course, but not conditional formatting.


    _________________
    Hey, I don't have all the answers. In life, to be honest, I've failed as much as I have succeeded. But I love my wife. I love my life. And I wish you my kind of success.
    - Dicky Fox
    avatar
    wprager
    Administrator
    Administrator

    Number of posts : 47394
    Age : 55
    Location : Kanata
    Favorite Team : Ottawa
    Registration date : 2008-08-05

    Re: Excel Conditional Formatting

    Post by wprager on Fri Nov 29, 2013 4:35 pm

    tim1_2 wrote:Excel has a pretty good built-in tool for conditional formatting, but I don't know how to make the value in one cell affect the formatting in a different cell, if that's what you're going for.
    Under Conditional Formatting select New Rule and you get this interface:


    Click on the last option -- "Use a formula to determine which cells to format". Any cell references in the formula are relative to the cell where you were when you started (in my examples I assumed a heading row 1, then the first cell to format was in cell 2). When you copy the formatting to the cell below, that 2 will automatically change to a 3. The $ in front of the column marker is to make sure that the formula always checks the cell in that column. This way you can copy this formatting to a range of cells in the same row.


    _________________
    Hey, I don't have all the answers. In life, to be honest, I've failed as much as I have succeeded. But I love my wife. I love my life. And I wish you my kind of success.
    - Dicky Fox
    avatar
    shabbs
    Hall of Famer
    Hall of Famer

    Number of posts : 31416
    Location : I'm sorry, I can't hear you over the sound of how awesome I am.
    Favorite Team : Ottawa
    Registration date : 2008-08-12

    Re: Excel Conditional Formatting

    Post by shabbs on Fri Nov 29, 2013 5:40 pm

    wprager wrote:You cannot have a single rule with multiple outcomes.
    Sure you can. I do it all the time with statuses to show progress for tasks (Not Started, In Progress, Completed, Error). I have a single conditional statement that sets the colour depending the status. That is the whole point of conditional.
    avatar
    TheAvatar
    Veteran
    Veteran

    Number of posts : 2728
    Age : 50
    Location : Colorado springs, colorado
    Favorite Team : Ottawa
    Registration date : 2008-08-05

    Re: Excel Conditional Formatting

    Post by TheAvatar on Sat Nov 30, 2013 9:30 am

    I'm the king of excel Smile

    Sponsored content

    Re: Excel Conditional Formatting

    Post by Sponsored content


      Current date/time is Wed May 24, 2017 7:23 pm