Creating a twin or double thermometer chart

"Thermometer" chart display the percentage of a task that's completed. How to create simple thermometer chart, see Creating a simple thermometer chart and Creating a glossy thermometer chart. But sometimes it will be more informative to display two results in one chart. This tip is how to create a thermometer chart with two competitive columns.
Twin or double thermometer chart Excel 2010

For example, the worksheet contains data from two teams to track monthly progress towards the goal: 1,000 new sales during the year. Cells B17 and C17 contains the goal value, and cells B18 and C18 contains a simple sum formula:

Twin or double thermometer chart data in Excel 2010

B18: =SUM(B4:B15) and C18: =SUM(C4:C15)

Cells B20 and C20 contains a formula that calculates the percent of goal:

B20: =B18/B17 and C20: =C18/C17

As you enter new data in column B or C, the formulas display the current results.

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

    1.    Select data range B20:C20 if you want to create an percentage axis or select cells B17,B18:C18 if you want to create an volume axis.

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

Charts in Excel 2010

Choose Clustered Column.

    3.    Remove the (x) axis, gridlines and the legend.

    4.    To sort the columns in a different order, follow next steps:

    4.1.    Right-click in the chart area and select Select Data... in the popup menu.

popup in Excel 2010

    4.2.    In the Select Data Source dialog box, select the data series and click Edit:

Select Data Source in Excel 2010

    4.3.    In the Select Data Source dialog box, select the data series in the order like you want, separating each with comma and click OK:

Edit Series in Excel 2010

    5.    To make columns occupy the entire width of the plot area, right-click the column and choose Format Data Series... in the popup menu. In the Format Data Series dialog box, on the Series Options tab, change the Gap Width setting:

Gap in Excel 2010

Set the Gap width to 0 and click OK.

    6.    In the popup menu of axis choose Format Axis.... In the Format Axis dialog box, on the Axis Options tab:

  • Set the Minimum to 0 and the Maximum to 1 (in you use an persentage axis) or to 1000 (for this example)
  • Select the Inside option in Major tick mark type and in Minor tick mark type.
Format Axis in Excel 2010

You can also hide the zero point in the axis (see How to hide points on the chart axis).

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

AutoShapes in Excel 2010

    8.    On the Shapes list, in the Basic Shapes group, choose the shapes that you want to add in your 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.

Thank you for visiting OfficeToolTips

We are glad to help you in your work. However, you are using ad blocker and our efforts will not be rewarded.

Please consider disabling ad blocker before continuing the reading.