How to Make a Thermometer Chart in Microsoft Excel

By Contributor

Updated May 13, 2020

The basic function of Microsoft Excel is to create efficient spreadsheets, but the application also allows users to make charts and graphs easily from the data in those spreadsheets. Excel offers more than a dozen types of instant charts, with even more subtypes, making it simple to create things such as pie charts and column graphs with a few clicks of the mouse. These can be further modified to create a custom chart, such as one that resembles a mercury-filled thermometer.

Open a blank worksheet in Excel. Enter headings for two columns of data, such as "Month" and "Total Clients" in cells A1 and B1. Enter the appropriate data into the column cells. The data should be building toward a goal.

Monthly total clients chart in Excel.

Enter the goal number at the bottom of the column and enter the actual number reached in the cell beneath the goal amount, which will be the number in the last cell.

Monthly number of clients and yearly goal chart in Excel.

Skip a cell below the totals, and enter a formula to determine the percentage reached in the next cell. In the Formula Bar, use the formula total amount/goal amount. For example, the formula could be "=B16/B15." This will come out as a decimal number.

Division formula used in Excel cell.

Right-click the cell with the decimal, select "Format Cells."

Formatting Cells option selected in Excel.

Select the "Number" tab and choose "Percentage." Choose how many decimal places you want to show, such as "2."

Formatting number as percentage in Excel.

Now your cell should display a percentage instead.

Cell with percentage in Excel spreadsheet.

Click the cell with the percentage. On the "Insert" tab, in the "Charts" group, choose the "Column" button and select the "Clustered Column" chart type, which is in the left-side corner.

Inserting Clustered Column into Excel spreadsheet.

This will create a chart with a wide bar. Remove the (x) axis by right-clicking on it and choosing "Delete." This should leave nothing below the bar.

Clustered Column based on Percentage Reached of goal in Excel.

In the thermometer chart, the column width is equal to chart width. To make the column take up the entire width of the plot area, right-click the column to display the "Format Data Series" option. Then in the Series Options tab, change the Gap Width setting: Set the Gap Width to "0."

Formatting Data Series option in Excel.

Then in the "Series Options" tab, change the "Gap Width" to 0%.

Changing gap width in clustered column chart to zero.

Click on your chart. "Chart Design" will appear in the ribbon at the top. Under the "Chart Design" options that appear below it in the ribbon, select "Add Chart Element" on the far left and choose "Axes" and then "More Axis Options."

Adding Chart Element and selecting Axis Options for chart in Excel spreadsheet.

In the popup menu, on the "Format Axis" task pane, in the "Axis Options" tab, set the Minimum value to "0.0" and the Maximum value to "1."

Adjusting Minimum and Maximum values for chart axes in Excel.

Go back to the "Add Chart Element" tab in the top ribbon, select "Data Labels" in the pop-up menu and then select "More Data Label Options."

Selecting

Under the"Label Options" tab on the "Format Data Labels task pane:

  • In the "Label Contains" group, check "Value"
  • In the "Label Position" group, check "Center."
Adjusting label positions for clustered chart in Excel.

To make the bar in your thermometer chart narrower, revisit the "Format Data Series" step from before and adjust the "Gap Width" to the maximum of 500%, or however much to your liking.

Adjusting column width in thermometer chart in Excel.

You can further narrow or elongate your thermometer chart by dragging the corners the same way you would resize an image pasted into Word or a Google doc. You can also drag the thermometer chart to a different spot in the spreadsheet if you need more space.

Newly created thermometer chart in Excel.

You can also change the color of your thermometer chart by going to the "Chart Design" tab in the top ribbon and selecting "Change Colors."

Changing colors of thermometer chart in Excel.

You can even change the style of your thermometer chart by exploring the style options that appear in the ribbon while the "Chart Design" tab is selected.

Changing style of thermometer chart in Excel.
×