How to Create a Gauge Chart in Excel: SPEEDOMETER

There many types of graphs you can use to present ways in Excel. One of the little-used charts is the Gauge Chart, commonly referred to as the speedometer chart. In this tutorial, I will guide you on how to create a Gauge Chart in Excel easily.

What is a Gauge Chart?

A Gauge chart uses a needle or a pointer to indicate information on the dial. It indicates the minimum, maximum and current.

The pointer usually points at the current value, making it easy to visualize how far the current value is from the maximum and minimum values. A gauge chart looks like a car’s speedometer. Here is how it looks in Excel.

How to Create a Gauge Chart in Excel

Additionally, you can create a gauge chart with two or more ranges between minimum and maximum values. Then use the pointer to show the range in which the value falls under.

Uses of a Gauge Chart?

A gauge chart can be used across various industries: business, sports, education, etc. Here are some of the most popular use cases

  1. Performance Monitoring: Gauge charts are commonly used to monitor key performance indicators (KPIs) and display metrics such as sales targets, revenue, website traffic, or customer satisfaction scores. The gauge can show how close the current value is to the target or desired level, giving a quick and visual understanding of performance.
  2. Health and Safety Metrics: In industrial or manufacturing settings, gauge charts can be used to monitor health and safety metrics like machine temperature, pressure levels, or toxic gas concentration. The gauge helps workers quickly assess if these vital parameters are within safe operating ranges.
  3. Budget vs. Actual Analysis: Gauge charts are effective in showing the comparison between budgeted and actual values. This could be used for various purposes, such as comparing budgeted expenses with actual expenses for a project or tracking the actual revenue against the projected revenue for a business.
  4. Resource Utilization: Businesses can use gauge charts to monitor the utilization of resources, such as the occupancy rate of a hotel, the capacity of a server, or the utilization of manufacturing machinery. By displaying resource utilization in a gauge format, it becomes easy to understand whether resources are being optimally used or not.
  5. Survey Results: Gauge charts can be used to visualize survey results with a single value. For instance, in employee engagement surveys, the gauge chart can represent the overall satisfaction level of employees. Similarly, in customer feedback surveys, it can display the average customer satisfaction rating.

How to create a Gauge Chart in Excel

To create a gauge chart in Excel, you must combine two charts: a pie chart and a doughnut Chat. Follow these simple steps.

1. First, you need to prepare the data you will use for the speedometer chart. The data has to be in three separate tables.

2. In this Tutorial, I will use student marks as an Example. The image below shows how the tables are supposed to look like.

Excel Gauge Chart Table Example

First Table: The first table defines the categories for the final gauge chart. These categories will help you visualize and understand the data easily.

Second Table: These are just the labels for the chart. You can customize them depending on your data.

Third Table: This one contains the pointer and its thickness. It helps you to identify the current value on the speedometer chart easily.

Download Excel Gauge Chart Template

3. Insert a Doughnut chart by clicking insert tab>Charts>Doughnut Chart

How to insert a doughnut chart in Excel

Doing that creates a blank canvas.

4. Now insert the data to the chart by right-clicking>Select data

5. Click on Add Legend Entries from the pop-up window

6. Under series name type =”category” in the input box. Then under series values type values

As you can see, a doughnut chart has been formed on the right side.

7. Click ok.

8. Now, we need to rotate the doughnut chat so that we can have the side already looking like a speedometer. To do this right, click on the chart and select the format data series option. On the angle of the first slice, type 270 and then click on enter.

9. To get a real doughnut shape, we need to make the bottom half transparent. To achieve this, click on the bottom part only and choose the option No fill,

10. You can now customize the colour codes of the other section. Just click on any section and change the color

11. Now, you need to add the data labels with the help of a second doughnut chart. Right-click on the chart, then click on select data. A popup box will display.

Under this pop-up window, click Add below legend entries. Type the label in the series name input box. In the series values, select skip values.

12. Now the doughnut chart will appear like the image below

The next step is to hide the bottom half of the chart again using no fill. Also, you need to add a color scheme to the top part

13. Now that we have our speedometer, it is time to insert the needle or pointer using a pie chart. In order to achieve this, right-click on the chart and choose select data. Click on add to add one more legend entry. You can label this series’ name as a pointer or needle.

14. Now change this third chart to a pie chart by clicking design change chart type. Then select Pie for the pointer

This is how your pie chart should appear at this stage. If the pointer falls out of the top half, make sure your primary angle axis is at 270 degrees

Apply no fill to the large grey and blue areas so that you only remain with the pointer. If the pointer is too slim, you can always change the thickness from your data table.

Change the pointer to the secondary axis if it’s passing below the doughnut chart. Then make a point of Explosion to 5%. This will allow it to protrude a little bit outside the doughnut.

The speedometer chart is now set. But you have to add labels for easy visualization. Follow the steps below to add the labels.

Categories: Right-click on the categories doughnut, then select Add data labels >Add data labels

Leave a Reply

Discover more from Excel Wizard

Subscribe now to keep reading and get access to the full archive.

Continue reading