Excel Conditional Formatting 4-Color Scale Explained

How-to basics · Updated November 19, 2026

Excel for Microsoft 365 does not have a built-in four-color scale: its Color Scales feature offers two- and three-color gradients. To show four distinct value ranges, select the cells and use Home > Conditional Formatting > New Rule to create four formula-based formatting rules, one for each range.

What Excel means by a color scale

A conditional-formatting color scale changes a cell’s fill according to its value relative to other values in a selected range. It is useful when you want to spot high and low numbers quickly, such as sales by region, response times, or scores. Unlike a rule that assigns one fill to every value above a cutoff, a scale can shade intermediate values differently.

The phrase four-color scale can mean two different things. Someone may want a continuous gradient that blends through four colors, or four discrete groups where each group gets one solid color. Excel’s built-in Color Scales command does not provide a four-stop gradient. Formula rules can, however, create four solid-color bands. Those bands are useful for rating ranges, but their boundaries are abrupt rather than smoothly blended.

How Excel’s built-in color scales work

Excel’s preset Color Scales are two-color or three-color scales. A two-color scale maps values between a low-end color and a high-end color. A three-color scale adds a midpoint color, so values move through three hues as they progress across the range. The colors between the selected points are interpolated, giving the cells a gradual appearance rather than dividing them into a few fixed categories.

The scale normally takes its low and high points from the selected cells’ values. In a three-color scale, the midpoint is not necessarily the arithmetic average of the smallest and largest values: its behavior depends on the midpoint type you choose. For example, a numeric midpoint of 50 uses the value 50, while a percentile midpoint uses a rank within the data. This matters when a dataset contains outliers or has a skewed distribution.

To apply a preset, select the range, then choose Home > Conditional Formatting > Color Scales and click a palette. To adjust the scale rather than use a preset, choose Home > Conditional Formatting > Manage Rules, select the scale, and edit the rule. In the New Formatting Rule dialog, a color-scale rule can use minimum, midpoint, and maximum settings, but it still has no fourth color stop.

Create four distinct color bands with formula rules

For four categories, create four rules with non-overlapping conditions. The example below uses values in C2:C100 and these bands: below 25, 25 to under 50, 50 to under 75, and 75 or higher. The ISNUMBER test prevents blank cells and text from being treated as values for these rules.

  1. Select C2:C100. Choose Home > Conditional Formatting > New Rule.
  2. Select Use a formula to determine which cells to format.
  3. Enter the first rule’s formula, choose Format, and set its fill color. Select OK.
  4. Repeat for the other three formulas, choosing a different fill for each rule.
  5. Open Home > Conditional Formatting > Manage Rules and confirm that each rule applies to =$C$2:$C$100.

With values in C2:C100, enter the first formula for the below-25 band. For example, if C2 is 18, it returns TRUE and applies that rule’s fill; if C2 is 25, it returns FALSE.

=AND(ISNUMBER(C2),C2<25)

With the same C2:C100 layout, enter this formula for values from 25 up to but not including 50. If C2 is 25, the formula returns TRUE; if C2 is 50, it returns FALSE.

=AND(ISNUMBER(C2),C2>=25,C2<50)

With values in C2:C100, use this formula for values from 50 up to but not including 75. A value of 50 in C2 returns TRUE, while 75 returns FALSE.

=AND(ISNUMBER(C2),C2>=50,C2<75)

With values in C2:C100, use the final formula for values of 75 or above. If C2 contains 75, it returns TRUE; if it contains 74.9, it returns FALSE.

=AND(ISNUMBER(C2),C2>=75)

The formulas refer to C2, the top-left cell of the selected range. Excel adjusts that relative reference for each cell in the Applies to range. Do not lock the reference as $C$2 in these formulas: doing so would make every cell’s rule depend on C2 instead of checking its own value. The dollar signs are appropriate in the Applies to range, where they keep the target range fixed.

Choose cutoffs that fit the data

The example’s cutoffs are arbitrary; they are not built-in Excel ratings. Choose boundaries that make sense for the measurement. For a percentage score, four bands might represent below 60, 60–under 75, 75–under 90, and 90 or higher. For delivery times, lower values may be better, so the most favorable color could belong to the first band rather than the last.

Decide how to handle values exactly on each boundary before writing the rules. The example assigns 25 to the second band, 50 to the third, and 75 to the fourth. Using “less than” for the upper edge and “greater than or equal to” for the next band avoids gaps and overlaps. If your data includes decimals, confirm that the thresholds still divide the values as intended.

