Color an entire row by formula

Reading time: 3 minutes
Last Updated on 10/05/2021 by Frédéric LE GUEN

This article will explain you how to color an entire row with a formula and the conditional formatting

Colored only one cell

We have seen in this article how to change the color of your cell according to a value or a formula.

Here for instance, I have applied a rule when the total of the sales is lower than 10.

=G2<0

Conditional formatting is in one column only

In the rule manager, you see the rule and the data range in which the rule applies (here, cells $G$2:$G$21)

Rule in the rule manager

To open the rules manager, you must activate the Conditional formatting> Manage rules menu .

Menu Manage Rules

Change the range where to apply the rule

For many Excel user, extend the range where to apply the rule is enough.

New range of application for the same rule

But the result is not what we want. Why? 🤔🤨

The rule does not apply to the whole row

How to correct the error?

To understand why changing the data range does not affect the display of the ruler, just write the test directly to cells.

Conditional rule written directly in a cell

Each time the test is true, the colour of the rule is applied.  This is the principle of conditional formatting 😉

When the test is copied over the whole cell range of cells where we want to apply the conditional formatting, we get the following result.

Comparison result of the test and result of the conditional formatting

Analyse the results of the test

If we look at the first row of the test (row 13) we have TRUE, TRUE and FALSE

  • The first TRUE it's because the quantity is 5, so it's less than 10 (test checked)
  • The second TRUE it's because the price is 3,50€. Here again it's less than 10
  • Last test, the total is 17,50€ and it's greater than 10. So the result is FALSE

And so on for all the other rows/columns of our document.

So, the problem here is to return the result of the test based on column G only. Even when the test is copied for the other columns.

How to Color the entire row

The trick is to used the $ to block only the reference of column G. This is called the mixed reference. Now, each row returns TRUE or FALSE only if the value in column G is lower than 10

=$G2<0

Use of a mixed reference to do the test always with the value in column G

In the Rules Manager, we just have changed the rule to add the dollar in order to colored the whole row.

Rule with a dollar to colord the whole row

Leave a Reply

Your email address will not be published. Required fields are marked *

Color an entire row by formula

Reading time: 3 minutes
Last Updated on 10/05/2021 by Frédéric LE GUEN

This article will explain you how to color an entire row with a formula and the conditional formatting

Colored only one cell

We have seen in this article how to change the color of your cell according to a value or a formula.

Here for instance, I have applied a rule when the total of the sales is lower than 10.

=G2<0

Conditional formatting is in one column only

In the rule manager, you see the rule and the data range in which the rule applies (here, cells $G$2:$G$21)

Rule in the rule manager

To open the rules manager, you must activate the Conditional formatting> Manage rules menu .

Menu Manage Rules

Change the range where to apply the rule

For many Excel user, extend the range where to apply the rule is enough.

New range of application for the same rule

But the result is not what we want. Why? 🤔🤨

The rule does not apply to the whole row

How to correct the error?

To understand why changing the data range does not affect the display of the ruler, just write the test directly to cells.

Conditional rule written directly in a cell

Each time the test is true, the colour of the rule is applied.  This is the principle of conditional formatting 😉

When the test is copied over the whole cell range of cells where we want to apply the conditional formatting, we get the following result.

Comparison result of the test and result of the conditional formatting

Analyse the results of the test

If we look at the first row of the test (row 13) we have TRUE, TRUE and FALSE

  • The first TRUE it's because the quantity is 5, so it's less than 10 (test checked)
  • The second TRUE it's because the price is 3,50€. Here again it's less than 10
  • Last test, the total is 17,50€ and it's greater than 10. So the result is FALSE

And so on for all the other rows/columns of our document.

So, the problem here is to return the result of the test based on column G only. Even when the test is copied for the other columns.

How to Color the entire row

The trick is to used the $ to block only the reference of column G. This is called the mixed reference. Now, each row returns TRUE or FALSE only if the value in column G is lower than 10

=$G2<0

Use of a mixed reference to do the test always with the value in column G

In the Rules Manager, we just have changed the rule to add the dollar in order to colored the whole row.

Rule with a dollar to colord the whole row

Leave a Reply

Your email address will not be published. Required fields are marked *