Let us learn a simple charting hack to create a thermo-meter chart in excel. This type of charts can be effective in communicating one data point, they can make excellent presentation slide or dashboard widget. What more, they are as simple to do as adding whipped cream to your latte. So lets begin:
1. First we will draw thermometer outline using excel’s drawing tools
This is the simplest part. We will create a thermometer outline by drawing a circle and a rounded rectangle. See the illustration to the right to understand. Next we will fill the circle with our favorite color. Not that excel presents us with may choices, but I choose the light green, the kind that you see on the Starbucks small size cups. Oh btw, learn how to tweak excel chart color limitation to add your own colors.
2. Create a one column bar chart to fit inside thermometer
Now we will create a one column bar to fit snugly inside our thermometer outline (see below illustration). We will start by creating a default bar chart for a single cell containing temperature (or customer experience index or sales actual vs. target % or no. of cats you have), Next we will remove grid lines, plot area backgrounds, x-axis, column borders, now it should look like just a bar. Then we will adjust gap width to 0 (select the bar, right click and goto format data series, click on the options tab and adjust gap width to 0), this will make sure that our one column occupies the entire plot area.
Then we will adjust the scale of y-axis so that whenever the temperature (or the number of cats) changes our bar height changes (instead of excel default behavior of adjusting plot area and thus often retaining the bar heights). Now we will remove the y-axis as well. Finally, change the bar color to light yellow, remove chart area fill color, border. That is all, we now have a shiny little bar that changes its height when you change the cell containing temperature.
3. Finally fit the chart inside the thermometer outline
This is simple drag and drop game where in we will drag our chart and drop it inside our thermometer outline created in step1. And we are done. Go ahead, celebrate, show it off, print shiny little thermometers on a paper and hang it in your cubicle.
If you have difficulty creating or understanding this trick download thermometer chart templates I have created and play with it.
Like this? Also learn how to create artistic grid charts as an alternative to pie charts, beautify your charts with these 73 designer quality templates, put together in-cell pie charts, bar charts and much more.
25 Responses to “Hot Thermometer Charts in Excel – here is a cool way to do them”
Hey there is an error in template u have uploaded. I tried doing the same thing, even I got the same error. It is like, the thermometer works fine till the number 83. When u type the number 84, the bar displays wrong. You can try it yourself.
@KD.. welcome to PHD... thanks alot for pointing out the mistakes. I forgot to set the minimum scale of y-axis to 0, and excel defaulted it to 75 (why, god, why?). I have updated the excel, download it now.. should work properly 🙂
Thanks! Works fine now.
great tip man. You rock!!
[...] Original post by Chandoo [...]
[...] this? Also try: Thermometer charts in Excel, Micro bar charts, Gantt charts with excel bar graphs Tags: charting, Excel Tips, how to, [...]
[...] try: Min-max charts to tell the story better, Incell graphs with colors, hot looking thermometer chart Do you like this post? Consider subscribing to my newsletter, every weekday I will send you [...]
[...] try: Thermometer chart, Partition chart, Chart around the clock, Min-max charts Categories : Excel Tips | hacks [...]
[...] Impressive Excel Charts you should try - Thermometer Charts, Min-Max Charts, Incell Tag [...]
[...] to learn more? Show temperature using these thermometer charts, Get stock quotes to your workbook with one click, Master your ifs and buts - learn these 6 tips on [...]
Excuse me, but I don't speak English.
Therefore, the example is in Portuguese from Brazil.
Todo em fórmulas, bastando apenas mudar os dados das linhas 2, 5, 8, 11, 14 e 17, ou as informações das células K9 ou N10.
Conforme o gráfico que foi apresentado no blog favillae.blogspot.com, acrescentando apenas as fórmulas para se colocar uma linha de média, que se adapta automaticamente.
http://www.sendspace.com/file/bi06cr
[...] Thermo-meter chart to show the project progress against 100% total [...]
[...] when you have a bunch of sales targets and you want to measure how the performance has been. [Read excel theremometer charts for another option.] To make a thermometer chart, just follow these 5 simple [...]
What is the application used to create the guage charts?
@Pam.. you can create gauges in Excel as well. Try this: http://chandoo.org/wp/2009/11/06/fancy-gauge-chart/
Hello:
Excelent site!!!!!
I am trying to do a thermometer chart with a bar graph, with 2 series overlapped, the target and the real value. How can I do for change the series color of the real value to green, red or blue automatically, due to the real value is under, on or above de target?
Thanks.
Great tip on this chart. I used a stackable bar to show multiple goals. Why draw a thermometer? It seems like just drawing a circle at the bottom of the bar chart works well enough. That way there aren't as many moving parts.
Thank you for the tip on thermometers - like others who are using these steps, it is very helpful. My question is this: is it possible to be able to have different scales (one on each side) of the thermometer? For example, percentage on the left and a value / number on the right? If so, can you describe how to do so? If it helps, I am trying to put Sales $ on the left and the number of new customers on the right. Thank you.
Hi
Thanks a lot for this. I had a question. Is it possible to change the color of the thermomter with Red amber Green trigger?
in a related question, can we have conditional format on excel charts?
Thank you for showing me how to make the thermometer! The drawing tip worked really well, I was able to have the circle overlap the rounded rectangle
@Cory, Arpan
You may also want to have a look at: http://chandoo.org/wp/2011/04/13/how-to-make-a-5-star-chart/
How can we set the color in such a manner that at bottom it remains light but as the value increase the color also become darker?
Chandoo, Hui or Luke please help if you have any idea or code for it!
Cheers!
PSG
Thanks. Great apps.
Thank you. i have trying it since yesterday how to overlap two charts.
Hari,
Just pick&place the bar-chart on the shape. The Thermometer shaped oval+rectangular is inserted as "shape", it is not a chart. In case it is remianing in the front, you can right click it & move to the back.