Excel Gauge Chart

Here is the famous gauge chart. In this tutorial, I’m going to show you how to make an Excel gauge chart also further customizing it for some fancy looks.

Like self filling chart and loading bar chart, gauge chart is too a thermometer chart. Thermometer charts have one series that has only one value. As value increases or decreases, chart series (bar, column, etc) will increase or decrease as well. So thermometer charts are consists of some simple logic and creativity.

Lets get to our Excel gauge chart:

We will  need a pie chart that has 3 data series for this gauge chart. Here is our setup:

Our chart data here is cells B5:C7

•  C5 has a formula that gets half of Sales% value. Min function is to ensure that picked value never exceeds 100%.
• C6 has a formula that gets remaining part of 50%
• C7 has a static 50% value typed in. This series will be hidden because we need a half circle for our gauge chart.

Now the setup is done, select chart range (B5:C7) and insert a pie chart. Right click on the 50% piece and format series:

1. Set slice rotation to ensure it becomes the lower half ( rotation value was 270 for me).
2. Set fill to “No Fill”.

Your chart should be looking like this by now:

At this point we achieved basic gauge chart. As sales% increases, blue slice will get bigger and red will get smaller. You can play with sales values to see how chart works. All we need is formatting from now on.

Notice: Gauge chart looks nice but it is not very useful. You can present same results much easily by using column or bar charts. Formatting for a fancy gauge chart is (a little) troublesome and finished product will be “hard to move around”.

Now fancy formatting part:

First move the title below upper half and change it to read “Sales %:”. Go to any cell (lets say E5) and input this formula: =MIN(D3;1). Then add a text box under chart title. Don’t type anything inside, instead go to formula bar and type =E5 while text box is selected.

Than make a separate pie chart from values 50%-5%-5%-5%-5%-5%-5%-5%-5%-5%-5%. This will give you a pie chart with two half like the one above. But while one half is one piece, other half will be 10 equal pieces (5% x 10). Arrange it’s rotation to position 50% part at the bottom and make it invisible like above.

Now select 5% slices and color them from dark green to dark red like shown in the animated picture. After you are done, click on the chart background and make it invisible (no fill). Do this for both charts.

Make second chart a bit bigger and while it is selected, go to chart tools/format from ribbon and click sent to background. Now drag this chart over the first one and position it like shown in animated picture. You’ll likely need to arrange size of this chart here.

Now select first chart and make slice colors gray. Set pie explosion to 1% from right-click menu/format series. This will give you gauge needle 🙂

Now it is time for last touch. Make 10 text boxes with empty background (make one and copy/paste rest). Type values from 0% to 100% increasing by 10%. Place them around outer chart as shown in animated picture. That is all…

If you liked this Excel gauge chart tutorial, you can download example workbook and practice with it. You can also find mor charting tutorials in Charts category.