Excel Chart Bars: Conditional Formatting Methods

Charts & pivot tables · Updated November 19, 2026

Excel does not apply worksheet conditional formatting directly to chart bars. To color bars according to a rule, split the values into helper series with formulas, then plot those series in a stacked bar or column chart; the chart colors update as the values or rule change.

What conditional formatting does—and does not do

Conditional formatting changes how cells appear on a worksheet. For example, Home > Conditional Formatting > Data Bars can draw colored bars inside cells, and Highlight Cells Rules can change a cell’s fill when its value meets a condition. A chart is a separate object, however: its data points have chart formatting, and worksheet cell fills are not a set of instructions that recolor the corresponding chart bars.

This explains a common surprise: formatting the source cells red does not make the bars in a chart red. The chart reads the underlying values, but it does not generally copy the cells’ conditional-formatting appearance. Nor is there a standard chart setting that says, for example, “use orange for every point above 100.” The practical formula-based method is to divide the plotted values into separate series and assign a color to each series.

The technique works well for charts where each category should show one bar in one of several colors. It is also dynamic: when a value crosses a threshold, a formula moves it from one helper series to another, and the chart reflects that change.

Set up helper columns for a threshold rule

Suppose A2:A5 contains product names, B2:B5 contains sales, and E1 contains a threshold of 100. The example values in B2:B5 are 80, 120, 95, and 150. The goal is to show values below 100 in one color and values of 100 or more in another. Add two helper columns: C for Below target and D for At or above target. The formulas return the original value in the matching column and NA() in the other.

With product names in A2:A5, sales in B2:B5, and the threshold in E1, enter this formula in C2 for the Below target series:

=IF($B2<$E$1,$B2,NA())

With the same cell layout, enter this formula in D2 for the At or above target series:

=IF($B2>=$E$1,$B2,NA())

Copy both formulas down through row 5. For the sample sales, the Below target series returns 80, #N/A, 95, #N/A, while the At or above target series returns #N/A, 120, #N/A, 150. These results keep the original magnitudes: a bar for 150 remains longer than a bar for 120. NA() produces an error value that Excel charts do not plot as a data point, so only the matching series supplies a bar for each product.

Use absolute references for the threshold, $E$1, so it stays fixed as you fill the formulas down. The row number in $B2 is deliberately relative, allowing the reference to change to B3, B4, and B5. Change E1 to adjust the cutoff without editing the formulas.

Build and format the chart

For a vertical chart, select the category labels in A1:A5 and the two helper series in C1:D5, including their headings. Because the ranges are not adjacent, select the first range, then hold Ctrl while selecting the second range on Windows. On a Mac, use Command when selecting a separate range. If selecting nonadjacent ranges is inconvenient, create a compact chart-data table elsewhere with category labels and the two helper series side by side, then chart that table.

  1. With the chart data selected, choose Insert > Insert Column or Bar Chart > 2-D Stacked Column.
  2. For horizontal bars instead, choose Insert > Insert Column or Bar Chart > 2-D Stacked Bar.
  3. Click a bar belonging to the Below target series, then use Chart Format or the Format Data Series pane to set its fill color. Set a different fill for At or above target.
  4. Add a chart title and, if useful, keep the legend so viewers can interpret the two colors.

A stacked chart is important here. Each category has a numeric value in only one of the two helper series, so the two series stack to make one bar with the original value. A clustered chart would place the series in separate positions, which can make it look as if each category has two possible bars or leave visual gaps. If the chart assigns the categories and series in the wrong direction, use Chart Design > Switch Row/Column.

Keep the unformatted source value column in the worksheet for reference, but do not add it as another chart series unless you intend to display it. When the values or threshold change, the helper formulas recalculate and the chart updates; the series colors remain attached to the series names.

Use three or more color bands

The same design can represent several ranges. For example, a project-status chart might use one series for values below 50, another for 50 through 99, and a third for 100 or more. Give each range its own helper column and formula, then use a stacked chart and assign one fill color to each series.

With the score in B2 and the cutoff in E1, one possible formula for the below-50 band is =IF($B2<50,$B2,NA()). With the same layout, a formula for the 50-to-99 band is =IF(AND($B2>=50,$B2<100),$B2,NA()). For the 100-and-over band, use =IF($B2>=100,$B2,NA()). Copy each formula down the rows of data. The ranges do not overlap, and together they cover values from 50 upward as well as those below 50. If blanks, text, or negative numbers are possible, decide how those should be treated and adjust the formulas to match the meaning of the chart.

