Pivot Chart Colors by Value in Excel: Setup Guide

Excel PivotCharts do not automatically change each data point’s color according to its value, and worksheet conditional formatting does not control PivotChart colors. For colors that update when values cross thresholds, build a regular chart from helper columns that split the values into separate series; for a one-off change, format individual PivotChart points manually.

What Excel can and cannot color automatically

A PivotChart is linked to a PivotTable, so its categories, series, and plotted values follow the PivotTable layout. Excel lets you format a chart series and individual points, but it does not provide a rule such as “make every bar above 100 green and every bar below 50 red” that dynamically evaluates PivotChart values. Applying a red fill to a worksheet cell with conditional formatting will not make the corresponding chart bar red.

There are three different tasks that are easy to confuse:

Color a whole series: every point in that series gets the same fill or line color. This is useful when series represent regions, products, or years.
Give points different colors: Excel can vary colors across the points in a series, but that is not the same as assigning a color according to a threshold.
Color points by value: a point’s color changes when its value meets a rule. A helper-data chart is the most dependable no-macro way to achieve this in Excel for Microsoft 365 on Windows.

Knowing which result you need helps avoid spending time on chart-formatting controls that cannot express the rule.

Change a PivotChart color manually

If the chart only needs a fixed appearance, you can recolor a series or a single point. Select the PivotChart, then select the series. To select just one bar or column, click the series once and then click the intended data point again. Right-click the selection and choose Format Data Series or Format Data Point, as appropriate. In the formatting pane, use the fill or line controls to choose a color.

For a simple column chart, selecting the series and enabling Vary colors by point assigns different colors to the points. The available control is in the series formatting options; its exact placement can vary with chart type and Excel’s pane layout. This is useful for visual variety, but Excel is not checking whether a bar is above or below a particular number. The point colors follow the chart’s point order and palette, not a value rule.

Manual point formatting also needs care after a PivotTable refresh. If categories are added, removed, or reordered, a color attached to a point may no longer communicate the intended category or status. Check the chart after refreshes, especially if colors carry meaning such as “below target.” Do not rely on a particular point’s formatting as a value-based rule.

Build a value-driven chart with helper columns

For threshold-based colors that recalculate, create a small helper range next to the PivotTable or on another worksheet, then make a regular chart from that range. The helper columns act as separate chart series. Each series returns the original value only when it meets its rule; otherwise it returns NA(), which Excel charts do not plot as a point.

For example, imagine a PivotTable with product names in A2:A20 and sales totals in B2:B20. You want sales of 100 or more to appear green, sales from 50 through 99.99 to appear amber, and sales below 50 to appear red. Set up helper headings such as High, Mid, and Low in C1:E1. With product names in A2:A20, sales in B2:B20, and the first helper row in row 2, enter these formulas:

For the High series, with the sales value in B2: =IF(B2>=100,B2,NA())
For the Mid series, with the sales value in B2: =IF(AND(B2>=50,B2<100),B2,NA())
For the Low series, with the sales value in B2: =IF(B2<50,B2,NA())

These are illustrative thresholds, not built-in Excel rules. Copy the formulas down alongside the categories. A value of 120 appears only in High; a value of 75 appears only in Mid; and a value of 30 appears only in Low. Each value is plotted once, so the columns do not stack or overlap with a duplicate value.

Select the category labels and the three helper series, then choose Insert > Insert Column or Bar Chart and select a clustered column chart. Format the three series separately, assigning green, amber, and red. Because the chart is a standard chart linked to formulas rather than a PivotChart linked directly to the PivotTable, the formulas determine which colored series receives each value.

Keep the helper chart in step with PivotTable changes

The chart updates when its helper formulas recalculate, but the helper range must still line up with the PivotTable. PivotTables can change their row count after filtering or refreshing, so plan for that rather than assuming the original list is permanent.

Put helper formulas outside the PivotTable’s expansion area, ideally on a separate worksheet. Otherwise, a refresh that expands the PivotTable may overwrite nearby formulas.
Allow enough helper rows for the largest expected result and copy formulas down. Keep the chart’s source range aligned with those rows.
Exclude PivotTable subtotals and Grand Total from the chart source if they are not actual categories. They can otherwise appear as extra bars or receive misleading threshold colors.
Check how the PivotTable displays row labels. If labels are blank on repeated rows or the layout changes, ensure the helper category labels still match the values being charted.
After changing filters or refreshing, confirm that newly appearing categories have formulas and are included in the chart’s source range.

