Conditional Formatting Color Scales in Excel Explained

In Excel for Microsoft 365 on Windows, a conditional formatting color scale colors cells according to their numeric values, making higher, lower, or middle values easier to spot. Select the cells, then choose Home > Conditional Formatting > Color Scales and pick a preset; use More Rules to set your own colors and thresholds.

What color scales show

A color scale is a visual summary of numbers in a selected range. Excel compares the values and assigns colors along a gradient: one end of the scale represents the lower values, the other represents the higher values, and an optional middle color highlights values between them. A common preset runs from red through yellow to green, but you can choose other combinations.

Because the color depends on the cell’s value relative to the rule’s thresholds, a scale can reveal patterns that are hard to see in a column of figures. For example, a sales table may show which products are performing strongly, while a test-score range may make clusters of high and low results apparent at a glance. The numbers remain in the cells; conditional formatting changes their appearance rather than replacing them with a chart or a calculated label.

A color scale is most useful when the figures form a meaningful continuum. It is less suitable when colors must represent fixed categories, such as “approved,” “pending,” and “rejected.” Those categories need rules tied to specific labels or conditions, rather than a gradient based on numeric rank.

How Excel assigns colors

Excel evaluates the values covered by a rule and maps them to color points. A two-color scale has a low point and a high point. A three-color scale adds a midpoint, so values can transition from one color to a second and then to a third. Between the defined points, Excel interpolates colors, creating a gradual transition rather than assigning every value a separate color.

By default, the low and high points are based on the lowest and highest values in the range. That means the same number can receive a different color in a different range. If one department’s sales run from 10 to 100 and another’s from 50 to 150, Excel evaluates each scale against its own limits unless you set common numeric thresholds.

The rule applies to the range named in its “Applies to” setting. If you add rows outside that range, they might not receive the formatting. If the rule covers multiple separated areas, Excel evaluates the values within the rule’s scope according to its settings; check the range in Manage Rules if the coloring does not match your expectations. Color scales are designed for numeric values. Blank cells and text labels do not participate as ordinary numbers in the gradient.

Apply a preset or customize a scale

To add a preset in Excel for Microsoft 365 on Windows, select the numeric cells you want to compare. Leave out headings so a title or column label is not treated as data. Then choose Home > Conditional Formatting > Color Scales and select a two-color or three-color palette. The preview updates immediately, and the formatting changes when the underlying values change.

To create a custom scale, open Home > Conditional Formatting > Color Scales > More Rules. In the New Formatting Rule dialog, choose a two-color or three-color scale and set the color and threshold type for each point. For a three-color rule, configure the minimum, midpoint, and maximum. Select OK to apply the rule.

To change or remove a rule, select a cell in the formatted range and choose Home > Conditional Formatting > Manage Rules. Use the “Show formatting rules for” list to inspect rules for the current selection or worksheet. From there, you can edit the scale, adjust “Applies to,” change rule order where relevant, or delete a rule. The dialog layout can differ slightly on Mac, but the conditional-formatting controls are available from the Home tab. Excel for the web also offers conditional formatting, though some editing controls and dialog details can differ from desktop Excel.

Choose thresholds that fit the data

The threshold type determines what “low,” “middle,” and “high” mean. Excel provides choices such as Lowest Value, Highest Value, Number, Percent, Percentile, and Formula. The right choice depends on whether the scale should adapt to the current data or use fixed reference points.

Lowest Value and Highest Value: Use the extremes of the applicable range. This is a useful default for exploring a changing set of values, but the colors are relative: a low color does not necessarily mean a poor result.
Number: Set a specific numeric boundary. Fixed boundaries help compare periods or groups consistently. For instance, a threshold of 0 can separate negative from nonnegative results, while a chosen target can mark performance below and above that goal.
Percent: Set a point as a percentage of the distance between the minimum and maximum. It is not the same as the percentage of cells below a value.
Percentile: Set a point by rank in the distribution. A midpoint at the 50th percentile is tied to the middle-ranked data, not necessarily the arithmetic midpoint of the smallest and largest numbers.
Formula: Define a threshold using a worksheet formula that returns a value. This can help when a limit is stored in another cell or calculated from other data; choose and test the reference carefully.

Percent and percentile are easy to confuse. Suppose values range from 0 to 100. A Percent threshold of 50 places a point halfway through that numeric span. If most values are crowded near 0 and only a few are near 100, that point may not divide the cells into equal groups. A 50th-percentile threshold instead follows the middle rank, so roughly half the observations fall on either side, subject to ties and the data distribution.

