report

Creating a Thermometer Graph or Chart Template in Excel 2007

Introduction

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.

Completed thermometer chart or graph created in Excel 2007.
Completed thermometer chart or graph created in Excel 2007. | Source

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

The beginning of the thermometer chart a 2-D stacked column chart.
The beginning of the thermometer chart a 2-D stacked column chart. | Source
  • 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

The thermometer chart begins to take shape
The thermometer chart begins to take shape | Source
  • Select the whole graph and resize it to make the column thinner and longer as shown below

Chart becomes more thermometer shaped.
Chart becomes more thermometer shaped. | Source
  • 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

How to select the vertical axis in Word 2007.
How to select the vertical axis in Word 2007. | Source
  • 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

How to remove the zero from the vertical axis in Excel 2007.
How to remove the zero from the vertical axis in Excel 2007. | Source

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.

The finished thermometer chart created using Word 2007.
The finished thermometer chart created using Word 2007. | Source

Conclusion

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


Comments

No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article