Conditional Formatting Color Based on Value in Excel

In Excel for Microsoft 365 on Windows, select the cells, then choose Home > Conditional Formatting and select a Color Scale or a rule that assigns a fill color when a value meets a condition. A color scale shows relative low-to-high values, while a rule is better when you want clear categories such as below, at, or above a target.

What conditional formatting does

Conditional formatting changes a cell’s appearance when its value or another specified condition meets a rule. The underlying value stays in the worksheet: coloring a cell does not replace its number, change a formula result, or sort the data. If the value changes, Excel checks the rule again and updates the appearance automatically.

Color is commonly used to make patterns easier to scan. A sales table might shade weak results red and strong results green; a project tracker could highlight overdue dates; a temperature list could use a gradient to reveal the warmest and coldest readings. The best approach depends on whether readers need to see a smooth range of values or identify specific thresholds.

Conditional formatting is attached to a worksheet range and one or more rules. A rule can use the cell’s own value, compare it with a number or date, or evaluate a formula. Excel applies the chosen format only when the rule’s condition is true, except for color scales, data bars, and icon sets, which visualize values across the selected range.

Choose a color scale or a threshold rule

A color scale is useful when the relative position of each value matters more than a fixed pass-or-fail boundary. A two-color scale blends between a low-value color and a high-value color. A three-color scale adds a midpoint color, making it easier to distinguish low, middle, and high parts of the distribution. For example, a three-color scale can make the smallest response times red, middle values yellow, and largest values green.

For exact business cutoffs, use a rule instead. If a score of 70 is the minimum passing result, a threshold rule can color scores below 70 red and scores at or above 70 green. That is more dependable than a gradient: the color boundary stays at 70 even when new values are added or the range changes. Color scales recalculate their relative colors from the values in their range, so a value’s shade can change as the surrounding data changes.

Color scale: best for spotting relative highs, lows, and clusters in numeric data.
Highlight Cells Rules: best for fixed comparisons, such as greater than a target, equal to a status value, or between two numbers.
Data bars: useful when readers should see both the number and a bar representing its size.
Icon sets: useful for compact status signals such as arrows or traffic-light symbols, though icons should not be the only way to communicate important information.

Apply a color scale in Excel

For a quick gradient in Excel for Microsoft 365 on Windows, select the numeric cells you want to compare. Then use Home > Conditional Formatting > Color Scales and choose a preset. Excel applies the scale to the selection immediately. The rule generally uses the lowest and highest values in the selected range, with a midpoint for a three-color scale.

Select the data cells, excluding headings if you do not want headings included in the scale.
Choose Home > Conditional Formatting > Color Scales.
Preview the available two-color or three-color presets and select one with enough contrast for your worksheet.
Check the result against the actual numbers. If the colors obscure text or make small differences look more important than they are, choose another preset or edit the rule.

To customize a scale, open Home > Conditional Formatting > Manage Rules. In the rules manager, select the relevant rule and choose Edit Rule. You can change the color and the type of value used for the minimum, midpoint, or maximum. Depending on the scale point, Excel can base it on a lowest or highest value, a number, a percent, a percentile, or a formula. A percent in this rule describes a position within the range; it does not necessarily mean that Excel is testing whether the cell contains a percentage such as 25%.

On Excel for Mac, the Conditional Formatting controls are on the Home tab as well, though the ribbon layout and dialog appearance can differ. In Excel for the web, select the range and use Home > Conditional Formatting; some advanced rule-editing options may differ from the desktop dialogs. If you cannot find a particular setting in the web app, open the workbook in the desktop version of Excel.

Use fixed color rules for meaningful cutoffs

For a fixed threshold, select the range and choose Home > Conditional Formatting > Highlight Cells Rules. Choose a comparison such as Greater Than, Less Than, Between, or Equal To, enter the value, and choose a preset format. For example, a task list can use a Less Than rule on a completion percentage to flag items below a required level. You can create a second rule for values that meet or exceed that level and choose a contrasting format.

Rules can also compare dates or text. A date rule can highlight deadlines in a selected period, while a text rule can find cells containing a status such as Pending. Make sure the selected range matches the kind of rule you are creating. Applying a date rule to text that merely looks like a date may not work as expected, because the cell may contain text rather than an Excel date value.

When a rule’s fill color is not among the ready-made choices, use Home > Conditional Formatting > New Rule and choose a rule type that lets you set its format. Select Format to choose a fill, font, or border. Use a restrained palette: a table with too many competing colors is harder to interpret than an unformatted one.

Color a row based on a value in another column

A frequent request is to color an entire record based on one column. For example, you may want every cell from A2 to C6 colored when the score in column B is at least 100. Select A2:C6, choose Home > Conditional Formatting > New Rule, and select Use a formula to determine which cells to format.

