Quick, clean, and to the point

Conditional formatting based on another cell

Excel formula: Conditional formatting based on another cell
Generic formula 

To apply conditional formatting based on a value in another cell, you can create a rule based on a simple formula. In the example shown, the formula used to apply conditional formatting to the range C5:G15 is:


Excel contains many built-in "presets" for highlighting values with conditional formatting, including a preset to highlight cells greater than a specific value. However, by using your own formula, you have more flexibility and control.

In this example, a conditional formatting rule is set up to highlight cells in the range C5:G15 when then are greater than the value entered in cell J6. The formula used to create the rule is:


The rule is applied to the entire range C5:G15, and the value in J6 can be changed at any time by the user. When a new value is entered, the highlighting is immediately updated.

The formula uses the greater than or equal to operator (>=) to evaluate each cell in the range against the value in J6. The reference to C5 is relative and changes as the formula is evaluated for each cell in the range. The reference to cell J6 is "locked" as an absolute reference ($J$6).

When a value in the range is greater than or equal to 15 (the current value in J6), the formula returns TRUE and the rule is triggered.

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Charts video training course
Video training for Excel Tables
Dynamic Array Formulas