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:
How this formula works
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.
Note: Excel contains many built-in "presets" for highlighting values above / below / between / equal to certain values, but if you want more flexibility you can apply conditional formatting using your own formula as explained in this article. If you...
Note: Excel contains built-in "presets" for highlighting values above / below / equal to certain values, but if you want more flexibility you can apply conditional formatting with your own formula as explained in this article. If you want to...
Note: Excel contains many built-in "presets" for highlighting values with conditional formatting, including a preset to highlight cells that contain specific text. However, if you want more flexibility, you can use your own formula, as explained in...
To highlight entire rows with conditional formatting when a value meets specific criteria, use a formula with a mixed reference that locks the column. In the example shown, all rows where the owner is "bob" are highlighted with the following formula...
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.