Typical Use of Conditional Formatting
For most users, Conditional Formatting is used to draw attention to cells where the cell’s value meets a defined criterion, such as “greater than 80,000”.
The issue with this technique is that the Conditional Formatting rule must be manually updated if the user wishes to change the value threshold.
Dynamic Conditional Formatting
Suppose we were to allow the user to enter a value in a cell above the data, then have the Conditional Formatting rule compare the user’s value against the data’s values to set the applicable colors.
We can accomplish this by utilizing a formula in the Conditional Formula rule.
Having the user enter a value of 80,000 in cell B3, we perform the following steps:
- Highlight cells that hold the “Yearly Sales” (cells B6 through B20).
- Select Home (tab) -> Styles (group) -> Conditional Formatting -> New Rule.
- In the New Formatting Rule dialog box, select the Rule Type “Use a formula to determine which cells to format” and enter the following formula:
=$B$6 > $B$3- Click the Format button to open the Format Cells dialog box to set font color, fill color, and/or border styles as desired.
Click OK to see that the results are not exactly what we expected.
Why didn’t it work? Let’s dig into the logic and find out.
Cascading References (with a bit of Imagination)
When writing formulas for use in Conditional Formatting, it’s important to understand that the formula’s cell references are written from the 1st (upper-left) cell’s perspective.
You must imagine the formula being “filled down” (or right) to the adjacent cells.
It would be as if you were writing the formula in a cell and filling it to adjacent cells.
Let’s write a formula to test the data’s values against the user’s value. If we wrote the following test formula in an adjacent cell, it would look like the following:
Because 60,000 is NOT greater than 80,000 the test results in a “False” response.
When the formula is filled down to the adjacent rows, all responses are “False”.
Why? Because all the cell references were “fixed” or “locked” to the two cells. This means that every test was comparing the same first value (cell B6) against the same test value (cell B3).
The Solution
The solution to this problem is to have the cell reference being tested (cell B6) set as a relative reference while leaving the user’s cell value (cell B3) reference set as an absolute reference (i.e. “fixed” or “locked”).
=B6 > $B$3
If this updated formula were used, the results would be more to our liking.
The Moral of the Story
The key takeaway here is that whatever formula is needed for the first cell in the dataset, that is the formula needed by Conditional Formatting to perform the same test.
If we were to update our existing rule with the below formula, the results will work flawlessly.
=B6 > $B$3
Testing the Dynamic Nature of Formulas
If the user were to enter a new value in cell B3, the Conditional Formatting rule updates automatically to reflect the new logic.
Highlighting Entire Rows
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.
Mixed References
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.
Applying the Formula to Conditional Formatting
Now that we have the logic nailed down, it’s time to set it up as a Conditional Formatting rule.
REMEMBER: We always use the formula created in the UPPER-LEFT corner of the test area.
Perform the following steps:
- Highlight cells that hold the data (cells A6 through B20).
- Select Home (tab) -> Styles (group) -> Conditional Formatting -> New Rule.
- In the New Formatting Rule dialog box, select the Rule Type “Use a formula to determine which cells to format” and enter the following formula:
=$B6 > $B$3- Click the Format button to open the Format Cells dialog box to set font color, fill color, and/or border styles as desired.
Click OK to see amazing, show-stopper results.
Testing the Conditional Formatting Formula
If the user changes the value in cell B3, the rows that meet the new value are updated accordingly.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
