## 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 1^{st} (*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*) reference set as an absolute reference (

**B3***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 5x 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.