For a rule based on another column—such as coloring each bar by whether its status is Complete—make each helper formula test that status rather than a numeric threshold. The key is still to return the plotted value in the series whose condition is true and NA() in the others. Keep conditions mutually exclusive if each category should have only one colored bar.

Important limits and chart choices

Helper series are a useful workaround, not a universal conditional-formatting engine. They work especially naturally for column and bar charts with one value per category. For line charts, stacked charts may change the meaning of the data, and splitting a line into series can introduce gaps or change how segments connect. Consider whether the resulting chart still communicates the data accurately before applying the technique to a different chart type.

Negative values also need planning. In a stacked chart, positive and negative values are handled on opposite sides of the axis, but a rule based only on a positive threshold may not describe how negative results should be colored. Add explicit negative-value bands if that is part of the intended message, and check the chart with values on both sides of zero.

There is another distinction worth keeping clear: a worksheet Data Bar rule can make cells look like bars, but it does not create a chart. Conversely, chart data labels can show values, but labels alone do not apply conditional colors. If the only goal is to make numbers easier to scan in a table, conditional-formatting Data Bars may be simpler. If the goal is a chart with a legend, axes, and categories, use chart series.

Alternatives and when to use them

For a small, fixed chart, you can select individual data points and format them manually. This is quick when the colors will not need to respond to changing values, but it is labor-intensive for many categories and does not reliably enforce a rule as the data changes. For a chart that must update automatically, helper columns are usually easier to inspect and maintain.

VBA can change individual point formatting in response to workbook events, but it adds macro security and maintenance considerations and is unnecessary for many threshold charts. A formula-driven chart is often preferable when the rule can be expressed in worksheet formulas. If you need a highly customized dashboard, consider whether a PivotChart, a separate series design, or another reporting tool better fits the data; those approaches have their own setup and behavior.

Common problems and fixes

  • Every bar has the same color: Confirm that you formatted each series, not the whole chart or plot area. Select a bar once to select its series; selecting the same bar again may select only an individual point.
  • Two bars appear for each category: Check that the chart type is Stacked Column or Stacked Bar rather than Clustered Column or Clustered Bar.
  • Categories or legend entries look reversed: Choose Chart Design > Switch Row/Column, and verify that the category labels come from column A and the helper headings identify the series.
  • Bars disappear or show gaps: Check the formulas and confirm that each row meets exactly one condition. A #N/A result is intentional in the nonmatching series; an error in both series means neither formula is returning the value.
  • The bars do not change after editing data: Verify that the formulas were filled through all rows and that the chart includes those helper cells. Also check that Excel calculation is not set to Manual under Formulas > Calculation Options.
  • New rows are missing: Extend the formula ranges and chart source, or keep the source data in an Excel table and make sure the chart uses the expanded data range.

When diagnosing the result, inspect the helper cells before changing chart formatting. If the formulas return the expected values and #N/A results, the issue is more likely to be the chart range, chart type, or series formatting than the threshold logic.

Frequently Asked Questions

Can Excel conditionally format chart bars directly?

No. Worksheet conditional formatting does not directly control chart-point colors. Use helper series and a stacked chart for a formula-driven result, or format points manually when the colors are fixed.

Will the bar color update when a value crosses the threshold?

Yes, if the helper formulas refer to the value and threshold cells and the chart includes those helper columns. The value moves to the other series when the condition changes, so the bar takes that series’ assigned color.

Can I use a different color for each bar?

Yes. You can format individual points manually, or create a separate series for each rule or category group. A separate series for every point can become cumbersome, so it is best reserved for charts with a small, stable number of categories.

Why does the chart show #N/A in the worksheet but not as a bar?

NA() deliberately supplies a not-available error for the series that should not plot that category. Excel charts normally omit that point, while the worksheet displays the error so you can see which series is inactive.

Can I use worksheet Data Bars instead of a chart?

Yes, if you want an in-cell visual alongside the numbers rather than a separate chart. Select the value cells and choose Home > Conditional Formatting > Data Bars; those cell bars do not become chart bars.

Related

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.