Because Excel does not have an inbuilt support of Gauge Charts/Speedometers, you’ll need to make the chart by mixing a doughnut chart as well as pie charts.
The gauge chart (a.k.a dial chart, also known as a speedometer chart) can be used to evaluate the performance of the goals you have decide to set. It is referred to as speedometer charts because it is reminiscent of a speedometer in automobiles, and makes use of a pointer to display information as an indication on the gauge.
In general, the gauge chart can be used in order to show the performance or achievement of a particular data field on a maximum-minimum size. Excel doesn’t come with built-in support for making gauge charts. However, with some tricks, you can make gauge charts by combining two chart types that include the doughnut chart as well as the pie charts.
Because Excel doesn’t provide the built-in gauge chart it is necessary to make a gauge chart making use of the combo chart feature in Excel. We’ll show you how to create a gauge chart within this post.
Table of Contents
Setup the Data for the Gauge Chart
We’ll begin by creating our data sets. We’ll first need to create three distinct tables of data that include One for dial data, another for the pointer, and one for the charts (optional).
Create your table according to the format shown below:
The Dial
- Performance Labels They determine the chart labels that you would like to display in the dial. These will include labels like Poor Good, Average, Good and Excellent.
- LevelsThe values used will divide the speedometer into several sections.
The Pointer
The pointer is made using the following values. These values decide where you would like to place the pointer on your gauge chart.
- Pointer This number indicates how much from the chart of gauges that you would like the needle to go.
- Thickness The Thickness is the size of needle (the pointer). You can alter the needle’s thickness to the size you prefer however, it’s recommended to limit it to five pixels.
- rest value The value that is for the remainder of the pie graph. It is calculated using the formula ‘=200-(E3+E4)’. This formula should be entered in cell E5.
Create Doughnut Chart
After you have set up your data choose the values in the column ‘Level’ of The initial Table (The Dial). Then, click the tab ‘Insert’, select the “Insert Pie chart or doughnut’ icon in the Charts section and select the ‘Doughnut’ chart from the drop-down menu.
Then, remove your default title for the chart as well as the legend. You now have the doughnut chart, which is an equilateral triangle with one end (level: 100), and the remaining components on the opposite side (level 20-50 and 20, 10,).
Rotate the Doughnut Chart and Remove the Chart Border
We now need to alter the direction of the chart by turning the chart. To do this, click right on the colored area of the chart and choose the Format Data Series option.
This will open an option on the left side of the chart. Within the pane you can set the angle of first slice’ to 270deg by using the slider. You can also alter the size of the doughnut hole as well if you wish to.
Remove the Chart Border
After you have placed the chart correctly then remove the border of the chart (the separation of white lines between every color) to keep the chart neat and tidy.
In the same right-side ‘FormatData Series pane, click on the ‘Fill and Line’ icon. Go to the section ‘Border and choose the option ‘No line’ to eliminate the border of the chart.
Turn Full Circle Doghnut Chart to Semi-Circle
As you’ve guessed that gauges never form a full circle, therefore in order to convert the full circle into half circles it is necessary to cover the lower slice in your graph.
To do this you need to double-click on the bottom in the graph to launch the Format Data Point’ pane. Within it, click the Fill & Line’ tab, and then in the Fill section, select “No Fill” to make the bottom of the slice transparent.
Change the Colors of the Rest of the Slices
For the remainder of four points in the data, we’ll alter the color to create a more attractive chart.
Select the desired slice of the graph by clicking twice on it. In the “Format Data Point’ pane, move to the ‘Fill & Line tab. click on the “Fill Color icon under the ‘Fill’ section . This will open the color palette and select a color for the slice.
Select each slice individually and alter the hue of the slices. After you’ve finished you’ll have something like this:
Add Data Labels to the Chart
After that after that, you need to label the data on the charts. A gauge chart that is not labeled is useless and therefore, let’s get rid of it. In addition, you can add label the data at the end as well, but that’s an arduous procedure. Therefore, we’ll label the data now, to make it easy.
To add labels to data simply right-click on the slice, select “Add Data Labels in the context menu, and then choose the option to add data Labels’.
This will include these value (Level Column) as labels from the table that was created earlier.
Double-click the data labels in the lower slices (transparent slices) and then delete them. After that, right-click any data label and choose the option ‘Format Data Labels’.
In the “Format Data Labels’ pane, select the “Value From Cells” option. A small Data Label Range dialog box will pop up.
Within the Data Label Range dialog, select the “Select Data Label Range’ field and then select the label names under the Performance Label from the first table of data then click OK. Be sure to not select the label ‘Total.
Untick the “Values” from the The Format Data Labels pane, and close the panel.
Make the pointer using a Pie Chart
Let’s include the pointer in the gauge. To do this, right-click on the chart, and then select “Select Data”.
Within the Select Data Source dialog, click the ‘Add’ button to open the Edit Series dialog box.
In the Edit Series dialog, type in ‘Pointer in the field for Series Name. Select the ‘Series Values field, and then delete the default value of ‘=1′. Then navigate to the Pie chart table (The Pointer), select the range that contains data for Pointer Thickness, Pointer and Rest Value i.e. E3:E5 for Series values , and select OK.
Then click ‘OK’ for the box to be closed.
Convert the Pointer Doughnut Chart to a Pie Chart
The next step is to convert the doughnut chart into an a pie chart. To do this, right-click the chart’s outer surface and choose the option to change Series chart type’.
Within the change Chart Type dialog box, select ‘Combo’ in the Tab All Charts. Select the Chart Type dropdown menu next to the Series Name ‘Pointer’ , and select ‘Pie’ as the type of chart. Then, look for the box ‘Secondary Axis’ next to the Series ‘Pointer’ and then click ‘OK’.
After you’re finished, your chart could look something as follows:
Change to convert the Pie Chart into the Pointer (Needle)
Make sure you align your Pie Chart with the Doughnut Chart
The next step is to align the pie chart to that of the doughnut. In order to make both charts work together it is necessary to align your pie charts by about 270°, as you did in the past. Right-click the pie chart and then open the Format Data Series’ and set the angle of the first slice to 270 degrees for the pie chart.
Remove Pie Chart Borders
The next step is to take away the borders of the pie chart, just as you did with the doughnut charts. Go to the Fill & Line’ tab and choose the ‘No line option’ under the section ‘Border’ to take away the border of the chart.
The chart now has three slices: the gray slice and the blue slice as well as the red sliver located at the 12 o’clock point in the image above.
Make the Pointer
To create the needle/pointer you must hide the pie chart’s grey segment (pointer slice) and blue section (Rest value slice) leaving the needle section alone.
You can also hide the pie slices chart, just like you did with the doughnut charts. Double-click on the gray data point, then right-click to select Format Data Point’. Then select the ‘FIll and Line’ tab. Check “No Fill” within the fill section. The same procedure is followed to cover the next large piece (Blue) in the chart, so it is only one line (Orange slices) remains.
Then, choose the pointer slice, then go to the Fill & Line’ tab. Click the “Fill Color icon under the Fill section and then change the color of the needle using black (Choose the color you prefer).
The speedometer is now ready
How Gauge Chart Works in Excel
We’ve created the gauge chart. Let us explain how it works. It’s easy.
When you alter the value that corresponds to Pointer within the table the needle will move.
However, before we can make that change, let’s create an individual labels for data (text box) for the needle , which will automatically update to reflect the value of the speedometer. This will make your chart more readable and easy to comprehend.
To do this, go to the tab ‘Insert’ within the Ribbon and then click the ‘Text Box icon in the Text group.
Go into your spreadsheet, then click and add the text box to the size you want, as illustrated below. When you click on the text box to edit it. Go into the Formula Bar, enter the symbol ‘= and then select that cell as called E3 (Pointer Value) Then hit the Enter key. This will connect the text box to cell E3. Then, format the text box in any way you think is appropriate.
The value of the pointer is the performance you wish to evaluate or measure. This will determine how far down your gauge chart do you would like the needle/pointer to go.
This is when the 3rd Table (Chart Data) comes into the picture. This table is where the marks of students to help us assess the student’s performance.
In the future, whenever you alter the value in cell E3 the needle will instantly move, and the value of the text box below the needle will change too. You can alter the value of the pointer with the marks of each student to evaluate their performance.
You can also alter the size of your needle simply by altering the value of ‘Thickness’ on the table below.
Now we have a working gauge charts in Excel.
Well, That’s it. This is the step-by-step guide to create an gauge chart or speedometer in Excel.