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.

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)

Value in cell B2

Max in cell C2

27 in cell B3 (exact value)

100 in cell C3 (maximum value)

<p>Hi there, not sure if you have answered this question before but I have 2 conundrums hoping you can solve:</p><p>1) I sometimes have negative values which need to be "in the green" so to speak, while positive values also need to be in the "green". For example -$1000 to +$10000 should be represented as green while anything below -$1000 should progressively become "redder" or worse. </p><p>2) I also cannot have the gauge going past the horizontal axis (as my users play around with the gauge it can tend to spit out a range of results which makes it go right round into "transparent" territory. How best can I fix this?</p><p>Thanks I find this tutorial definitely the best on the web and most appreciate the maths behind it!</p><p>You'll see in the example that cash flow (-$1570 should be in the green - while should the cash flow be positive then this should be even more green!) and invested I need to have the same issue (as the negative sign is messing things up! I tried making it an absolute value but that didn't work :( )</p>

<p>Cool! Thanks for letting me know. I don't know if I can fix these issues. Rest assured that I will do a brainstorm for the same. If I can fix this, will surely do the victory dance. Will keep you updated.</p>

<p>There will be two victory dances. #nopressure</p>

<p>I didn't see a fix posted for negative values, so here's a way of achieving it. :)</p><p>The method artworker posted assumes the dial starts at zero, which for negative values isn't the case.</p><p>Add an extra value in cell A3 which should hold the bottom limit of your dial, so for a guage ranging from -100 to +100 you'd have -100 in A3 and 100 in C3. Keep your needle value in B3.</p><p>Then, based on PKM's change to use RADIANS below, use these 2 formulae</p><p>B7 =-COS(RADIANS((B3-A3)*180/(C3-A3)))</p><p>C7 =SIN(RADIANS((B3-A3)*180/(C3-A3)))</p>

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

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

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.

<p>Great workaround instead of downloading an add-on, thanks!</p><p>One improvement I've made is to combine all onto one graph for simplicity. With Excel 2013:<br>- Instead of creating a new scatter graph for the B6:C8 data, click the doughnut graph > Chart tools > Design > Select data.<br>- 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.<br>- 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.<br>- Click on the doughnut and amend as per original instructions of 90degree angle of first slice, hole of 80% etc<br>- 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.<br>- 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.</p><p>You should now have it all under one graph! I've found formatting, copy/paste etc to be no issue.</p><p>Hope that helps :)</p>

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

<p>Great instructions ... easy to follow and easy to customize to my specific application.</p><p>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)</p><p>Any thoughts?</p><p>Thanks</p><p>(Using Office 2007, Windows 7)</p>

<p>Damn! That is a serious issue you mentioned. I was totally devastated for some time. Got the solution!<br>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.<br>See the image for more info.<br>Hope it helps. Kindly revert if require more info.</p>

<p>Well, so great!.Thanks!</p>

<p>This is wonderful and clearly described. I thank you and am using it.</p>

<p>Thank you! Appreciated!</p>

You could use XLGauge (www.pendragonsystems.com)

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

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

Gr8 job art worker. <br>This will be useful to create dashboards in excel itself.

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

<blockquote>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<br></blockquote> <br> From <a href="http://phoenix.phys.clemson.edu/tutorials/excel/trig.html" rel="nofollow">here</a><br> <br> You can simplify the expressions to<br><br> B7: =-COS(RADIANS(((B3*180/C3))))<br> C7: =SIN(RADIANS(((B3*180/C3))))

Thanks PKM.<br><br>Its really an easy way !!!

Thanks PKM for the simplification. I got the formulae, but did not had the time to update the instructable. Appreciated!

thank you, nice guide

Wonderful! Now the folks at work can be amazed by "MacroMan"'s graphing abilities as well. We'll keep it our secret right? ; ] <br>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! <br>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. <br>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...

Very nicely done! I am fattered, I mean flattered! :P

This is a very cool method for making data visual in a different way! Cool.