Speedometer Graph in Excel

by artworker

369K4038

Intro: Speedometer Graph in Excel

I have seen some speedometers in some of the professional dashboards. I thought how? How do they do it? So I did some preliminary research and came up with this speedometer. Kindly comment for any changes required. 

STEP 1: Insert Values

Open a new workbook and enter
Value in cell B2
Max in cell C2
27 in cell B3 (exact value)
100 in cell C3 (maximum value)

STEP 2: Creating the Dial

Creating the Dial is simple. I used a doughnut graph to do that.
Doughnut in cell B12
180 in cell B13
30 in cell B14
150 in cell B15
Make sure cell B14 and B15 adds up to 180.

Select the 3 cells and create Doughnut graph from them.
Remove the legends
Change the doughnut angle and hole size to suite you. Mine was Angle=90 and hole size= 81%
Select the 180degree arc and set its fill to none.
In the same way set the other arcs to whatever color you require.

STEP 3: Graph for the Speedometer Pin

For the graph for the speedometer pin
Enter
Graph in cell B5
0 in cell B6
0 in cell B8
0 in cell C6
0 in cell C8

Now for the real thing which will use the values in cell B3 and C3
Enter formulae
=SIN(((((B3+C3)*180/C3)+90))*PI()/180)      in cell B7
=COS(((((B3+C3)*180/C3)+90))*PI()/180)    in cell C7

STEP 4: Speedometer Pin

Select the cells B6:C8 and create a scatter with streight lines graph.
Delete the legends and all except the graph line.
Open the primary horizontal axis and set the
Minimum to -1 (fixed)
Maximum to 1 (fixed)

Open the primary horizontal axis and set the
Minimum to -1 (fixed)
Maximum to 1 (fixed)

Set the graph's background to No fill

STEP 5: Finally

Align the pin graph with the dial graph and you are done.
Change the value in cell B3 and the speedometer will show the same!

This is just a basic instructable. Change it as per your convenience as required.

29 Comments

guyhussar 3 years ago

Thanks for this. I have only used the first part, so make a guage or my data - which I'd not known how to do before.

CheylaM 5 years ago

Ho can you add labels and the value the pointer is pointing at?

artworker 5 years ago

You can create labels by replicating the process of step 2 to create another doughnut of labels. Overlap with the two graphs and you are done. You may have to make the background transparent.

Chrispy_duck 8 years ago

Great workaround instead of downloading an add-on, thanks!

One improvement I've made is to combine all onto one graph for simplicity. With Excel 2013:
- Instead of creating a new scatter graph for the B6:C8 data, click the doughnut graph > Chart tools > Design > Select data.
- under Legend Entries click Add. Give the series a name and leave the value as one (1). OK and OK again. You should see a second doughnut ring around the original.
- Select the chart > Chart tools > Design > Change Chart Type. Select Combo at the bottom. Change the doughnut series back to a doughnut graph (Excel would have changed it) and change the new series you've just added to scatter with straight lines > OK.
- Click on the doughnut and amend as per original instructions of 90degree angle of first slice, hole of 80% etc
- Now go to Chart tools > Design > Select data. Click the new series you added (scatter graph) > Edit. In X values physically select B6:B8 (Should show as =Sheet1!$B$6:$B$8). In Y values do the same but for C6:C8. OK and OK again.
- Select the chart axis and change the max and min values (1 and -1 respectively). Select the axis and choose no fill under the text options.

You should now have it all under one graph! I've found formatting, copy/paste etc to be no issue.

Hope that helps :)

purple_moss 8 years ago

Thanks ! Is there a way for either the doughnut graph or the Scatter graph to "send to back" or "Move to front" ?

Haagimus 8 years ago

Unfortunately there is no way to do this with excel charts. If you need to adjust the Z-Order I would recommend leaving it as two charts and sending whichever one you want to back there.

AmmarS9 8 years ago

100 % more than perfect

PKM 13 years ago

Good work- you'll have me out of a job!  I can't think of a way to improve this graph (simplicity is the key to a good dashboard) but for people who would want to customise theirs further you might want to explain the math you used in step 3 to get the points for the speedometer needle.

For more fun with this you could play with the Data -> From Web tool- it gets the contents of part of a website into your spreadsheet.  If the thing you want your dashboard speedometer to show is available on the web somewhere, you can probably get it this way.

eliagp 8 years ago

dude your 5 year old comment has given me new tools to tackle my thesis. Love you, internet stranger!

artworker 13 years ago

Thanks PKM for the comment. It is a nice idea to have a web query connected to this graph. I will implement this on my share price sheet. As for the formula shown, I am looking for words to explain the same. Will do it ASAP.

kerrylangley 9 years ago

Great instructions ... easy to follow and easy to customize to my specific application.

I do have one question ... I need to copy as a link the final "speedometer graph" into Word & Powerpoint (as I am building an editable template)... but when I try to do this in Word, it separates the two charts ... so my only solution is to copy and paste as a picture (which very problematic)

Any thoughts?

Thanks

(Using Office 2007, Windows 7)

artworker 9 years ago

Damn! That is a serious issue you mentioned. I was totally devastated for some time. Got the solution!
After pasting into word right click on the graph which you want to put on top and set it's wrap value as in front of text. Now you can drag it over the other graph.
See the image for more info.
Hope it helps. Kindly revert if require more info.

jjdaly 9 years ago

This is wonderful and clearly described. I thank you and am using it.

jraikes 12 years ago

You could use XLGauge (www.pendragonsystems.com)

brianmcfadden 11 years ago

There was also a speedo graph with an attractive customizable background here (www.ExcelDashboardWidgets.com)

abaker5 12 years ago

I love this it's so helpful, especially the example.
More Comments