Excel Chart Color Based on Value: How to Set It Up

Yes. In Excel for Microsoft 365 on Windows, the most reliable way to color chart points according to their values is to create helper columns and plot each as a separately colored series. Excel does not natively link a chart point’s fill color to a cell formula, so helper series make the color rule update automatically when the underlying values change.

How value-based chart coloring works

A chart normally takes its colors from the chart’s series formatting or theme. A worksheet formula can calculate a number or label, but it cannot directly return a fill color for a chart point. Worksheet conditional formatting is separate too: highlighting a cell red does not automatically make the corresponding bar or marker red.

The helper-column approach gets around that limitation by dividing values into groups. For example, values below 50 can go into a red series, values from 50 through 99 into an amber series, and values of 100 or more into a green series. Each original value appears in only one helper column; the other helper cells return #N/A, which Excel charts do not plot as a data point. You then assign a fixed color to each series.

This gives you a chart that responds to changing values while keeping the color meaning consistent. A value that crosses a threshold moves from one series to another, and its displayed color changes with it.

Build a color-by-threshold column chart

Suppose column A contains product names and column B contains sales. Add three headings in C1:E1: Red, Amber, and Green. Enter the formulas below in row 2, then fill them down through the rows containing data.

With sales values in B2:B6 and a red threshold below 50, enter =IF($B2<50,$B2,NA()) in C2. For the example values 125, 80, 35, 100, and 49, the helper results are #N/A, #N/A, 35, #N/A, and 49.
With sales values in B2:B6 and amber defined as 50 through 99, enter =IF(AND($B2>=50,$B2<100),$B2,NA()) in D2. The example results are #N/A, 80, #N/A, #N/A, and #N/A.
With sales values in B2:B6 and green defined as 100 or more, enter =IF($B2>=100,$B2,NA()) in E2. The example results are 125, #N/A, #N/A, 100, and #N/A.

These rules give 50 to Amber and 100 to Green, avoiding gaps or overlaps at the boundaries. If your thresholds differ, change the comparison signs and numbers to match your definition. Copy each formula down; the dollar sign fixes the source column while allowing the row reference to adjust.

To create the chart, select the category names and the three helper columns, including their headings, and insert a 2-D Stacked Column chart from Insert > Insert Column or Bar Chart. If you select the whole A:E range to make the chart, it will also include the original sales series in B. Remove that series using Chart Design > Select Data, select the original series, and choose Remove.

In the chart, select a colored series, open its formatting pane, and use Fill & Line > Fill > Solid fill to choose its color. Repeat for the other two series. The five categories now show bars colored by their values, rather than by a repeating point-color palette.

Choose a chart type that suits the data

A stacked column chart works well for this example because each category has a value in exactly one helper series. Since the other helper cells return #N/A, the chart draws one segment per category, with the segment’s height equal to the original value. It is not stacking multiple parts of a total; the mutually exclusive helper series are being used to control color.

For horizontal comparisons, use a 2-D Stacked Bar chart with the same helper data. A line chart can also use separate series, but it may draw lines between points within each color group, which can be misleading when the groups alternate. For a line that should stay continuous while individual markers change color, use separate marker-only series or consider a scatter chart; setting that up requires more care because each series needs suitable X and Y values.

Pie and doughnut charts have individual slices, so it is often easy to format a slice manually. They are a poor fit for many threshold-based comparisons, however, and manual slice colors do not become formula-driven just because the worksheet data changes. Use a chart type that communicates the comparison clearly, not just one that makes color changes possible.

Set colors and keep the chart maintainable

Pick colors that preserve their meaning across the workbook. For instance, red for below target, amber for approaching target, and green for meeting target is familiar, but color alone should not carry information that readers must distinguish. Keep the series names meaningful, add a clear chart title, and explain the thresholds in a subtitle, note, or nearby text.

Consider accessibility as well as appearance. Red and green can be difficult to distinguish for some readers, and the same colors may look different when printed in grayscale. Use distinct lightness levels, add data labels when useful, and avoid relying on color as the only way to identify a category. A concise legend such as “Below 50,” “50–99,” and “100 or more” is more informative than generic series names.

