ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software»
  • Office Software Suites»
  • Microsoft Office

Creating a Speedometer, Dial or Gauge chart in Excel 2007 and Excel 2010

Updated on March 19, 2015

How to construct a Speedometer, Dial or Gauge chart using a Doughnut chart and Visual Basic code in Excel 2007 and Excel 2010

Hi and welcome to my latest hub on Excel. Today, I am going to look at how to create a Speedometer chart which is sometimes known as a Gauge or Dial chart. You can use one as part of a dashboard, in a presentation, or just to give your Excel data some extra visual impact and set it apart from other spreadsheets. The Speedometer chart that we will create today will be used to show how the sales team is progressing towards its monthly sales target.

The chart itself is made up of three components:

  • A Doughnut chart to make up the dial
  • A needle, created with a transparent background which will rotate perfectly around its fulcrum
  • Visual Basic code to bring the needle to life. The code takes the sales data we are working with and rotates the needle to represent the progress towards the goal

Our finished chart will look something like the one below:

A completed Speedometer chart created using Excel 2007 or Excel 2010.
A completed Speedometer chart created using Excel 2007 or Excel 2010. | Source

Create the Doughnut chart which will form the dial of the Speedometer chart in Excel 2007 and Excel 2010

The first step is to decide how many sections you want in your Speedometer.

  • Create a table of numbers with twice the number of segments you want in your Speedometer dial (if you want four, choose eight numbers)
  • The numbers in your table can be any number; they just have to be all the same to ensure all the segments are the same size.
  • Select your table
  • On the Insert tab, select the Other Charts button in the Charts group
  • Choose Doughnut and Doughnut once more

You will end up with a chart that looks like the chart below

The initial Doughnut chart created in Excel 2007 or Excel 2010, without any configuration.
The initial Doughnut chart created in Excel 2007 or Excel 2010, without any configuration. | Source

Now we need to configure the chart

  • Select the legend and delete it
  • Right click on the first of the sections you will hide to create your Speedometer (in my chart, it is the orange section)
  • Choose Format Data Point
  • Under Fill click Solid Fill and select the Transparency slider moving it to 100%

Our Doughnut chart with one section made transparent in Excel 2007 or Excel 2010.
Our Doughnut chart with one section made transparent in Excel 2007 or Excel 2010. | Source
  • Repeat, making the fill colour transparent for the other sections (so in my example, I want to hide the light blue, purple and green segments that make up the bottom half of the doughnut)

Change the colours of the sections you want to display by:

  • Right clicking once more and picking Format Data Point
  • Once more on the Fill tab select Solid Fill and choose the colour you want
  • You will end up with something like this:

Doughnut chart with the bottom sections transparent and the rest with the desired colours, created in Excel 2007 or Excel 2010.
Doughnut chart with the bottom sections transparent and the rest with the desired colours, created in Excel 2007 or Excel 2010. | Source

Next, we need to make it so that the transparent parts are less obvious. We do this by making the chart area itself transparent and turning off gridlines

  • To do this, select the chart again and right click selecting Format Chart Area
  • Change the Fill to Solid Fill and choose 100% Transparency
  • On the Border Colour tab, pick Solid Line and 100% Transparency
  • Lastly, turn off Gridlines by selecting the worksheet and on the View tab in the Show / Hide group, clear the Gridlines check box

Finally, add a title and data labels should you wish to:

  • For the title, click the chart and on the Layout tab, choose the Chart Title button in the Labels group. Select the title position that you require
  • To add data labels, again with the chart selected, right click and choose Add Data Labels. These can then be edited to add a caption for each section should you wish to

Your chart should again look something similar to mine below.

The dial section of our Doughnut chart created in Excel 2007 or Excel 2010 is now complete.
The dial section of our Doughnut chart created in Excel 2007 or Excel 2010 is now complete. | Source

Create a Speedometer needle for our Speedometer chart in Excel 2007 and Excel 2010

