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
In the rule manager, you see the rule and the data range in which the rule applies (here, cells $G$2:$G$21)
To open the rules manager, you must activate the Conditional formatting> Manage rules menu .
Change the range where to apply the rule
For many Excel user, extend the range where to apply the rule is enough.
But the result is not what we want. Why? 🤔🤨
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.
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.
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
In the Rules Manager, we just have changed the rule to add the dollar in order to colored the whole row.
