# Color an entire row with conditional formatting in Excel

Last Updated on 04/06/2024

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

1. Build your test for only one cell

Before trying to color the entire row, test your conditional formatting rule

2. 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.

1. Start to write exactly the same test =E2<10 in E11
2. Copy the test for the other cells in the same column
1. 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.

# Color an entire row with conditional formatting in Excel

Last Updated on 04/06/2024

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

1. Build your test for only one cell

Before trying to color the entire row, test your conditional formatting rule

2. 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.

1. Start to write exactly the same test =E2<10 in E11
2. Copy the test for the other cells in the same column
1. 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.