How to Make a Thermometer Chart in Microsoft Excelby ContributorUpdated 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.
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.
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.
Right-click the cell with the decimal, select "Format Cells."
Select the "Number" tab and choose "Percentage." Choose how many decimal places you want to show, such as "2."
Now your cell should display a percentage instead.
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.
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.
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."
Then in the "Series Options" tab, change the "Gap Width" to 0%.
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."
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."
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."
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."
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.
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.
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."
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.