In Excel for Microsoft 365 on Windows, color scale conditional formatting shades cells according to their numeric values, making higher and lower results easier to spot. Select the cells, then choose Home > Conditional Formatting > Color Scales and pick a palette; Excel applies the gradient without changing the underlying values.
What color scales do in Excel
A color scale maps the numbers in a selected range to a gradual change in fill color. In a two-color scale, one color represents the low end and another represents the high end, with intermediate values shown in blended shades. A three-color scale adds a midpoint color, which is useful when a meaningful center separates low, middle, and high results.
For example, a sales column might use pale red for its lowest value and green for its highest. Cells between those extremes receive colors along the gradient. The shading is a visual cue, not a new value or a calculation: it does not edit the cell, change a formula, or sort the rows. If you remove the conditional formatting, the original numbers remain.
Excel’s built-in choices include several two- and three-color palettes. The colors are applied as conditional formatting rules, so they can update when the numbers change. A scale is most useful when the reader needs to compare many numeric values at a glance, rather than simply identify whether each value passes one fixed test.
How to apply a color scale
On Windows, select the cells you want to compare and use the Home ribbon. For example, select B2:B31 if that is where the measurements are, then follow these steps:
- Choose Home > Conditional Formatting > Color Scales.
- Preview the available palettes by pointing to them, then select a two-color or three-color scale that suits the data.
- Check the results across the range, including the smallest and largest numbers, to confirm the direction and contrast make sense.
Excel uses the selected range as the scale’s comparison group. If B2:B31 contains the measurements, the lowest and highest values in those cells normally anchor the gradient. Applying another scale to a different group creates a different comparison: a value of 50 may look like a high value in one group and a low value in another. This matters when people compare separate tables visually.
Excel for Mac also provides color scales from the Home tab’s Conditional Formatting controls; the ribbon layout and dialog appearance can vary by release. In Excel for the web, select the range and use Home > Conditional Formatting > Color Scales. Some detailed rule-editing controls may be arranged differently or be more limited in the web interface than in desktop Excel. If you cannot find a particular setting online, open the workbook in the desktop app.
How scale thresholds and midpoints work
The default scale is based on the values in the selected cells. In a two-color scale, Excel assigns the low-end color to the minimum and the high-end color to the maximum, then shades the values in between. A three-color scale assigns a separate color to a midpoint as well. The default midpoint is generally centered between the minimum and maximum, so it does not necessarily equal the average or median.
To control the boundaries, open Home > Conditional Formatting > Manage Rules, select the rule, choose Edit Rule, and adjust the settings for the minimum, midpoint, or maximum. Depending on the point in the scale, the rule can use options such as Lowest Value, Highest Value, Number, Percent, Percentile, or Formula. Available options differ for the minimum, midpoint, and maximum.
- Lowest Value and Highest Value make a boundary follow the smallest or largest value in the applied range. These suit changing lists where the actual extremes matter.
- Number sets a fixed numeric boundary. For example, a temperature scale can use 0 and 30 as meaningful endpoints, even when the current data does not contain either number.
- Percent places a boundary at a position across the span from the minimum to the maximum. It is not the same as a percentile rank.
- Percentile sets a boundary by a value’s position in the distribution. It can help when extreme outliers would otherwise compress most of the gradient into similar colors.
- Formula lets a numeric formula determine a boundary, which is useful when the threshold depends on another cell or a workbook-specific calculation.
Choose boundaries based on what the colors are supposed to communicate. If red means below a service target and green means above it, use the target as a meaningful boundary rather than allowing the current minimum and maximum to define the story. A fixed midpoint can also make results easier to compare between reporting periods.
Choosing a scale for real data
Start by asking whether color should communicate relative position or a business meaning. A lowest-to-highest gradient answers, “Which values are lower or higher than the others in this range?” It does not, by itself, answer, “Which values are good?” If every result is poor, a relative scale still assigns its most favorable color to the best result in the group.
A midpoint is useful when there is a meaningful center, such as zero for profit and loss, a target score, or the middle of an acceptable measurement range. Consider a three-color scale with a neutral color at zero when negative values represent losses and positive values represent gains. For a measure where only one threshold matters, a simple conditional rule such as highlighting values below a target may communicate the point more directly than a full gradient.
Consider these factors before choosing a palette:
- Direction: Confirm whether high values are desirable, undesirable, or simply different. Reverse the color order if the default palette suggests the wrong interpretation.
- Meaningful limits: Use fixed boundaries when values must be compared against a target or standard, rather than only against the current range.
- Distribution: A single unusually large or small value can stretch the scale and make the rest appear nearly the same color. Percentile boundaries or a carefully chosen fixed range may help.
- Readability: Keep text legible over every shade. A pale-to-dark gradient is often easier to read than colors that become too dark behind black text.
Worked example: comparing monthly results
Imagine a table with one row per employee and monthly sales in column B. Applying a two-color scale to B2:B21 answers which results are relatively low or high within that month’s list. It does not show whether anyone met a sales goal. If the goal is 10,000, a scale based only on the observed minimum and maximum may color 9,800 as relatively strong even though it falls short of the goal.
For a goal-focused view, edit the rule and use a fixed boundary that reflects the target, or use a separate conditional formatting rule to mark values below 10,000. If results can be substantially below and above the goal, a three-color scale with a midpoint at 10,000 can show shortfalls, near-target values, and results above goal. This remains a visual choice: add a label or note explaining the palette so a reader does not have to guess what each color means.
Now suppose the next month’s results are placed in column C. Applying the same default scale independently to C2:C21 makes each month use its own extremes. The colors show relative rank within each month, not whether the same sales value has the same color across months. To compare absolute performance across time, use consistent fixed boundaries for both ranges, or apply one rule to a range that includes both months if that matches the intended comparison.
Editing, extending, and removing a rule
To inspect a scale, select a cell it applies to and choose Home > Conditional Formatting > Manage Rules. In the rules manager, check the range in Applies to and the rule description. If the rule is not listed, change the Show formatting rules for control to the relevant worksheet or selection. Select the scale and choose Edit Rule to change its colors, threshold types, or values.
When new rows are added, check whether the rule’s Applies to range includes them. A rule applied to B2:B31 will not necessarily cover data entered well below row 31. You can edit the range in Manage Rules so it covers the additional cells. If the data is in an Excel table, table expansion can help keep related formatting with new rows, but verify the conditional formatting range after adding data, especially if the workbook has several overlapping rules.
To remove formatting, select the affected cells and use Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. To remove the worksheet’s conditional formatting, choose Clear Rules from Entire Sheet instead. Clearing a rule removes the visual treatment, not the data. Be cautious with the entire-sheet option because it can remove other conditional formatting rules on that sheet as well.
Common problems and how to fix them
Every cell looks nearly the same. The values may be tightly grouped compared with an outlier, or the selected palette may have too little contrast. Inspect the minimum and maximum, then consider a percentile boundary, meaningful fixed limits, or a stronger palette. Do not hide a genuine outlier without deciding whether it matters to the analysis.
The colors imply the wrong result. Excel does not know whether a larger number is good. A red-to-green scale may be misleading for a measure where higher values mean more defects. Change the color order or choose a palette whose direction matches the metric, and explain what the endpoints mean.
Two equal values look different. They may be in different conditional formatting ranges with different minimums and maximums. Compare their rules and Applies to ranges in Manage Rules. To make equal values use a consistent color, put them under the same scale and use consistent thresholds.
Unexpected cells are colored. The rule may apply to a larger or different range than intended, or overlapping conditional formatting rules may affect the result. Check Manage Rules and the Applies to field. Also remember that dates are stored as numbers in Excel, so a date included in a numeric scale can affect the scale’s extremes.
Color scales are designed to evaluate numeric values. Text entries and empty cells do not provide ordinary numeric points for the gradient. If a cell contains a formula that returns a number, that numeric result can be colored; if the formula returns an empty string, it does not behave like a numeric measurement. Review the actual cell contents when the display seems inconsistent.
Color scales compared with other formatting
Use a color scale when the gradual difference between values matters. Use Data Bars when readers should compare approximate magnitudes while still seeing the numbers in their cells. Use Icon Sets when a small number of categories, such as arrows or symbols for low, medium, and high, is clearer than a continuous gradient. Use Highlight Cells Rules when the key message is a specific condition, such as values greater than a target or duplicate entries.
These tools answer different questions, and none changes the underlying data. A color scale emphasizes relative position across a range; a data bar emphasizes size; an icon set classifies values into visual groups; and a highlight rule draws attention to values meeting a defined test. Combining several types can create clutter, so use the smallest number of visual signals that makes the worksheet easier to interpret.
Consider accessibility as well as appearance. Red and green alone can be difficult for some readers to distinguish, and a color-only message may disappear in grayscale printing. Choose a palette with clear lightness differences, keep numbers visible, and add a label, symbol, or separate status column when the color carries an important decision. Do not assume that a particular shade has a universal meaning across teams or reports.
Frequently Asked Questions
Can I make an Excel color scale use a fixed number?
Yes. Open Home > Conditional Formatting > Manage Rules, select the scale, choose Edit Rule, and set the relevant minimum, midpoint, or maximum type to Number. Enter the boundary you want, then confirm that the colors still communicate the intended meaning.
Does a color scale show the average or median?
Not automatically. A default three-color scale places its midpoint between the minimum and maximum, which is not necessarily the average or median. To use a meaningful center, edit the rule and set a specific number or another appropriate threshold.
Can I use a color scale for text?
A color scale is intended to grade numeric values, not rank words such as Low, Medium, and High. For text categories, use a rule that matches specific text or assign explicit category formatting instead of expecting a numeric gradient.
Will a color scale update when values change?
Yes. Conditional formatting recalculates the colors as numeric values in its applied range change. If you add cells outside that range, however, they may not receive the rule until you extend its Applies to range.
Can I use more than three colors?
The built-in Excel color scale rules offer two-color and three-color scales. For more complicated categories or distinct thresholds, consider multiple conditional formatting rules, but check for overlaps and make sure the resulting colors remain understandable.
Related
- Bottom Border Excel Shortcut and How to Use It
- Billion Number Format in Excel: Display Values in Billions
- Birthday List Excel Template: Setup and Tips
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Filter Unique Values in Google Sheets With a FormulaIf the values are in A2:A and their statuses are in B2:B, enter =UNIQUE(FILTER(A2:A7,B2:B7="Paid",A2:A7<>"")) to return each nonblank…
- Google Sheets: Count Weekdays Between DatesYes. In Google Sheets, use =NETWORKDAYS(A2,B2) to count Monday-through-Friday workdays between the start date in A2 and the…
- Excel List of Values Dropdown: 10 Reliable Ways to BuildIn Excel for Microsoft 365 on Windows, select the cells for the dropdown, choose Data > Data Validation,…
- CSV vs Excel Logo: What the File Icons Actually MeanA CSV file does not have a universal Excel logo: CSV is a text-file format, while the green…
- How to Set Up Solver in ExcelTo set up Solver in Excel for Microsoft 365 on Windows, enable the Solver Add-in from File >…
- Excel Shortcuts for Evenly Sized Rows and ColumnsYes. In Excel for Microsoft 365 on Windows, select the columns or rows you want to make equal,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.