Displaying conditional colors in a column chart

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 2012 - 2015:

Colored column chart in Excel 2016

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

    1.    Split you 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 2016

    2.    Select the data cells.

    3.    On the Insert tab, in the Charts group, choose the Column button:

Column charts in Excel 2016

Choose Clustered Column. Change Data Source if necessary.

Column colored chart in Excel 2016

    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 2016
  • In the Number group, choose format you prefer:
    Add Format Code in Excel 2016

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