Excel Bar Graph Conditional Formatting: Color by Rules

Excel does not apply worksheet conditional formatting rules directly to the bars in a chart. To make bar colors change automatically with values, split the chart data into helper series using formulas, then assign each series a color; the chart updates as the underlying values change.

What conditional formatting can—and cannot—do

Conditional formatting changes the appearance of cells in a worksheet, such as filling a cell red when its value is below a target. A chart is a separate object: it draws bars from its source data, but it does not automatically inherit those cell fill colors. Applying conditional formatting to the source cells therefore will not, by itself, make corresponding chart bars change color.

Excel does let you format a chart series or an individual data point. You can select one bar and set its fill color, but that choice is ordinarily manual and stays attached to that point rather than responding to a rule. The useful distinction is between conditional formatting on cells and conditional coloring in a chart. For automatic chart colors, the common no-macro method is to make each rule its own chart series.

This approach works for bar and column charts in Excel for Microsoft 365 on Windows. It is also useful when you want the chart to communicate a threshold consistently, such as highlighting sales above quota or negative results in a contrasting color.

How the helper-series method works

A chart can assign a different fill color to each series. Helper formulas put each value into the series that represents its rule and return #N/A for the other series. Excel charts do not plot those #N/A results as ordinary values, so a category’s bar appears in the series—and color—that matches its value.

For example, imagine a sales table with product names in A2:A6 and sales in B2:B6. A target of 100 separates results into “At least target” and “Below target.” Add those headings in C1 and D1. With product names in A2:A6, sales in B2:B6, and the target fixed at 100, enter this in C2: =IF(AND(ISNUMBER(B2),B2>=100),B2,NA()). If B2 is 120, C2 returns 120; if B2 is 80, it returns #N/A.

With the same layout and target, enter this in D2: =IF(AND(ISNUMBER(B2),B2<100),B2,NA()). If B2 is 120, D2 returns #N/A; if B2 is 80, it returns 80. Together, the formulas preserve the original sales amount in exactly one helper series for each numeric row. Copy both formulas down through row 6. The paired results for sales of 120 and 80 are respectively (120, #N/A) and (#N/A, 80), so neither value is lost or counted twice.

The use of ISNUMBER prevents blank cells or text entries from being treated as valid sales. If your rule is based on a different cutoff, replace 100 in both formulas with the appropriate number, or refer to a cell containing the target so you can change it without editing the formulas.

Create and color the bar chart

After filling the helper formulas down, make a chart from the category labels and the two helper columns. In Windows Excel for Microsoft 365, you can select A1:A6, hold Ctrl while selecting C1:D6, then choose Insert > Insert Column or Bar Chart > 2-D Bar > Stacked Bar. If Excel does not interpret the nonadjacent selection as intended, insert a chart and use Chart Design > Select Data to add the two helper columns as series and set A2:A6 as the category-axis labels.

Use a stacked bar chart because each category has a value in only one of the two helper series. The bars therefore occupy the same category position, rather than appearing as two side-by-side bars. Select each series in the chart, open Format Data Series, and choose a fill color under Fill & Line > Fill > Solid fill. For example, use green for “At least target” and orange for “Below target.” Rename the series headings in C1 and D1 to make the legend meaningful.

A practical sequence is:

Keep the original values and category labels unchanged, and add helper columns beside them.
Copy the rule formulas through every data row, checking that each formula refers to that row’s source value.
Build a stacked bar chart from the labels and helper series.
Set one fill color per series and confirm the legend explains the colors.
Change a sample source value across the cutoff and verify that its color moves to the other series.

Use Stacked Column instead if you want vertical bars. The same helper-series logic applies; the chart orientation changes, not the conditional-color method.

Use more than two color rules

Several bands can be represented by several helper series. For instance, suppose the same source values are in B2:B6 and you want three bands: under 50, from 50 to under 100, and 100 or more. Add headings such as “Under 50,” “50 to 99,” and “100 or more,” then use one formula per band. With that layout, enter in C2: =IF(AND(ISNUMBER(B2),B2<50),B2,NA()). A value of 40 returns 40 in this series; 50 and 120 return #N/A.

With the same B2:B6 source layout, enter in D2: =IF(AND(ISNUMBER(B2),B2>=50,B2<100),B2,NA()). A value of 50 or 99 returns that value; 40 and 100 return #N/A. With the same layout, enter in E2: =IF(AND(ISNUMBER(B2),B2>=100),B2,NA()). A value of 100 or 120 returns that value; 40 and 50 return #N/A. These conditions use non-overlapping boundaries: 50 belongs to the middle band, and 100 belongs to the top band. Copy each formula down, add all three helper columns as chart series, and choose a distinct fill for each.