Assuming the scores are in B2:B6 and the selected range is A2:C6, enter =$B2>=100 as the rule formula. Choose a format and confirm the rule. For a sample list with B2:B6 equal to 72, 105, 160, 95, and 210, the condition is false for the first and fourth rows and true for the second, third, and fifth rows. Thus the rule colors A3:C4? No: because the selected rows correspond to the values, it colors A3:C3, A4:C4, and A6:C6 when applied to a larger range extending through row 6. The formula locks the B column with the dollar sign but leaves the row number relative, so Excel checks B2 for the first selected row, B3 for the next, and so on.

Check the Applies to range in the Conditional Formatting Rules Manager if the result is shifted or incomplete. The formula’s row reference should normally match the first row of the applied range. If the selected range starts at row 2 but the formula starts with row 3, Excel may evaluate the wrong record. The dollar sign before B is helpful when the rule spans multiple columns; it keeps the comparison in column B while the formatting moves across each row.

Understand rule order and overlapping formats

A cell can be affected by more than one conditional-formatting rule. For instance, a range might have a color scale and a separate rule for values below a minimum. Excel uses rule order to resolve overlapping formatting. Open Home > Conditional Formatting > Manage Rules and set Show formatting rules for to the current selection or the relevant worksheet. The manager lets you inspect, edit, reorder, and delete rules.

When two rules set the same format property, such as fill color, the higher-priority applicable rule can determine what appears. The Stop If True option can prevent lower-priority rules from being evaluated after a rule is true. This option is useful when rules are intentionally arranged in priority order, but it can also hide a color scale or another rule if used without considering what follows. If the result looks unexpected, review the order and the Applies to ranges before rebuilding the rules.

Make the colors readable and useful

Color is an aid, not a substitute for clear labels or values. Choose colors with enough contrast against text and avoid relying on red-versus-green alone, since some readers have difficulty distinguishing those colors. If the distinction is important, include a label, icon, or explanatory note as well. Check the worksheet in grayscale or with a color-vision accessibility tool if the colors carry essential meaning.

Consider the data distribution before choosing a scale. One unusually large value can compress most other values into nearly the same shade. A scale with a midpoint can help when the midpoint has meaning, such as zero for positive and negative differences, but a midpoint based only on the range may not represent a meaningful target. In that situation, use a rule with an explicit threshold or edit the scale’s midpoint to a meaningful number.

Also decide whether blanks, zeros, and error values should receive a color. A blank may be treated differently from a numeric zero, and a scale can make zero appear like an ordinary low value even when it means no data was recorded. A short note explaining the scale and the meaning of blank cells prevents readers from drawing the wrong conclusion.

Common problems and how to fix them

Nothing changes: confirm that the selected cells fall within the rule’s Applies to range, and check that the values are numeric or real dates rather than text that only looks numeric.
The wrong cells change color: check the first-row reference in any formula rule and verify whether column references should be fixed with a dollar sign.
The gradient looks nearly uniform: inspect the range for an outlier, or choose a fixed threshold rule if the decision depends on a specific cutoff.
A manually applied fill seems to disappear: conditional formatting can take precedence over ordinary cell formatting while its condition is true. Edit or remove the rule rather than repeatedly changing the cell’s standard fill.
New rows are not formatted: the rule may not include those rows in Applies to. Expand the range or set up the rule over the area where future entries will go.
Colors seem inconsistent: look for overlapping rules, different Applies to ranges, or rules that were copied from another area with references that shifted.

Conditional formatting is usually preferable to manually coloring cells when values change often. Hand-applied fills do not recalculate when a number changes, so a previously correct color can become misleading. You can remove conditional formatting through Home > Conditional Formatting > Clear Rules, choosing whether to clear rules from selected cells or the entire worksheet. Review the selection before clearing because that action removes rules, not just the visible color.

Frequently Asked Questions

Can Excel change a cell’s color automatically when its value changes?
Yes. Apply a conditional-formatting rule to the cell or range. Excel reevaluates the rule when the relevant value changes and updates the display, while leaving the stored value unchanged.

Can I color an entire row based on one cell?
Yes. Select the full row range you want formatted, create a formula-based rule, and refer to the condition column with a fixed column reference and a relative row reference. Set the formula to match the first row of the selected range.

Can a color scale use a fixed number as its midpoint?
Yes. Edit the color-scale rule and set the midpoint type to Number, then enter the desired value and color. This is useful when the midpoint represents a target such as zero or a score threshold.

Why does the same value have a different shade in another table?
Each color scale is evaluated over its own applied range. If the lowest and highest values differ between tables, the same number can occupy a different relative position and receive a different shade.

Does conditional formatting change the actual cell value?
No. It changes the cell’s appearance according to a rule. Formulas that refer to the cell still use its underlying value, not the displayed color.

Can I sort or filter by a conditional-formatting color?
Yes. Excel’s sorting and filtering tools can use cell color, font color, or icon where applicable. This acts on the displayed formatting; it does not turn color into a numeric value or replace the original data.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.