In Excel for Microsoft 365 on Windows, select the numeric cells, then choose Home > Conditional Formatting > Color Scales and select a three-color preset. To set your own colors and thresholds, choose Home > Conditional Formatting > New Rule > Format all cells based on their values, then set Format Style to 3-Color Scale.
What a three-color scale does
A three-color scale fills cells with a continuous blend of colors based on the numbers in the selected range. One color represents values at the low end, another marks a midpoint, and a third represents values at the high end. Values between those points receive blended shades rather than being assigned to just one of three separate categories.
For example, imagine a column of monthly response times. A red-yellow-green scale could shade shorter times green, longer times red, and values around the midpoint yellow. A reader can spot patterns and outliers without adding a chart or sorting the table. The colors are applied to the cells themselves and update when the underlying values change.
This is a visual aid, not a calculation or a change to the stored data. A cell containing 42 remains 42; conditional formatting changes only how Excel displays it. It also does not automatically label a value as good or bad. That meaning depends on the colors you choose and the context of the data.
How Excel chooses the colors
Excel evaluates the numeric values within the rule’s applied range. It maps the low point, midpoint, and high point to the three selected colors, then blends between them. With the default-style setting, the minimum and maximum are usually based on the lowest and highest values in that range, while the midpoint is based on the 50th percentile. The exact preset choices and initial settings can vary, so inspect the rule if the mapping matters.
Suppose the range contains 10, 20, 30, 40, and 50. If the rule uses the lowest value, a midpoint of 30, and the highest value, the value 10 receives the low-end color, 30 the midpoint color, and 50 the high-end color. Values such as 20 and 40 receive intermediate blends. If you change the midpoint to a fixed number, the transition is anchored to that number instead of simply using the range’s statistical center.
The scale is relative to the rule’s scope. If you apply the same rule separately to two columns, each column can have its own low and high values. A value of 50 might look like a high value in one column and a low value in another. That is useful for comparing patterns within each group, but it can mislead if the intention is to compare absolute values across groups.
Apply a preset color scale
For a quick result in Excel for Microsoft 365 on Windows:
- Select the cells that contain the numbers you want to compare. Include the entire data range, but avoid including headings unless you intend them to be part of the rule.
- On the ribbon, select Home > Conditional Formatting > Color Scales.
- Choose a three-color preset from the gallery. The preview shows the general order of its colors.
- Review the worksheet and make sure the result communicates the pattern clearly. If it does not, undo the change or edit the rule.
To inspect or change the rule later, select a cell in the range and choose Home > Conditional Formatting > Manage Rules. In the rules manager, check the “Applies to” range and select the rule to edit. If the manager is showing rules for the current selection, it may not show every rule on the sheet; change the display option to show rules for the worksheet when needed.
Excel for Mac has conditional formatting and color-scale options as well, though dialog layout and some ribbon details can differ by release. Excel for the web also supports color scales for common tasks, but desktop Excel generally offers more complete rule-management controls. If an option is missing or arranged differently, check the current application’s Conditional Formatting menu and rule editor.
Customize colors and thresholds
Use a custom rule when the default scale does not match the meaning of your numbers. Select the target range and choose Home > Conditional Formatting > New Rule. Choose “Format all cells based on their values,” set Format Style to “3-Color Scale,” then select the color and Type for the minimum, midpoint, and maximum. The Type menu lets you define thresholds in ways such as lowest or highest value, a number, a percentage, a percentile, or a formula. Available choices can depend on which scale point you are editing.
Choose the threshold type according to the question you want the colors to answer:
- Lowest Value and Highest Value: Anchor the ends of the scale to the observed extremes in the applied range. These are convenient for a quick overview, but a new extreme can change the appearance of every other cell.
- Number: Set a meaningful fixed boundary, such as a target or limit. This helps maintain a consistent interpretation when values are added or the range changes.
- Percent: Set a position relative to the numerical span from the rule’s minimum to maximum. This is not the same thing as saying that a value is at a particular percentile in the data.
- Percentile: Set a point based on the distribution or rank of values. A 50th-percentile midpoint is intended to represent the center of the observations, which may differ from the arithmetic average.
For example, if a quality score ranges from 0 to 100 and 70 is the important boundary, a fixed midpoint or threshold around 70 may communicate more than a midpoint that moves whenever the data changes. A three-color scale still blends across its range; it does not create a sharp pass/fail boundary. If you need cells below 70 to be one color and cells at or above 70 another, use a separate rule such as Highlight Cells Rules or a formula-based conditional-formatting rule instead.
Choose a useful scale for your data
Color order should match the meaning of the measure. Red-yellow-green is familiar for low-to-high comparisons, but it implicitly suggests that one end is undesirable and the other desirable. That can be wrong: a high count of defects is not good simply because it is “high.” For values where neither end is inherently positive, choose a restrained palette that indicates magnitude without implying a judgment.
Consider these practical questions before picking a preset:
- Does a larger number mean better performance, greater risk, more volume, or something else?
- Should the scale show relative position within this range, or should the colors correspond to fixed targets?
- Are negative and positive numbers meaningful on opposite sides of zero? If so, set the midpoint to 0 rather than relying on a midpoint based on the data distribution.
- Will people need to read exact values? If yes, keep the cell numbers visible and avoid colors so dark that they reduce text contrast.
For a measure that crosses zero, a diverging palette can make the midpoint especially clear. If zero is the meaningful boundary, configure the midpoint as a number with value 0. Otherwise, Excel’s default midpoint may be above or below zero, especially when the range is skewed or contains values mostly on one side.
Three-color scales compared with other formats
A two-color scale is simpler: it blends between a low and high color without a separately defined midpoint. It can work well when a center color would add little meaning. A three-color scale is helpful when the middle has significance, when you want a neutral transition, or when a range crosses a meaningful center such as zero.
Data bars emphasize approximate size using an in-cell bar, making them useful for comparing amounts while retaining the numbers. Icon sets assign symbols to groups or thresholds, which can make categories easier to scan but can hide subtle variation. A color scale emphasizes a smooth gradient instead. None is universally best: use the display that answers the reader’s question with the least ambiguity.
A color scale also differs from a chart. It provides context inside a table but does not show axes, labels, or a clear shape across a sequence. For a long time series or a comparison across many categories, a chart may explain the pattern more effectively. You can use a scale and a chart together if both add useful information rather than competing for attention.
Common problems and how to fix them
- Colors change when new data is added: With low- and high-value endpoints, a new extreme can stretch the scale. Edit the rule to use fixed numeric thresholds if comparisons need to remain stable.
- The same number has different colors in different sections: The sections may have separate rules or separate applied ranges. Check Manage Rules and decide whether they should share one scale and one scope.
- The midpoint does not look like the average: The midpoint may be based on a percentile rather than the arithmetic mean. Set a numeric midpoint if a particular value, such as a target or zero, is what matters.
- Blank cells or text behave unexpectedly: Color scales are designed for numeric values. Check that values intended to be numbers are stored as numbers rather than text, and remove unwanted cells from the “Applies to” range.
- Another format seems to override the colors: A cell may have a different conditional-formatting rule or a manually applied fill. Review the rules manager, their order and scope, and the option to stop if true where relevant. For a color-scale rule itself, first confirm that it applies to the cells in question.
- The colors are hard to interpret: Use a less intense palette, preserve readable text contrast, and add a note explaining what the low, middle, and high colors mean. Do not rely on color alone for an important distinction.
For accessibility, consider that readers may have color-vision differences or may print the sheet in grayscale. Use labels, numbers, symbols, or a written explanation alongside the gradient when the distinction matters. A three-color scale cannot guarantee that every reader will distinguish every shade.
Frequently Asked Questions
How do I add a three-color scale in Excel?
Select the numeric range, then choose Home > Conditional Formatting > Color Scales and select a three-color preset. For custom colors or thresholds, create a New Rule and choose 3-Color Scale.
Can I make the middle color represent zero?
Yes. Edit or create the rule and set the midpoint Type to Number and its value to 0. This is useful for data that includes positive and negative values when zero is the meaningful dividing point.
Does a three-color scale use the average as its midpoint?
Not necessarily. The midpoint type may use a percentile or another threshold setting, and a percentile is not the same as an arithmetic average. Inspect the rule or set a fixed numeric midpoint if you need a specific value.
Can I use a three-color scale for text?
It is intended to compare numeric values, not to assign colors to text labels such as “High,” “Medium,” and “Low.” For text categories, use a text-specific conditional-formatting rule or a formula-based rule.
Why did the colors change after I added a row?
If the scale uses the lowest and highest values, adding a new minimum or maximum can change how the gradient is distributed. Check whether the new row is included in the rule’s “Applies to” range, and use fixed numeric thresholds when stable color meanings are important.
Related
- Excel Autofit Shortcut Key: Resize Rows and Columns
- Autofill Weekday Dates in Excel and Skip the Weekends
- Close Excel Shortcut Key for Windows and Mac
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Circular Reference Settings in Excel: How They WorkIn Excel for Microsoft 365 on Windows, turn on circular-reference calculations at File > Options > Formulas by…
- Excel Table Borders: Add, Change, and Print ThemTo add borders to an Excel table in Microsoft 365 for Windows, select the cells, then choose Home…
- Excel: Group Rows by the Same ValueYes. In Excel for Microsoft 365 on Windows, sort your records by the value you want to group,…
- Remove Formatting in Word: Keyboard Shortcuts ExplainedIn Microsoft Word for Windows, press Ctrl+Spacebar to remove direct character formatting, such as manually applied bold or…
- Microsoft Word: Insert a Table of Contents AutomaticallyTo insert a table of contents in Microsoft Word, apply built-in Heading styles to your section titles, then…
- How to Change Page Breaks in ExcelTo change a page break in Excel for Microsoft 365 on Windows, open the worksheet, choose View >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.