If the chart should grow as new records are added, convert the data range to an Excel table with Home > Format as Table or the Ctrl+T shortcut on Windows. Include the source and helper columns, and check that the chart’s data source includes the table columns you intend to use. In particular, remove the original values series if you do not want it plotted. Excel tables generally extend formulas and chart data when rows are added, but verify the chart range if you reorganize the table or add columns.

Other ways to color chart points

For a one-off chart with only a few points, manual formatting may be faster. Select a point once to select its series, then select that point again to select just the data point; use the formatting pane to set its fill. This is a visual override, not a rule. If the data order changes, the custom color may remain attached to a point position rather than follow the intended business category, so check the chart after sorting or editing data.

Some single-series chart types offer a Vary colors by point setting in the data-series fill options. This assigns colors across points using the chart palette; it does not inspect each point’s value or apply thresholds. It is useful when categories need different colors for visual variety, but it is not a substitute for value-based coloring.

Another option is VBA that formats points after recalculation. That can suit a workbook with specialized rules, but it introduces macro security, maintenance, and compatibility considerations. Macros cannot be undone with Ctrl+Z, so save a copy before running one. For ordinary threshold charts, helper series are usually easier to inspect and maintain than code.

Common problems and fixes

All points have the same color: Check that the chart contains the helper series, not just the original values, and set the fill separately for each helper series.
The chart shows extra bars: The original value column may still be plotted alongside the helper columns. Remove it through Chart Design > Select Data.
There are gaps or missing bars: Confirm every possible value belongs to one threshold band. Review the boundary tests, especially whether values equal to a threshold use >= or <.
The legend is unclear: Rename the helper headings to describe the ranges, such as “Under 50,” “50–99,” and “100+.” The legend uses the series headings.
New rows do not appear: Check the chart’s source range. If the source is not expanding with the data, update it in Chart Design > Select Data or use a table-based range.

One common misconception is that returning a color name or a hex color code from a worksheet formula will color a chart point. Such a formula returns text in a cell; chart formatting does not interpret that text as a fill instruction. The helper-series method works because it changes which numeric series contains each value, and each series has its own chart formatting.

Windows, Mac, and Excel for the web

The helper-formula method uses standard worksheet functions and is suitable for Excel for Microsoft 365 on Windows. In Excel for Mac, the same formulas and chart concepts apply, though the ribbon layout and formatting-pane controls can differ slightly. When selecting separate ranges with a keyboard, Windows commonly uses Ctrl and Mac uses Command; selecting a single contiguous range that includes the helper columns avoids that extra step.

Excel for the web can handle many common chart and worksheet edits, but available formatting controls can differ from the desktop application. If a particular fill or chart option is missing, open the workbook in the desktop version of Excel. The chart’s helper formulas remain ordinary worksheet formulas, so the underlying classification logic does not depend on VBA.

Frequently Asked Questions

Can Excel color a chart automatically from conditional formatting?
No. Conditional formatting changes worksheet cell appearance, not the chart point’s formatting. Use helper series for a chart that changes color as values cross defined thresholds.

Can I make a bar turn red when its value is negative?
Yes. Create separate positive and negative helper series, returning the value in the appropriate series and NA() in the other. Format those series with the colors you want, and decide how zero should be handled.

Do helper columns change the original values?
No. They create additional chart-ready values while leaving the source data intact. You can keep the helper columns visible for auditing or hide them after confirming the chart still displays as intended.

Will the colors update when I edit a value?
Yes, if the helper formulas recalculate and the chart uses those helper columns. A value that moves into another threshold band appears in that band’s series color. The series’ chosen fill colors stay fixed unless you change them.

Can I use more than three value bands?
Yes. Add one helper column and one chart series for each band, and define non-overlapping rules that cover the values you expect. Use clear series names so the legend explains the ranges.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

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

Spotted an error in this guide? Let us know.