Instructables

Speedometer Graph in Excel

Picture of 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. 
 
Remove these adsRemove these ads by Signing Up

Step 1: Insert Values

Picture of 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

Picture of Creating the Dial
value3.jpg
value4.jpg
value5.jpg
value6.jpg
value7.jpg

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

Picture of Graph for the speedometer pin
value9.jpg
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

Picture of Speedometer pin
value11.jpg
value12.jpg
value13.jpg
value14.jpg
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

Picture of Finally
value16.jpg
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.
jraikes2 years ago
You could use XLGauge (www.pendragonsystems.com)
There was also a speedo graph with an attractive customizable background here (www.ExcelDashboardWidgets.com)
abaker52 years ago
I love this it's so helpful, especially the example.
Gr8 job art worker.
This will be useful to create dashboards in excel itself.
kelseymh3 years ago
Very nicely done! It's always fun to see some actual mathematics used in Excel (pity you have to do the conversion from degrees to radians by hand, though).
PKM kelseymh3 years ago
Excel has a built-in function known as RADIANS(angle) where angle is the angle in degrees you wish to convert to radians. For example, the Excel expression used to convert 270° to radians would be RADIANS(270) which equals 4.712389 radians

From here

You can simplify the expressions to

B7: =-COS(RADIANS(((B3*180/C3))))
C7: =SIN(RADIANS(((B3*180/C3))))
hkabra PKM2 years ago
Thanks PKM.

Its really an easy way !!!
artworker (author)  PKM3 years ago
Thanks PKM for the simplification. I got the formulae, but did not had the time to update the instructable. Appreciated!
andrejgano2 years ago
thank you, nice guide
finton3 years ago
Wonderful! Now the folks at work can be amazed by "MacroMan"'s graphing abilities as well. We'll keep it our secret right? ; ]
I've had a play around with this today: if one sets things up like in the attached pic, one can enter data (eg scores out of 20) on another sheet and have the graph reflect this. And yeah, there are many and better ways of setting things up, - as I said, just a play around!
Adding the Redline cell easily allows one to change where the red extends to, which might be useful if one has to use this graph for different criteria.
Well done artworker: I'd not have come up with that one on my own, so thanks! Any other graph tricks you can show us? I'd be keen...
Excel Speedo clips.JPG
artworker (author)  finton3 years ago
Very nicely done! I am fattered, I mean flattered! :P
Toebak3 years ago
This is a very cool method for making data visual in a different way! Cool.
PKM3 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.
artworker (author)  PKM3 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.
Pro

Get More Out of Instructables

Already have an Account?

close

PDF Downloads
As a Pro member, you will gain access to download any Instructable in the PDF format. You also have the ability to customize your PDF download.

Upgrade to Pro today!