For a threshold held in a worksheet cell, a cell reference is easier to maintain than a hard-coded number. For example, if the cutoff is in F1, the two-rule formulas can compare B2 with $F$1 instead of 100. The dollar signs keep the reference fixed as you copy the formulas down. Ensure the target cell contains a number and document what the cutoff means so another person can interpret the chart.

Choose the right chart and color approach

A stacked bar chart is usually the clearest choice when the categories are labels and the main purpose is to show each value’s size and status. A stacked column chart is a reasonable alternative when vertical orientation fits the page or the surrounding dashboard. For a chart with many categories, limit the number of rule bands: a large legend and many similar colors can make the status harder to read.

Excel also offers Vary colors by point for some chart types, available through the data-series formatting options. That setting gives individual points different colors from the chart’s palette; it is not a conditional-formatting rule engine and does not assign a chosen color according to a threshold. It can be useful for simple visual variety, but it is not a substitute for helper series when colors must mean “over target,” “under target,” or another defined status.

Manual point formatting is suitable when a single exception needs to stand out and will not need to update automatically. For repeatable rules, helper series are generally easier to audit than manually recoloring bars. VBA can also set chart-point colors, but it adds code and maintenance, and a macro-enabled workbook may be restricted by an organization’s security policy. It is unnecessary for most threshold charts.

Common problems and fixes

The chart shows two bars per category. Check that the chart type is stacked, not clustered. In a clustered chart, helper series are displayed side by side.
A category has no visible bar. Check that its source value is numeric and that at least one helper formula returns that value rather than #N/A. A true zero has no bar length, even though it is a valid numeric result.
Every bar has the same color. The chart may still contain only the original value column. Use the helper columns as the chart series and format each series separately.
The legend shows generic names. Set the helper-column headings before adding the series, or edit the series names through Chart Design > Select Data.
The bars change but the colors do not. Confirm that the formulas are copied through all rows and that the series use fixed fill colors. Also check that the formulas point to the intended source column and threshold.
Negative values look unexpected. Stacked charts can behave differently when series contain positive and negative values. Test the chart with representative negative data, and consider separating positive and negative rules or choosing a chart design that makes the sign clear.

Also check whether chart labels, axis bounds, and number formats still make sense after the change. The helper columns are for plotting; the original value column remains useful for data labels, calculations, and review. If you want labels to show the original values rather than helper results, select the chart labels and configure their source carefully. Do not replace the underlying sales or measurement data merely to obtain colored bars.

Excel for Mac and Excel for the web

The helper-series approach is based on worksheet formulas and standard chart series, so the workbook can be opened in Excel for Mac and Excel for the web. Ribbon names and the placement of chart-formatting controls can differ between platforms and builds. In the web app, chart editing capabilities may also be more limited than in the Windows desktop application; if a formatting control is missing, open the workbook in desktop Excel rather than assuming the chart cannot use helper series.

On Mac, use the chart and series formatting controls available in that version to change the fill. Windows keyboard shortcuts such as Ctrl-selection are not the same on Mac; use Command where the Mac interface calls for a modifier key, or select the chart data through its Select Data controls. The formulas themselves use the same comma-separated syntax in the stated Microsoft 365 setup, although regional settings can affect list separators in some installations.

Frequently Asked Questions

Can Excel conditional formatting change bar chart colors automatically?
Not by directly applying a worksheet conditional-formatting rule to chart bars. Create helper series that separate values by rule, then assign a chart color to each series.

Can I make bars above a target green and bars below it red?
Yes. Create one helper series for values at or above the target and another for values below it, using formulas that return the source value for a matching rule and #N/A otherwise. Plot them as a stacked bar chart and color the series green and red.

Will the colors update when I change the data?
Yes, if the helper formulas cover the changed rows and the chart includes those helper ranges. The formulas recalculate, moving a value to the series that matches its new status. A manually colored point does not provide the same rule-based updating.

Why does my chart show blank bars or gaps?
One cause is that all relevant helper formulas return #N/A, often because the source is blank, text, or outside the defined bands. A zero is another special case: it is numeric, but its bar has no length. Inspect the source cell and each helper result.

Can a chart use more than two conditional colors?
Yes. Add one helper series for each non-overlapping rule band, assign each series a different fill, and use a stacked chart. Clear boundaries matter; for example, define whether a value exactly equal to a cutoff belongs to the lower or upper band.

Does “Vary colors by point” apply threshold rules?
No. It varies point colors using the chart palette, but it does not evaluate worksheet conditions and map each condition to a specified color. Use helper series for colors with defined meanings.

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.