Creating a rainbow thermometer chart

Creating a thermometer chart you are not limited to a single color bar. Instead you can specify different colors for different value intervals. This tip shows you how to create a rainbow thermometer chart. E.g., working on some goal you can specify risk zones for it: red zone - risk of the fail is high, yellow zone - success/fail is unknown, green zone - success is not far enough.
The thermometer Chart Excel 2016

For example, a worksheet set up to track daily progress towards a goal: 1,000 new sales in a 10-day period. Cell C16 contains the goal value, and cell C17 contains a simple sum formula:


Cell C19 contains a formula that calculates the percent of goal (see Creating a simple thermometer chart):


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

    1.    Add additional data to see different colors for different intervals (in this example, cells C20:C24):

Additional data Excel 2016

    2.    Select cells C20:C24.

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

Column Charts in Excel 2016

Choose 100% Stacked Column: Stacked Column Charts in Excel 2016

    4.    Do one of the following:

  • Right-click in the chart area. In the popup menu select Select Data...:
    Select Data in Excel 2016

    Then in the Select Data Source dialog box, click the Switch Row/Column button:

    Add data series in Excel 2016
  • Under Chart Tools, on the Design tab, in the Data group, click the Switch Row/Column button:
    Data in Excel 2016

    5.    Open the Format Data Series, do one of the following:

  • Right-click in the data series and choose Format Data Series... in the popup menu:
    popup data series in Excel 2016
  • Excel displays the Chart Tools, adding the Design and Format tabs:
    The Chart Tools in Excel 2016

    On the Format tab, in the Current Selection group, click the Format Selection icon:

    Format Selection in Excel 2016

    6.    In the Format Data Series task pane for every series choose different colors. You can choose different series in that task pane:

Choose data series in Excel 2016

Make any other adjustments to get the look you desire.