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.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: "https://hubpages.com/privacy-policy#gdpr"

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)