How to Apply Row Conditional Formatting in Excel

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