A very interesting way to draw attention to rows that meet a defined value is to highlight the entire row.
This not only makes row discovery easier but is quite the show-stopper for those not well-versed in the ways of Excel.
The process is almost the same as what was described above. The only difference is that more cells are selected before Conditional Formatting is applied (that and a small modification to the formula.)
Testing the Logic on the Grid
It’s always easier to demonstrate, test, and refine the logic by writing the formulas on the grid beside the data.
Using cell D6 as our starting cell, we write the following test (remember that we had one cell reference set as relative and the other cell reference set as absolute):
=B6 > $B$3
When filled down and to the right, the results are less than stellar.
It’s as though half of it worked but the other half failed. Let’s examine the underlying formulas.
All the references to Column B have shifted to Column C when the formulas were filled from left to right. That is the nature of relative references.
“But Great Teacher”, I hear you say, “how do we allow the row numbers to change while locking the column letters?”
That’s a great question.
The answer lies in using what are known as “mixed references”. These are cell references where half of the reference is locked while the other half remains free to change.
Let’s look at the four possible reference modes:
- A1 – Relative Reference (column and row references can change)
- $A$1 – Absolute Reference (column and row references are fixed)
- A$1 – Mixed Reference (column reference can change but row reference is fixed)
- $A1 – Mixed Reference (column reference is fixed but row reference can change)
We need the version that allows the row reference to change while locking the column reference.
The formula is updated as follows:
=$B6 > $B$3
The result is more to our liking.