Creating a Thermometer Graph or Chart Template in Excel 2007
The genesis of my latest Excel 2007 article is a little unusual. I was looking at some of the searches people used to find my other Excel 2007 articles and saw a search for thermometer chart Excel 2007. I had never heard of such a chart and promptly looked it up. Curiosity well and truly tweaked, I decided it would be fun to build my own. Along the way, I learnt not only how to create what I think is a very nice thermometer chart, but also learnt some new Excel 2007 tricks along the way. As Homer said, “the Journey is its own reward”. Our goal is to create a thermometer graph as shown below, so let’s get cracking.
Creating a Thermometer Chart
- Into a cell in your spreadsheet, type in the current total raised.
- Select your cell and then
- Select Column from the Charts group on the Insert tab
- Select 2-D Column and then Stacked Chart
- Excel will create a chart similar to the one shown below
- Select Series1 (the legend) and hit Delete
- Click on the graph so that the Chart Tools tabs appear and select the Axes button from the Axes group on the Layout tab. Select Primary Horizontal Axis and thenNone
- Again on the Layout tab,selectthe Gridlines button in the Axes group. Select Primary Horizontal Gridlines and select None.
- Right click on the blue column in your graph and select Format Data Series
- On the Series Options tab, select a Gap Width of zero
- Click on the Fill tab and select Gradient fill and select Fire from the Preset colours
- Next, select Border Colour and click Solid line. Choose a colour (I used a dark red)
- Click Close to return to our graph or chart
You can see from the figure below that our chart / graph has now changed a lot
- Select the whole graph and resize it to make the column thinner and longer as shown below
- While still selecting the whole graph, right click and select Format Chart Area
- Select the Border Colour tab and selectNo line
The next step is a little fiddly. Select the numbers on the vertical axis so that your graph / chart looks similar to mine below
- Right click and select Format Axis and then on the Line Colour tab, select No line. Keep the dialogue box open for the next step
- Now we are going to adjust the numbers on the vertical axis. Click on Axis Options. Depending on your total you may wish to adjust the Maximum and the Major unit to suit, select the Fixed button and adjust as you see fit. In my case, I changed the Maximum to 1,000,000 and left the others fixed at their default values
- Next, we need to remove the zero on the vertical axis. Once again, select the vertical axis and right click and select Format Axis. Click on the Number tab and select Custom. Type in 0;0; and click Add. Select it under Type and click Close. It should have looked like my screenshot below and the zero should disappear
There is one final change you can make if it suits your purposes. You can have the numbers of your target (the vertical axis) either on the left or the right. To do this,
- Select the graph and on the Chart Tools Layout tab, select Axes and then Primary Horizontal Axis. Select Show Right to Left Axis and the numbers will flip to the right hand side.
Now we are finished with the graph and we will begin to add shapes to make it look like a thermometer. First we will add the circle at the bottom.
- On the Insert tab, select Shapes in the Illustrations group. In the Flowchart group, there is a circle that we will use for the base of the thermometer.
- Place your circle at the base of your column and resize until it looks right.
- Right click and select Format Shape. Select the Fill tab and choose Colour. Next click on Line Colour and select the same colour for the line
Next, we are going to add a rectangle above the column (otherwise all that white space looks a little weird)
- Again on the Insert tab, select Shapes in the Illustrations group. Select a rectangle from Rectangles and draw a rectangle that extends your column upwards towards your goal.
- Right click your rectangle and select Format Shape. Select the Fill tab and choose No fill. Click on Line Colour and use the same colour as you used for the line around your column. Also, click on Line Style and change the Width to 1pt.
- To add check marks to each milestone (in my case every 100,000) click on the Insert tab, select Shapes in the Illustrations group and choose a line in the Lines group.
- Move the line to the correct position and then right click on it and select Format Shape
- Select Line Colour and select Solid line and choose white. Click Line Style and change the Width to .75 pt
- Repeat the above two steps to create as many check marks as you need
- Finally, we will create the frame for the thermometer and remove the gridlines.
- On the Insert tab, select Shapes in the Illustrations group. Select a rounded rectangle from Rectangles and draw a rectangle the covers your thermometer
- Right click your rounded rectangle and select Format Shape. Select the Fill tab and choose No fill. Click on Line Colour and select a colour that suits (I chose Orange)
- Finally turn off gridlines by clicking on the Gridlines checkbox in the Show / Hide group on the Views tab
Now we have finished our thermometer and it should look something like mine below.
I hope you enjoyed creating your thermometer chart or graph and that you also hit your target whatever it happened to be. Along the way, I also hope that you learnt a few new tricks in Excel 2007 as I did while creating mine. If you would like to know more about creating shapes or using graphs in Excel 2007 I have two additional articles, the first is on creating charts and graphs in Excel 2007 and the second is on using shapes in Excel 2007. Please feel free to leave any comments below and thanks again for reading!
More by this Author
Shapes in Excel 2007 and Excel 2010 can be renamed and reordered using the Selection Pane. To save time, shapes can also be grouped so that they can be formatted, manipulated or moved together.
If your data range contains many blank cells, it can be hard to correctly create a chart. Use the IF function to convert blank cells to #N/A which Excel ignores or change how Excel deals with blanks.
Dynamic charts in Excel change automatically if data it is added or removed saving you time and effort. They use a formula with the OFFSET and COUNTA functions and named ranges using Defined Names.
No comments yet.