Quick answer
Excel chart conditional formatting does not exist as a feature. Conditional formatting rules color cells; a chart series ignores them completely, so coloring your source data red does nothing to the bars. To color bars by value you split the data into one helper column per color band using IF and NA, then plot those columns as a stacked chart — each series carries its own color and the bars change automatically. If you only want a bar-like display, Conditional Formatting → Data Bars draws one inside the cells and is genuinely rule-driven.
People arrive at this problem from a reasonable place. You have applied a red-amber-green rule to a column of figures, it looks great, you build a chart from the same column — and every bar comes out the same shade of blue. Nothing is broken. Conditional formatting is a property of the cell display, and chart series read the underlying values, not the formatting applied over them.
So the question becomes: how do you get the effect anyway? There are three real answers and one that only looks like an answer.
Option 1: helper columns, one per color band
This is the method to learn, because it updates on its own and survives someone else editing the workbook.
Say column A holds months, column B holds actual sales, and the target lives in $E$1. Instead of charting column B, split it in two.
In C2, headed Met target:
=IF(B2>=$E$1,B2,NA())In D2, headed Below target:
=IF(B2<$E$1,B2,NA())Fill both down. Every row now has a number in exactly one of the two columns and #N/A in the other.
Now build a stacked column chart from A2:A13 together with C2:D13 — not column B. Colour the “Met target” series green and the “Below target” series red. Because only one series has a real value per month, each month shows a single bar in the right color, and when a number crosses the target the bar changes color by itself.
Why NA() and not “”
Use NA() rather than an empty string. Excel charts plot "" as a zero, which leaves a visible flat mark on the baseline and can distort a stacked total. NA() is treated as “no data here” and plots nothing at all. The price is that the cells display #N/A on the sheet — hide the helper columns, or push them out of sight to the right.
Three or four bands work the same way: one column per band, each IF returning the value or NA(). Nest them so the conditions cannot overlap, or the same value will appear in two series and the stack will double.
Option 2: Data Bars, if you want a bar and not a chart
Often the real requirement is “show me at a glance which numbers are big”, and a chart object is more machinery than that needs.
Select the range, then Home → Conditional Formatting → Data Bars. Excel draws a proportional bar inside each cell. This is real conditional formatting: it is rule-driven, it updates instantly, it needs no helper columns, and it moves with the rows when you sort.
Two settings are worth opening Manage Rules → Edit Rule for. Ticking Show Bar Only hides the numbers and leaves a clean in-cell bar chart. And if your data contains negatives, the Negative Value and Axis button controls where the zero line sits and what color the negative bars take — the default puts the axis in the middle of the cell, which surprises people.
Option 3: color one bar by hand
To highlight a single point — this month, or the outlier you are presenting — click the series once to select all bars, then click the one bar again to select just that point. Now Format Data Point → Fill, and pick your color.
This is fine for a chart going into a slide deck today. It is a trap for a monthly report: the formatting is attached to the data point’s position, not its value, so next month the color stays on the same bar while the number it was highlighting has moved on. Changing the chart style or type can also wipe it.
Comparing the options
| Approach | Updates when values change | Where the logic lives | Best for |
|---|---|---|---|
| Helper columns + stacked chart | Yes | IF formulas on the sheet | Recurring reports, dashboards, anything with thresholds |
| Conditional Formatting → Data Bars | Yes | A conditional formatting rule | Tables where a chart object would be overkill |
| Manually filling one data point | No | Nowhere — it is fixed formatting | A one-off slide |
| REPT() in-cell bars | Yes | A formula, plus a font-color rule | Plain-text reports and very old files |
| VBA looping over points | Only when the macro runs | Code | Rules too complex for helper columns |
The REPT approach is the old trick and still occasionally the neatest: =REPT("|",B2/10) in a narrow column draws a bar out of pipe characters, and a conditional format on that cell changes the font color by value. It prints and pastes anywhere.
If your coloring rule is genuinely too complicated for helper columns — say, color by a category looked up from another sheet — VBA can set .Points(i).Format.Fill.ForeColor in a loop. It only runs when you run it, which is the trade-off. Something like the short VBA routine that borders every used cell shows the general shape of that kind of macro.
Common mistakes
Expecting the chart to inherit the cell formatting. It never will, in any version. This is the single most common cause of the question.
Using a clustered chart instead of a stacked one. With helper columns, a clustered chart reserves a slot for every series in every category, so the bars come out thin with gaps beside them. Stacked puts them in the same slot.
Overlapping conditions. If a value satisfies two of your IF tests it appears in two series and the stacked bar shows their sum. Make the bands mutually exclusive: >= in one, < in the other.
Leaving the helper columns visible. Columns full of #N/A undermine an otherwise good report. Hide the columns, or hide the values with a custom number format and leave the column in place.
Forgetting the chart range. New months added below the data will not appear unless the source is a Table — the same issue covered in adding new data to an existing chart. While you are there, adding total labels to a stacked chart uses the same helper-column idea in a different direction.
Frequently asked questions
Can you apply conditional formatting to an Excel chart?
No. Conditional formatting rules apply to worksheet cells only. A chart series reads the values in those cells and ignores any formatting applied to them, so the bars keep the color set in the chart itself.
How do I make chart bars change color based on their value?
Split the data into one helper column per color band using =IF(condition,B2,NA()), plot those columns as a stacked chart, and give each series its own color. Because only one column has a value per row, each bar shows in the color matching its band, and it changes automatically when the value does.
How do I change the color of just one bar?
Click the series to select every bar, then click the single bar again to select that data point on its own, and set its fill. The color is fixed to that position, so it will not follow the value if the data changes.
What are Data Bars in Excel?
A conditional formatting option under Home → Conditional Formatting → Data Bars that draws a proportional bar inside each cell. Ticking Show Bar Only in the rule settings hides the numbers and leaves an in-cell bar chart that updates with the data.
Why do my helper-column bars look thin and spaced out?
The chart is clustered rather than stacked. A clustered chart gives every series its own slot in each category; switching the chart type to stacked puts them in one slot so each category shows a single full-width bar.
Sources
- Use conditional formatting to highlight information in Excel — Microsoft Support. Accessed September 10, 2026.
- Highlight data with conditional formatting — Microsoft Support. Accessed September 10, 2026.
- Add or remove data labels in a chart — Microsoft Support. Accessed September 10, 2026.
