Color Row Conditional Formatting makes your Excel data clearer. This guide explains each step to avoid mistakes.

Common Mistakes in Conditional Formatting

Let's take this example. You want to color a row when the price in column E is lower than 20.

• The conditional formatting rule is correct: =E2<20.
• When you apply the conditional formatting rule to other cells, the rule affects unexpected cells, as shown in the image.

How to Apply Color Row Conditional Formatting

The secret lies in the custom rule of conditional formatting.

• A custom conditional formatting rule is a logical test.
• When the test returns TRUE, the color is applied.

So, when the color rule is not what you expect, it is best practice to write your rule in a cell and analyze the result for the different columns.

Step 1: Write Your Test in a Cell

1. Go to a cell in the same column as your test (easier to understand)
2. Write your logical test. For example: =E2<20.

Step 2: Copy the Formula for Other Cells

Copy your formula to all the other columns where the test applies.

And just like that, you can see where the problem comes from.

• The results of the logical tests are correct in this scenario. Cell C2 is lower than 10, and the same is true for D2.
• But it's not what we want. We want to stay focused on the column E

Step 3: Add a Dollar to the Formula

To stay on column E, add a dollar before the column reference: $E2<20. This creates a mixed reference.

The dollar symbol locks column E but not the row. The test stays on column E for each row 😃👍

Step 4: Update Your Rule in the Name Manager

Now, update your rule to color the entire row correctly.

1. Go to Home > Conditional Formatting > Manage Rules.
2. Change the formula of your rule.
3. Check the range of cells.

Related Articles

• Master Conditional Formatting in Excel
• Copy a Conditional Formatting Rule
• How to Use Mixed References in Excel Formulas

Tutorial Video

Find this trick and more in the following video