Creating a Speedometer, Dial or Gauge chart in Excel 2007 and Excel 2010
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:
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
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%
- 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:
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.
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
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
- 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
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
- 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
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:
Dim Sales As Long
Sales = Cells(18, "C").Value
If Sales >= 25000 And Sales <= 30000 Then
Selection.ShapeRange.Rotation = 135#
If Sales >= 30001 And Sales <= 40000 Then
Selection.ShapeRange.Rotation = 90#
If Sales >= 40001 And Sales <= 49999 Then
Selection.ShapeRange.Rotation = 45#
If Sales >= 50000 Then
Selection.ShapeRange.Rotation = 0#
If Sales <= 25000 Then
Selection.ShapeRange.Rotation = 180#
- 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:
- 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.
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