Now that we have our main dial for the Speedometer created, we need to create the Speedometer needle.

  • First, pick the image for your needle; I suggest something on a pure white background, although any colour will be fine as long as the colour is the same around the needle itself
  • Next, open MS Paint (Start / Run MSPaint) or a similar picture processing software
  • Paste in the picture of your needle
  • Ensure that the image you are creating is a perfect square (mine is 1,000 by 1,000)
  • Turn on Gridlines and Ruler (in Paint, on the View tab, select Rulers and Gridlines

Beginning to configure the image of our needle for the Speedometer chart in MS Paint.
Beginning to configure the image of our needle for the Speedometer chart in MS Paint. | Source

Now, the next step is important as it will make your Speedometer needle rotate nice and cleanly without jumping around.

The fulcrum (the part the needle pivots around), needs to be as close to the centre of the picture as possible

  • Select your image and using the gridlines and rulers, paste the centre of the fulcrum as close to the centre of the picture as possible

Move the fulcrum of the needle to the centre of the picture created using MS Paint, utilising the ruler and grid lines as a guide.
Move the fulcrum of the needle to the centre of the picture created using MS Paint, utilising the ruler and grid lines as a guide. | Source
  • Once you have it positioned correctly, turn the gridlines and the ruler off, select the WHOLE picture including the white space, copy it and paste it into Excel

The finished image of our Speedometer needle, positioned in the centre of the picture.
The finished image of our Speedometer needle, positioned in the centre of the picture. | Source

With the picture pasted into Excel:

  • Right click and select Size and Properties
  • Shrink it down until the needle is the size you want (don’t worry about the white space we will deal with that next). Make sure to check Lock Aspect Ratio so that the image is not distorted.

Now we need to remove the background. This is white in my example, but can be anything as long as it is just one colour.

In Excel 2010:

  • On the Format tab with the picture selected, click the Remove Background button in the Adjust group
  • The picture will go purple; expand the selection to the whole picture to ensure that the entire background is removed

For those of you using Excel 2007:

  • Select the picture
  • Again on the Format tab, select Recolor in the Adjust group
  • Select Set Transparent Colour
  • The cursor will change, click on your background and it will immediately disappear

Lastly:

  • Position your needle on top of your doughnut chart and adjust the location and size of your data labels and also the Speedometer needle until it looks perfect

Our finished Speedometer, Dial or Gauge chart, created using Excel 2007 or Excel 2010 as well as MS Paint.
Our finished Speedometer, Dial or Gauge chart, created using Excel 2007 or Excel 2010 as well as MS Paint. | Source

Using Visual Basic to rotate the needle to reflect changes in data on our Speedometer chart in Excel 2007 and Excel 2010

The final step is to have the needle rotate to reflect your data on your Speedometer chart. To do this, we will write some Visual Basic code inside a macro and then assign that macro to the needle.

First, we need to enable the Developer tab so that we can work with Macros

For Excel 2007

  • Click the big Office button
  • Select Excel Options
  • On the Popular tab, tick Show Developer tab in the Ribbon

In Excel 2010

  • Select File
  • Choose Excel Options
  • On the Customize Ribbon tab, check the Developer box

We will put the Monthly Sales figure in a cell which will be used to rotate our needle. In my example below, I use cell C18, but you can use any cell you like (just change the code below to reflect that).

Now, we create and edit a macro:

  • Click the Record Macro button from the Code group on the Developer tab
  • Press the Stop Recording button
  • Next, select the Macros button and Edit the macro you just created
  • Copy in the text below:

Sub Macro13()

ActiveSheet.Shapes("Picture 8").Select

Dim Sales As Long

Worksheets("Speedometer").Activate

Sales = Cells(18, "C").Value

If Sales >= 25000 And Sales <= 30000 Then

Selection.ShapeRange.Rotation = 135#

End If

If Sales >= 30001 And Sales <= 40000 Then

Selection.ShapeRange.Rotation = 90#

End If

If Sales >= 40001 And Sales <= 49999 Then

Selection.ShapeRange.Rotation = 45#

End If

If Sales >= 50000 Then

Selection.ShapeRange.Rotation = 0#

End If

If Sales <= 25000 Then

Selection.ShapeRange.Rotation = 180#

End If

End Sub

  • You can see from the picture below, I explain what each line of code does in the green REM statements.

To learn more about the basics of coding in Visual Basic, I have a hub that is a beginner's guide to Visual Basic. It introduces a number of simple and yet powerful commands so that you can begin to write your own code. That hub can be found here:

http://hubpages.com/hub/Visual-Basic-for-Excel-2007-and-Excel-2010-A-Beginners-Guide


The Visual Basic code used to rotate the needle in the Speedometer chart based on the value stored in the linked cell, created in Excel 2007 or Excel 2010.
The Visual Basic code used to rotate the needle in the Speedometer chart based on the value stored in the linked cell, created in Excel 2007 or Excel 2010. | Source
  • The final step is to save the macro using the Save button in the tool bar and close the Visual Basic editing screen to return to Excel.

Now, we have to assign the macro to the needle:

  • Select the image of your needle and right click on it
  • Choose Assign Macro
  • Pick the macro you just created

Now, when you change the contents of your linked cell, and click on the image, it will rotate based on the value in the cell

With that, our Speedometer chart is complete. If we change the data in cell C18 (or whichever cell you linked to the needle using the Visual Basic code) and press the image, the needle will move automatically.

Conclusion

In today’s hub, we created a Speedometer chart (also known as a Dial or Gauge chart). They can be used as part of a dashboard or a presentation, to illustrate your data in a striking yet simple way. You can use one to show immediately how, for example, your team is progressing towards its monthly sales target. They will give your Excel spreadsheet and the data your chart a real visual impact.

To create it:

  • First, we took a Doughnut chart and used it to create the dial itself
  • Then we created the needle to show our progress towards our goal or target
  • Finally, we created some Visual Basic code to allow the needle to move so it could illustrate our progress towards our target

I hope that you enjoyed reading this hub and found it useful and informative. I had a lot of fun researching this and making my Speedometer chart just that bit more special than the standard fare. Please feel free to leave a comment below and thanks so much for reading.

© 2013 Robbie C Wilson

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.