+ Reply to Thread
Results 1 to 4 of 4

Excel 2007 : Conditional Formatting: Multiple Conditions

  1. #1
    Registered User
    Join Date
    08-10-2011
    Location
    California, USA
    MS-Off Ver
    Excel 2007
    Posts
    1

    Conditional Formatting: Multiple Conditions

    Now that I've ripped my hair out, I'm turning to the forum for help. I'm trying to use the Conditional Formatting feature when two conditions must both be satisfied.

    In Column A, I have listed 20 Employees' Names
    In Column B, I have listed the offices where those 20 employees work
    In Column C, I have listed how many performance reviews that the particular employees have had.

    Whenever both "Fresno" is listed in Column B AND the number '2' or higher is listed in Column C, I want to highlight that employee in Column A via conditional formatting (in the alternative, I would be happy if the formatting just applied to the applicable cell in Column B).

    In short, I want to have Excel highlight for me (via conditional formatting) Fresno employees with 2 or more performance reviews.

    Any help you can give me here would be much appreciated.

    Thanks

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Conditional Formatting: Multiple Conditions

    Hello,

    select the whole row (or the cells in the row that you want to be formatted). Create a new formatting rule using a formula, for example if you select row 2, the formula would be

    =and($B2="Fresno",$C2>=2)

    Select a format. Now you can copy the row, select the rows below and use Paste Special > Formats.

    cheers,

  3. #3
    Registered User
    Join Date
    10-30-2013
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Conditional Formatting: Multiple Conditions

    Excellent post. Thank you. This worked perfectly. But I am curious, can you embed "if" statements in the conditional formatting box or do you just proceed the formula with the "and" you describe above. I'm assuming "or" would work as well. What other Boolean logic? And do you just put the Boolean conjunction at the beginning? What if you need a mixture "and" and "or"?

  4. #4
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Excel 2007 : Conditional Formatting: Multiple Conditions

    You don't need an IF statement in a CF formula if you phrase the formula such that the result is either TRUE or FALSE.

    It is, of course, possible to embed IF() statements within a more complex CF formula, if there are multiple or nested conditions that cannot be expressed with a simple statement. In most cases, though, IF() is redundant in a CF formula. Compare

    =If(A1=1,TRUE,FALSE) vs =A1=1

    =If(OR(A1=1,B1=2),TRUE,FALSE) vs =OR(A1=1,B1=2)

    Basically, anything that you put into an IF() statement in a CF can be done without it. You only need an IF() statement if the conditions are more complex.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1