How to color an entire row with conditional formatting in Excel? This step-by-step guide will help you to understand the technique.
Steps to Color an entire row with conditional formatting in Excel
- Build your test for only one cell
Before trying to color the entire row, test your conditional formatting rule
- Change the position of the dollar in your reference
Then, lock the column reference to color the entire row
Colored only one cell with conditional formatting.
We have seen in this article how to change the color of your cell according to a value or a formula. In this example, all the cells with a value less than 10 are blue. The formula used is this one.
=G2<0
To see the range of cells where is applied the conditional formatting rule, you must open the menu Conditional formatting > Manage rules
Common mistake done
Now, for many Excel users, to highlight the entire row, they extend the range of cells. BUT IT'S a mistake. Take a look at this situation. The rule applied to the range of cells A2:E8 . The result in the worksheet is the following.
What happens behind the scenes and how to correct the mistake?
The best way to understand the mistake is to write the logical test directly into cells.
- Start to write exactly the same test =E2<10 in E11
- Copy the test for the other cells in the same column
- Each time the test is true, the color is applied.
- And also we copy the same test to the left
EACH cell with a value lower than 10 is TRUE. But it's not what we want. We want TRUE when the value of column E only is lower than 10.
How to Color the entire row?
The trick is to use the $ to block only the reference of column E. This is called the mixed reference. For that, when you copy the test to the left, each cell in the same row stays focused on the column E.
=$E2<0
In the Rules Manager, we just changed the rule to add the dollar in order to color the whole row.