Practical examples and good design choices

Imagine a monthly report with 12 product rows and a column of revenue figures. A two-color scale can make the smallest and largest revenues stand out, while a three-color scale can use a midpoint to distinguish lower, middle, and higher results. If the business has a clear target, a custom numeric midpoint is usually more informative than a midpoint that changes whenever the data changes.

For a table of temperatures, a three-color scale can show cold, moderate, and hot readings. In this case, fixed thresholds based on the context are often more useful than the range extremes: the hottest value in a cold week might still be below a meaningful warm-weather threshold. For a column of profit and loss values, a neutral midpoint at zero can make losses and gains visually distinct.

Use colors with enough contrast to distinguish the ends and, if applicable, the midpoint. A red-yellow-green scale is familiar, but it can be difficult for some people with color-vision deficiencies to interpret. A blue-white-red or another contrasting palette may work better, depending on the background and the message. Keep the underlying values visible, and explain the scale when readers may not know what each color signifies.

When comparing separate periods, regions, or teams, use consistent thresholds if the colors are meant to convey the same meaning. If each group gets its own automatic minimum and maximum, the darkest color in every group means only “highest in this group,” not the same absolute performance. That distinction is crucial when readers compare one heat map with another.

Common problems and how to fix them

If some cells have no color, first check that they contain numbers rather than text that merely looks like a number. Imported data can store numeric-looking values as text. Also verify that the cells fall inside the rule’s “Applies to” range and that the rule has not been removed or changed by another formatting rule.

If every cell looks nearly the same, the values may be tightly clustered, or one unusually large or small value may stretch the scale and compress the colors for the rest. A custom midpoint or fixed numeric limits can make the important variation easier to see. Do not choose limits just to create dramatic colors, though: a misleading scale can exaggerate minor differences or hide meaningful ones.

If colors change after you paste data or add rows, inspect Manage Rules. The rule may cover only the original cells, or the pasted cells may bring their own formatting. Adjust “Applies to” to include the intended range, and remove duplicate or unintended rules. Also check whether the worksheet contains multiple conditional-formatting rules that affect the same cells; rule order and “Stop If True” settings can matter for interactions with other formatting rules.

Sorting or filtering does not turn a color scale into a fixed rating system. A scale based on the current range may look different when its values or scope change. If colors need to stay attached to permanent categories or absolute performance bands, define rules with fixed thresholds or use a different conditional-formatting rule type.

Color scales compared with other options

Color scales are one of Excel’s conditional-formatting tools, but they are not the only way to visualize values. Data bars put a bar inside each cell, which can make magnitude comparisons intuitive while leaving the number readable. Icon sets assign symbols such as arrows or traffic-light icons according to thresholds. Highlight Cells Rules can emphasize values meeting a particular condition, such as values above a number or duplicate entries.

Choose a color scale when the overall spread and relative position of many values matter. Choose data bars when comparing amounts along a row or column is the main task and the cells have room for bars. Use icon sets or explicit rules when a small number of defined states matters more than a smooth gradient. These approaches can be combined, but multiple visual signals in the same cells may make a worksheet harder to interpret.

Frequently Asked Questions

Do color scales change the values in my cells?
No. A conditional formatting color scale changes how cells are displayed, not the stored values. You can still calculate with the numbers, and editing a value can change its color.

Can I make the midpoint zero?
Yes. Edit the rule through Home > Conditional Formatting > Manage Rules, choose the three-color scale, set the midpoint type to Number, enter 0, and select a suitable midpoint color. This is useful for values that cross from negative to positive.

Why does the same value have different colors in two columns?
Each rule may use the minimum and maximum of its own range. Set matching numeric thresholds in both rules if the same value should receive the same color across columns.

Can a color scale use text such as High, Medium, and Low?
A color scale is intended to grade numeric values, not assign colors to text labels. Use a rule such as Highlight Cells Rules or a formula-based conditional-formatting rule when the cells contain categories or labels.

Will the colors appear when I print or export the workbook?
Conditional formatting is generally displayed in the worksheet and can appear in print output, but check Print Preview because page layout, grayscale printing, and export settings can affect readability. If colors carry essential meaning, include labels or values so the message is not conveyed by color alone.

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.