For more elaborate workbooks, you can build the helper area with formulas that refer to PivotTable results or use a separate reporting table designed to expand with the output. The important principle is that the regular chart needs a dependable, correctly aligned range; the PivotChart itself cannot use the helper columns as its value-color rules.

Choose thresholds that make the colors meaningful

Before assigning colors, decide what the thresholds mean. Fixed cutoffs such as below 50, 50 to under 100, and 100 or more are straightforward, but they may not suit every report. A sales value of 75 might be good in one department and poor in another. If the intended rule is “below target,” compare each value with its own target rather than using one universal cutoff.

Use a small number of clearly distinct colors and include a legend that explains them. Avoid relying on red and green alone if readers may have difficulty distinguishing those colors; labels, markers, or a separate status column can provide additional context. For negative and positive values, consider whether zero needs its own visual treatment. Be explicit about boundary values: in the example above, 50 belongs to Mid and 100 belongs to High.

If thresholds are likely to change, store them in worksheet cells and refer to those cells in the helper formulas instead of typing each cutoff into every formula. This makes the rule easier to maintain. When editing formulas, keep the category and value references aligned, then check boundary cases such as 49.99, 50, 99.99, and 100.

Why conditional formatting does not color the chart

Conditional formatting changes the appearance of worksheet cells. A chart, including a PivotChart, uses its own formatting for series and points; it does not generally inherit the cell fill color produced by a conditional-formatting rule. This is why changing the PivotTable’s cell colors may leave the chart unchanged.

The same distinction applies to charts based on ordinary ranges: cell fill colors are not a reliable way to control the colors of plotted points. For a standard chart, separate helper series provide explicit chart data and independent series formatting. This is more predictable than expecting the chart to read the displayed colors of the source cells.

PivotChart or regular chart: which should you use?

Keep a PivotChart when the most important feature is interactive PivotTable behavior, such as filtering fields and changing the summarized view. It is usually the simpler choice if consistent series colors are enough, or if the color only needs to be changed manually.

Use a regular chart fed by helper columns when point colors must communicate a changing value rule. The trade-off is extra setup: you must maintain the helper range and confirm it follows the PivotTable’s changing categories. You also give up some of the direct PivotChart behavior because the chart is linked to the helper range rather than functioning as a PivotChart. A separate dashboard can keep the interactive PivotTable and the threshold-colored chart side by side.

In Excel for the web, chart formatting and PivotTable options can differ from the Windows desktop interface, and some editing controls may be limited. If a specific point-formatting control is not available in the browser, open the workbook in Excel for Microsoft 365 on Windows. Excel for Mac has its own ribbon and formatting-pane layout; the same general distinction between PivotChart formatting and a helper-series chart applies, although the exact control placement can differ.

Common problems and fixes

Every bar has the same color: the chart may be formatting one series, not individual values. Use helper series for rule-based colors.
The chart shows extra bars: remove subtotal or Grand Total rows from the chart source, or adjust the PivotTable layout so those rows are not included.
A category has no colored bar: check whether its helper formula returns NA() for all series, whether its value is stored as a number, and whether the row is included in the chart range.
Colors appear to move after refresh: manual point formatting is tied to chart points and may not express a stable category or threshold rule. Recheck it, or use helper series.
The legend names are unclear: use meaningful helper headings such as Below target, Near target, and At or above target, and make sure those headings appear in the chart legend.

Frequently Asked Questions

Can a PivotChart change color automatically when a value crosses a threshold?
No, not through a built-in PivotChart conditional-color rule. Use a regular chart based on helper columns that route values into separately colored series.

Will conditional formatting on the PivotTable change PivotChart colors?
No. Conditional formatting changes worksheet cell appearance; it does not set PivotChart point fills. Format the chart itself or create helper series.

What does Vary colors by point do in Excel?
It assigns different colors to points in a series. It does not test values against thresholds, so it is not a substitute for value-based coloring.

Will a helper-column chart update when I refresh the PivotTable?
It can, provided its formulas refer to the updated values and its chart source includes the relevant rows. Make room for the PivotTable to grow, exclude totals, and check the chart after refreshes.

Can I keep the PivotChart and use a value-colored chart too?
Yes. Keep the PivotChart for interactive analysis and add a regular chart linked to a helper range for threshold-based colors. The two charts can serve different purposes in the same workbook.

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.