Creating a twin or double thermometer chart

Excel 2016
The thermometer chart (also known as a thermometer goal chart or progress chart) displays the percentage of a completed task. See also how to create a simple and glossy thermometer chart. But sometimes, displaying two results in one chart will be more informative and impressive. Below is an example of creating a thermometer chart with two competitive columns:
Twin or double thermometer chart Excel 365

For example, the spreadsheet contains data from two teams to track monthly progress towards the Goal of 1000 new sales during the year:

Twin or double thermometer chart data in Excel 365

Cells C15 and D15 contain the goal value, and cells C17 and D17 contain a simple sum formula for each team:

C17: = SUM (C3:C14) and D17: = SUM (D3:D14)

Cells C19 and D19 contain a formula that calculates the percent of the Goal for each team:

C19: = C17 / C15 and D19: = D17 / D15.

The formulas display the current results when you enter new data in columns C or D.

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

Create a simple column chart

   1.   Select cells C19 and D19 if you want to create a percentage axis, or select cells C15, C17, and D17 if you want to create a volume axis.

   2.   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 simple column chart:

The clustered column chart in Excel 365

Format the chart

   3.   Remove unnecessary chart elements such as the horizontal (Category) axis, gridlines, legend, and chart title.

To remove any of these chart elements, select it and click the Delete key. See how to select chart elements for more details.

Modify the chart data

   4.   Right-click on any data series and choose Select Data... in the popup menu:

Select Data in popup menu Excel 365

In the Select Data Source dialog box:

  • For the first variant with the percentage values:
    • Switch the data if needed by clicking the Switch Row/Column button:
      Select Data Source in Excel 365
    • Add a new data series for the Goal if you want to create a percentage axis (for example, with the fixed value = 100%):
      • Under Legend Entries (Series), click the Add button,
      • In the Edit Series dialog box, in the Series values field:
        • Select the appropriate cell or
        • Type the fixed value (leave the proposed value = {1} for the 100%):
    Edit Series dialog box in Excel 365
  • Reorganize data series to have a Goal between the other data series by clicking the Move Up and Move Down buttons:
    Move Up and Move Down in Select Data Source Excel 365

Add the secondary vertical axis

   5.   To make marks for the primary and secondary vertical axes, do one of the following:

  • Right-click on any chart data series and select Change Series Chart Type... in the popup menu:
    Change Series Chart Type in popup menu Excel 365

    In the Change Chart Type dialog box, select the Secondary Axis for the Series2:

    Change Chart Type in Excel 365
  • Right-click on the Series2 data series and choose Format Data Series... in the popup menu:
    Format data series in popup menu Excel 365

    On the Format Data Series pane, on the Series Options tab, under Plot Series On, select the Secondary Axis option:

    Secondary axis in Format Data Series Excel 365

Format vertical axes

   6.   Right-click on the primary vertical axis and choose Format Axis... in the popup menu (or double-click the axis):

Format Axis popup in Excel 365

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

  • In the Axis Options group:
    • In the Minimum field, type 0,
    • In the Maximum field, type:
      • 1 as a 100% if you use a percentage axis,
      • The Goal value if you use a volume axis (for this example, 1000):
    Format Axis in Excel 365
  • In the Tick Marks group, select the Inside option in both Major type and Minor type dropdown lists:
    Tick Marks type in Excel 365

Make other adjustments, such as changing the line and label color and width. You can also hide the zero point in the axis (see How to hide points on the chart axis).

Repeat step 6 customization for the secondary vertical axis to make them symmetrical.

Format chart data series

   7.   To make columns occupy the entire width of the plot area, right-click on any data series but the Goal, and choose Format Data Series... in the popup menu:

Format data series in popup menu Excel 365

On the Format Data Series pane, on the Series Options tab, change the Series Overlap and Gap Width setting:

Gap width for chart in Excel 365

For this example, set both to 0.

Add shapes

   8.   On the Insert tab, in the Illustrations group, select Shapes:

Shape in Excel 365

On the Shapes list, in the Basic Shapes group, choose the shapes that you want to add to the chart. Format shapes, and insert the data label if you want (see How to insert cell content to the shape).

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

See also this tip in French: Comment créer un graphique de thermomètre double.

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.