How to Create an Excel Gauge Chart the Easy Way?
(Note: This guide on how to create an Excel gauge chart is suitable for all Excel versions including Office 365)
Excel charts are a great way to depict data easily and effectively. There are a variety of charts available and depending on your preference you can choose the chart of your choice.
One among the variety of charts is the “gauge chart”. But what does it do and how can it help you?
In this article, you’ll learn how to create an Excel gauge chart to depict your data effectively.
You’ll Learn:
Watch this Video on How to Create an Excel Gauge Chart
Related Reads:
How to Create a Funnel Chart in Excel? 2 Useful Ways
How to Save Excel Chart as Image? 4 Simple Ways
How to Create a Step Chart in Excel?
What is Excel Gauge Chart?
Before we learn how to create an Excel gauge chart, let us see what a gauge chart is.
The Excel gauge chart also called the speedometer chart consists of a dial-like structure over which a needle-like pointer represents a specific point in the data. This type of chart helps depict the performance of the data. The Excel gauge chart is dynamic, meaning the chart changes when the data in the table changes.
This type of chart has a variety of applications from corporate presentations to show the performance of a project or to show the stage of a process to school presentations to show the performance to students.
The gauge chart is created by a combination of a pie chart and a donut chart. It represents the start and end points on the scale and where the specific data lies in the scale. You can also add multiple ranges to the scale.
How to Create an Excel Gauge Chart?
Let us consider a basic example where we have to create an Excel gauge chart to represent the performance of a student in a school.
The first step in creating an Excel gauge chart lies in creating the data points and the scale. We also need to create data points for the dial. Aside from that, we need to create three tables to create the chart.
The first table denotes the category in the chart. The performance of a student can be plotted from initial to maximum and there can be any number of data intervals in between. Let us say, the initial value is 0 and the maximum value is 100. Create the data points in increment values, so that the intermediate data points add to the maximum value.
The second table denotes the labels in the chart. Let us name the labels in increments of 10 from the start to the end and also make a total of it.
The third table denotes the pointer and three values. In this table, you can specify the value you want to denote and the width of the pointer. The rest value denotes the total values of the first table minus the pointer value and the pointer width.
The first and second tables are used to create the doughnut chart and the final table is used to create the pie chart.
Creating the First Doughnut Chart from the First Table
Let us now create a chart for the data in the first table.
- Navigate to Insert and then click on the dropdown from Insert Pie or Doughnut Chart. Select Doughnut Chart. This will populate an empty chart since we have not selected any data.
- To add the data to the chart, right-click on the empty space and select Select Data.
- In the Select Data Source dialog box, click on Add.
- This in turn opens the Edit Series dialog box. In the Series name text box, enter any name for the data. In the Series values text box, click on the select data button and select the data under the Values section of the first table.
- Click OK.
- This gives you the doughnut chart. You can see the orientation of the chart is a bit off.
- To change the angle of the chart, right-click on the doughnut chart and select Format Data Series.
- In the Format Data Series, under the Series Options, change the Angle of the first slice to 270°.
- If you want to change the size of the chart, change the Doughnut Hole Size.
- Now, let us delete the bottom part of the doughnut chart. Select the part you want to delete, select the Fill&Line section and click on No Fill.
- If you want to change the colors of the other data points, click on the data points, navigate to the Line&Fill section and select the color from the dropdown.
Let us now select the data labels which will act as cues for the viewers.
- In the customize shortcut buttons, click on Chart Elements and select Data Labels. This populates the numbers on the chart.
- Now, right-click on the numbers and select Format Data Labels.
- In the Format Data Labels pane, select Values from Cells and select the headers corresponding to the values. Click OK.
- Uncheck any other checkbox in the Data Labels option.
- Now, delete any unnecessary data labels and elements by clicking on them.
- Thus we have the doughnut chart which acts as the scale over which the pointer moves.
Suggested Reads:
How to Make a Pie Chart in Excel? A Step by Step Guide
How to Create an Excel Map Chart from Pivot Table Data? 3 Simple Steps
How to Make a Box Plot Excel Chart? 2 Easy Ways
Creating the Second Doughnut Chart from the Second Table
Plotting the second chart by using the second table is an optional part of creating the Excel gauge chart.
This part is mainly helpful to give in-depth cues and additional scaling to the existing chart.
- First, right-click on the empty space in the table and click on Select Data.
- In the Legend Entries(Series) click on Add.
- This opens the Edit Series dialog box.
- In the Series name text box, give the chart any name. In the Series value, select the data from the Values in the second table.
- Click OK. The selected data is added to the Data Series dialog box.
- Again, click OK. This populates the chart above the existing chart.
- You can see that this new chart is already rotated to the previously specified degree.
- Now, right-click on the below half of the chart and select Format Data Series. In the Fill&Line section, click on No Fill.
- Change the color coding as per your wish.
- Now in the customize shortcut buttons, select the Data Labels. Right-click on the numbers and select Format Data Labels.
- In the Label Options, check the checkbox for Value from Cells and select the headers for the values in the table. And, make sure to uncheck any other checkbox.
- Delete any element you deem unnecessary and move any data elements based on your choice.
Creating the Pointer and Pie Chart from the Final Table
This is the third, final, and most important step in creating the Excel gauge chart.
- To create the pointer, right-click on any empty space in the chart and select Select Data.
- This opens the Select Data Source dialog box. In the Legend Entries(Series), click on Add.
- This in turn opens the Edit Series dialog box.
- Enter the series name in the textbox given below. In the Series values text box, select the data in the values column from the third table.
- Click OK.
- Then, right-click on the doughnut chart and select Change Series Chart Type.
- In the Change Chart Type dialog box, select Combo and click on Custom Combination. In the Pointer data series, click on the dropdown and select Pie.
- Click OK.
- If the angle is not correct, right-click and select Format Data Series. Under the Series Options, change the Angle of the first slice to 270°.
Now, let us just leave the pointer and hide the other parts of the chart.
- Click on the data points in the pie chart. Navigate to the Fill&Line section in the Format Data Series and click on No Fill.
- Delete any unwanted elements or move the chart elements to your preference.
And, here it is. We have successfully created the Excel gauge chart or the speedometer chart to our liking.
When you change the Pointer value in the third table the pointer in the dial changes accordingly.
Also Read:
How to Make an Excel Bullet Chart – 2 Easy Examples
How to Graph a Function in Excel? 2 Easy Ways
How to Make a Line Graph in Excel? 4 Best Line Graph Examples
Frequently Asked Questions
Can we create an Excel gauge chart with multiple scales?
Yes, we can create any number of scales or dials over which the pointer moves to depict the information. Once you create the first table and the doughnut chart, creating the rest of the dials is the same.
Are the speedometer chart and gauge chart the same in Excel?
There is a lot of confusion regarding the term gauge charts and speedometer charts in Excel. Excel gauge chart signifies the charts which help show the performance of a particular data over a range of data. And, speedometer chart is also a part of the Excel gauge chart.
What are the specifications to create the pointer table in the Excel gauge chart?
The pointer value to which the pointer should point. The thickness of the pointer. When the pointer value is 1, the pointer is thin and when it is 5, the pointer will be thick. The rest value is used to keep the pointer in balance. It is the result of the total of the first table minus the pointer value and its thickness.
Closing Thoughts
In this article, we saw how to create an Excel gauge chart easily.
Creating an Excel gauge chart involves a couple of steps to arrive at the perfectly crafted chart. Initially, this may seem pretty confusing and elaborate at first. Once you understand how to create tables and how to curate the chart, it is a very easy and powerful tool to depict the performance of a variety of data.
Furthermore, you can use the existing chart, duplicate it and use it as a template to create several charts and depictions.
Want more high-quality guides for Excel? Check out our free Excel resources centre.
Click here to access in-depth Excel training courses and master in-demand advanced Excel skills.
Simon Sez IT has been teaching critical IT software for over ten years. For a low, monthly fee you can get access to 130+ IT training courses by seasoned professionals.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!