Conditional Formatting- Linking to different cells

You do not have to limit yourself to simple references in you conditional formatting. Say you want to check whether a number is present in another list of numbers.

To follow along go to 16.1

Highlight the cells you are applying the conditional format to,

Conditional Formatting Formula

and set up a New Rule via the conditional format as shown below

Conditional Formatting Formula

When the New Formatting Rule box appears, choose ‘Use a formula to determine which cells to format’ as shown below.

In the formula box you can now enter a formula to determine which cells need the formatting. In this case note that we have built a COUNTIF function. Look at the highlighted cells above and note that the area C12 to E17 is highlighted with cell C12 the current active cell.

The formula below is built as if we are only referring to cell C12, with the absolute and relative referencing set up correctly. So using the formula below, in D12, Excel knows that it must still look at the same range ($C$8:$H$8), but when it is in D12, it must use D12. If we had set the formula up so that the reference to C12 was $C$12 then even cell D12 would use the number in C12 instead of D12 to determine the formatting.

Conditional Formatting Formula

Back to: Advanced Excel Course > Conditional Formatting