Conditional Formatting based on another cell

Learn how to use conditional formatting based on another cell and create a rule that depends on another cell value in Excel.

Conditional formatting enables us to determine highlights based on the value of a referred cell. We can modify the values arbitrarily in the referred cell. If the value changes, then the highlights of the given range (icons, shapes, custom formatting, etc.) will also dynamically change.

In the example, you want to highlight all the dates in column N when they are more than 15 days newer than the date I entered in cell M2. Select the Updated column (range N2:N24) in the sample file.

Steps to use conditional formatting based on another cell

1. Click Conditional Formatting on the Home tab of the ribbon
2. Click New Rule. A popup window will appear.
3. Choose ‘Use a formula to determine which cells to format’.
4. Enter the formula =$M2>$N$1+15
5. Click the Format button and select your formatting style.
6. Click OK.

Explanation: In this expression, Excel evaluates values in column ‘M.’ M2 references the first cell in the selected range. You can apply this conditional formatting rule for this range. Use the ‘$’ symbol for M2. Press F4 twice ($N$1) to apply absolute reference because N1 is an absolute value. The conditional formatting rule always uses this cell. What does the second part of this formula ‘>$N$1+15’ mean? Every cell more than 15 days after the date in cell N1 is appropriate to our criteria!

Result: Excel will highlight cells where the date difference is more than 15 days.

How to highlight cells based on another cell?

To highlight cells based on a value in another cell, use this conditional formatting formula to the range C4:G11 is: =C4>=$K$4. Thus, in this example, you want to highlight cells in the range C4:G11 when they are greater than the value entered in cell K4.

How to highlight a column based on another column

To use conditional formatting based on a value in another column, create a rule using a formula for range D5:D14: =$F4>$E4. Excel highlights values in column F that are greater than in column E.

Additional resources:

• Stop if True rule
• Multiple conditions