When you edit a built-in color scale, the threshold types affect what the colors mean. Number uses a stated value. Percent positions a threshold relative to the range from minimum to maximum; it is not the same as Percentile, which refers to a place in the data’s distribution. A midpoint set to the 50th percentile, for example, corresponds to the median rank, whereas a midpoint set to 50 percent is halfway across the numerical span. Use a Number threshold if a business cutoff is the point you need to communicate.

Four bands versus a four-stop gradient

Four formula rules are best when your categories have clear meanings: pass, review, warning, and critical, for example. Each value falls into one category, and the fill remains consistent within that category. Readers can interpret the colors against the stated cutoffs, and the categories do not shift simply because a new high or low value enters the range.

A built-in three-color scale is often better for exploring a continuous dataset. It shows relative variation without forcing values into categories. Its colors can change when the set of values changes, since the scale is evaluated against its range. That is helpful for spotting patterns but can make comparisons between separate tables less direct.

If you specifically require a smooth gradient that passes through four chosen colors, standard Excel conditional-formatting color scales cannot define that fourth stop. A practical alternative is to use the three-color scale and select the most useful low, middle, and high colors, or to split the data into four categories with formula rules. A chart with a suitable color treatment may be another option when the goal is to communicate a continuous pattern, rather than color the worksheet cells themselves.

Common problems and how to fix them

  • Every cell gets the same color: Check that the formula uses a relative reference such as C2 and that the rule applies to the intended range. A locked reference such as $C$2 can make all cells test one value.
  • Some cells have no fill: Check whether their contents are numbers or numeric values stored as text. The example rules deliberately exclude text and blanks using ISNUMBER. Convert text-formatted numbers if they should be included.
  • A boundary value seems to be in the wrong category: Review the less-than and greater-than-or-equal operators. In the example, 50 belongs to the third band, not the second.
  • Colors appear to conflict: In Manage Rules, inspect the rule order and Applies to ranges. Formula rules that do not overlap should not compete, but other conditional-formatting rules may also apply to those cells. Adjust the rules or their order if necessary.
  • The scale changes after editing the data: That is normal for a scale based on the range’s minimum and maximum. Set explicit numeric thresholds if the colors must represent fixed standards over time.

When copying the setup to another column or worksheet, verify the Applies to range and the relative cell reference. A formula based on C2 is suitable for a range beginning in C2; if the selection begins in D5, the formula should refer to D5 so each cell is evaluated from the correct starting position.

Make the colors understandable

Color alone should not carry essential meaning. Use a clear heading or a short legend that states what each band represents, especially if the worksheet will be printed or shared. Choose fills with enough contrast against the text, and avoid relying only on red and green to distinguish categories; some readers have difficulty differentiating those colors.

For a four-band example, a legend could list the numeric ranges alongside labels such as low, moderate, high, and very high. Ensure the labels reflect the actual measure: for error counts, a higher value may be worse, while for completed tasks, a higher value may be better. If the thresholds change, update both the conditional-formatting rules and the legend so they remain consistent.

Frequently Asked Questions

Can Excel make a four-color conditional-formatting scale?
Not as a single built-in color-scale rule. Excel’s Color Scales feature supports two or three colors. To show four solid categories, use four formula-based conditional-formatting rules; these create bands, not a continuous four-stop gradient.

How do I add a fourth color to an Excel color scale?
You cannot add a fourth stop to the standard Color Scales rule. Use four mutually exclusive formula rules and assign a fill to each, or choose a three-color gradient if continuous shading is more important than a fourth color.

Can I use percentages as the four cutoffs?
Yes. In formula rules, you can compare the cell value with numeric percentage cutoffs when the values are stored as percentages. For example, 25% is stored as 0.25 in Excel. Be sure the formula’s threshold uses the same scale as the underlying values.

Why is my formula-based color rule not applying?
Check the formula reference, the Applies to range, and the cell values’ data types. The formula should refer to the selected range’s top-left cell, and ISNUMBER excludes text and blanks. Also review other rules that may format the same cells.

Can I use a color scale and formula rules on the same cells?
Excel can have multiple conditional-formatting rules applying to the same cells, but their combined appearance can be confusing. Review them in Manage Rules and choose a single approach where possible. A built-in scale is for a gradient; formula rules are for explicit categories.

Related

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.