Speedometer Chart / Gas Gauge Chart



A speedometer graph is comprised of multiple components to give the illusion of one chart. The background is an image or donut chart. The needle is created with a pie chart. The numbers and text are done with text boxes.

To start you want to lay out your preliminary data. The following 3 columns contain all the data I will need to create the chart. You will notice that I have formulas in 3 cells. I have displayed them for reference. You will also notice that in row 14 I sum the above data and each column (A, B and C) total is 360 (the same amount of degrees in a circle). The needle will represent any position on the chart based on the value entered in cell C2.


















To create the chart, highlight cells A2 through A12 (don’t include the header row). Create a Donut Chart (shown below using Excel 2007)…


This creates the necessary components to create the first half of the Donut Chart.


You want to rotate the chart 90 degrees so the solid blue portion is positioned at the bottom. To do that, right mouse click on the donut ring and select Format Data. Under Series Options, select Angle of first slice and set it to 90. When you close the window you will see the chart has rotated.


Now we have the foundation for the chart. You won’t need the legend on the right so you can remove it by left mouse clicking on it then pressing the delete key.

Next I want to make the bottom ring of the circle transparent. I left double click on the bottom section to select the bottom portion of the circle and then right mouse click on it. I select Format Data Point to bring up the Format Data Point Dialog Box. I click the Fill option and select No Fill. If the boarder is visible, I also change the boarder color to no fill.

In the below example I am in the process of making the bottom circle transparent.

When Done I am left with the following.


Now I want to work on the needle of this chart. I first highlight cells C2 through C5 and create a Pie Chart.


Just as I did with the donut chart, I want to rotate the Pie Chart 90 degrees and remove the legend to the left.


I also want to make the Red, Blue and Purple sections of the Pie chart transparent without any boarders (just like the bottom of the donut). This will leave me with just the needle showing.



I make the whole pie chart background transparent. And the needle is shown on top of my donut chart.

You can also resize the needle by changing the pie chart size and then moving the Pie chart so it still is aligned correctly over my donut chart. In this example I have the needle slightly extending beyond the arch of the donut chart.

I also have changed the needle color to be black….



Well now you can test you chart by changing the value in cell C3. Set it to 0 and also 100. Check the position of the needle and readjust the pie chart as needed.

Now I want to add some numbers as indicators on the gauge. I simply insert a text box for each label I want to use. (In Excel 2007 from the Ribbon, Insert Tab >> Text Box). Make sure the background and outline of the text box is transparent.



I position by changing the value in cell C3 for each text box I have. This helps me position the text correctly.

Almost done! Now I want to add a heading to the gauge. This is where cell C2 come in. I insert another text box but instead of typing in text, I place an = in the formula bar and then select cell C2.

Now I was creating this chart to show a percent of holdings for an account so I used the word Share. You could just as easily put the word Percent, Total or any other text you want.


The last thing I want to do to complete this is to group all the components of this chart together so that I can resize the graph as needed.

From the Ribbon (Excel 2007) I select my items and then on the Format Tab I choose Group.
Depending on what you group first you may need to arrange the items on the chart again by bringing items forward or moving items backwards.

When done you have a fully functional speedometer graph or Gas Gauge chart. Used judicially, this little graphic can turn your ordinary dashboard into a “WOW did you see that dashboard”.

Now what if we wanted to dress the dial up some more.

Perhaps I wanted to do a gradient fill from left to right. Well to do that I would change my data in the dial column to be only two equal rows.














I then continue with the above procedure but when I select fill for the top section of the donut, I choose a Gradient fill and select the colors I want and direction of the angle.



Using the same technique, I can use 3 data points for my dial














Perhaps you are looking for a more realistic graphic. Just past in a picture and overlay the pie chart. Tweak the data to ensure the needle does not pass the E or F and viola!
















So how would you use this technique in your dashboards?  

13 comments:

  1. very helpful - thank you for this!

    ReplyDelete
  2. Awesome tutorial! One question, how would you change the scale if you wanted it to be, say 70-100 instead of 0-100. I've played around and can't seem to get the formula right. Thanks!

    ReplyDelete
  3. Let me understand your question. Where I have 0 on the chart, you would have 70 Where I have 100 you would have 100. and instead of my 50 being mid point, yours would be 87.5.

    That is no problem. I will followup with a new posting with that solution if you can confirm this.

    Thanks for stopping by.

    ReplyDelete
    Replies
    1. I am looking for the same thing Koufax is, did you find a solution? Thanks!

      Delete
  4. Thanks for stopping by. Here is the solution to modifying the Fuel Gauge numbers.

    http://excelprofessor.blogspot.com/2013/02/fuel-gauge-redo.html

    ReplyDelete
  5. Thank you! This was perfect. I spent several minutes searching for something already made to do this for me to put into a Powerpoint presentation but this did the trick. Thanks a bunch!

    ReplyDelete
  6. This is awesome!

    Does it work if the scale went over 100%?
    I've built one with a scale of 0-100% but once it goes over 101% the needle sarts to go backwards.

    Thanks

    ReplyDelete
  7. Awesome work Excel Professor - a very valuable tool. Thanks!

    ReplyDelete
  8. vba code for this sample!!??

    ReplyDelete
  9. I need help, I work with -% and positive % so i need a scale from -100 0 100

    ReplyDelete
  10. Take a look at the following post. I think it will help you. http://excelprofessor.blogspot.com/2013/02/fuel-gauge-redo.html

    ReplyDelete