Hot Thermometer Charts in Excel – here is a cool way to do them

Posted on June 26th, 2008 in Charts and Graphs , Featured , hacks , ideas , Learn Excel , technology - 25 comments

Create a thermo-meter chart in microsoft excelLet 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

drawing-thermo-meter-outline1This 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.

one-column-bar-chart-steps-to-create

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.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

25 Responses to “Hot Thermometer Charts in Excel – here is a cool way to do them”

  1. KD says:

    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.

  2. Chandoo says:

    @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 :)

  3. KD says:

    Thanks! Works fine now.

  4. great tip man. You rock!!

  5. […] this? Also try: Thermometer charts in Excel, Micro bar charts, Gantt charts with excel bar graphs Tags: charting, Excel Tips, how to, […]

  6. […] 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 […]

  7. […] try: Thermometer chart, Partition chart, Chart around the clock, Min-max charts Categories : Excel Tips | hacks […]

  8. […] Impressive Excel Charts you should try – Thermometer Charts, Min-Max Charts, Incell Tag […]

  9. […] 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 […]

  10. Paulo Cesar Semblano da Costa says:

    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

  11. […] 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 […]

  12. Pam says:

    What is the application used to create the guage charts?

  13. Chandoo says:

    @Pam.. you can create gauges in Excel as well. Try this: http://chandoo.org/wp/2009/11/06/fancy-gauge-chart/

  14. MV says:

    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.

  15. brices11 says:

    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.

  16. Jerry says:

    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.

  17. Arpan says:

    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?

  18. Cory says:

    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

  19. 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

  20. Romboy says:

    Thanks. Great apps.

  21. Hari says:

    Thank you. i have trying it since yesterday how to overlap two charts.

    • Kartik says:

      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.

Leave a Reply