Home Forums Tech Office and Applications How to Apply Row Conditional Formatting in Excel

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #2325
    papa
    Participant

    Ok. Ladies and gentlemen. In Excel you have a “driver” column that has a limited number of values: marks, ranks, status and like. You need to color entire row based on the value in that column.

    Conditional formatting is the answer.

    This example:

    – assumes your data starts from second row;

    – uses column C as a “driver” column;

    – add 2 conditions Less than 4 and Greater then 7.

    -01- Highlight the entire range of cells that need coloring (it means all rows and all columns that will be included in coloring).

    -02- Pick Conditional Formatting on Home tab, Styles section and pick New Rule.

    -03- Pick the last option – Use formula to determine witch cells to format.

    In the box “Format values where this formula is true” type this:

    =IF($C2 < 4, 1,0)=1

    Note: This formula is satisfied and evaluates to true, when value in your “driver” column “C” is less than 4. Missing dollar sign in fromt of 2 (in $c2) means that you want your condition to change for every row.

    -04- Set fill format to pink

    -05- Repeat the same steps for values greater than 7. Use formula:

    =IF($C2 > 7, 1,0)=1

    And set fill color to light green.

    You are done! Just look at your magnificent work.

    Note: if you need to set a rule for values between 4 and 7, use this more elaborate rule:

    =IF($C2 > 3,IF($BC<7,1,0),0)=1

Viewing 1 post (of 1 total)
  • You must be logged in to reply to this topic.