Displaying conditional colors in a column chart

Excel 2016 2013 2010
To emphasize differences between data in the chart it will be interesting to 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 2017 - 2020:

Colored column chart in Excel 365

To create the chart like this one, do the following:

   1.   Split your data to the different columns:

  • Column I = IF (cell < 7%, cell, ""),
  • Column II = IF (AND (cell >= 7%, cell < 15%), cell, ""),
  • Column III = IF (cell < 15%, "", cell),

For example (here cell = D*):

Colored column chart in Excel 365

   2.   Select the data cells (for this example, B4:B16, E4:G16).

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

Column charts in Excel 365

Choose Clustered Column:

Clustered Column Charts in Excel 365

Change Data Source if necessary:

Column colored chart in Excel 365

   4.   Open Format Axis on the Format task pane, in which:

  • In the Labels group, in the Label Position list, choose Low:
    Axis options in Excel 365
  • In the Number group, choose the format you prefer:
    Add Format Code in Excel 365

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.

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.