Displaying conditional colors in a column chart

Excel 2016
There are many ways to emphasize differences between data on the chart. You can add a threshold value using horizontal or vertical lines, highlight differences with a waterfall, individual values, and so on. Also, you can create a column chart in which the color of each column depends on the value displayed.

For example, you want to demonstrate the differences in sales during 2020 - 2022:

Colored column chart in Excel 365

To create a chart like the one above, do the following:

Prepare a new data

   1.   Split your data into different columns:

For example, split values into three columns:

  • Column I - the values less than 7%:

    = IF (<cell> < 7%, <cell>, ""),

  • Column II - the values less than 15%:

    = IF (AND (<cell> >= 7%, <cell> < 15%), <cell>, ""),

  • Column III - the values equal to or more than 15%:

    = IF (<cell> < 15%, "", <cell>), where <cell> = D*:

Colored column chart data in Excel 365

Create a column chart

Excel creates charts from the data with gaps in a strange manner. You need to add or change the data series manually. Follow the next steps to create a perfect chart:

   2.   Select the data cells without gaps (for this example, B3:C16).

On the Insert tab, in the Charts group, click the Insert Bar or Column Chart button:

Insert Bar or Column Chart in Excel 365

Choose Clustered Column:

Clustered Column Charts in Excel 365

Excel creates a chart from that you need to modify:

Simple column chart in Excel 365

Modify the chart data series

   3.   Do one of the following:

  • On the Chart Design tab, in the Data group, choose Select Data:
    Select Data in Excel 365
  • Right-click on the chart area and choose Select Data... in the popup menu:
    Select Data in popup menu Excel 365

In the Select Data Source dialog box, under Legend Entries (Series):

  • Click the Add button:
    Select Data Source dialog box in Excel 365

    In the Edit Series dialog box, type or choose the data for each data series.

    For this example:

    • The first data series for values less than 7% (E4:E16),
      Edit Series dialog box in Excel 365
    • The second data series for values less than 15% (F4:F16),
    • The third data series for values equal to or more than 15% (G4:G16).
  • Select the existing data series (for this example, Sales), and click the Remove button:
    Select Data Source dialog box in Excel 365

After clicking the OK button, Excel modifies the chart:

Colored column chart in Excel 365

Optionally, format the horizontal axis

   4.   Right-click on the horizontal axis and select Format Axis... in the popup menu (or double-click on the axis):

Format Axis popup in Excel 365

On the Format Axis pane, on the Axis Options tab:

You can then make any other adjustments to get the look you need.

See also this tip in French: Affichage des couleurs conditionnelles dans un graphique à colonnes.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.