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

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

One Response to “How to compare two Excel sheets using VLOOKUP? [FREE Template]”

  1. Danny says:

    Maybe I missed it, but this method doesn't include data from James that isn't contained in Sara's data.

    I added a new sheet, and named the ranges for Sara and James.

    Maybe something like:
    B2: =SORT(UNIQUE(VSTACK(SaraCust, JamesCust)))
    C2: =XLOOKUP(B2#,SaraCust,SaraPaid,"Missing")
    D2: =XLOOKUP(B2#,JamesCust, JamesPaid,"Missing")
    E2: =IF(ISERROR(C2#+D2#),"Missing",IF(C2#=D2#,"Yes","No"))

    Then we can still do similar conditional formatting. But this will pull in data missing from Sara's sheet as well.

Leave a Reply