How to Create an Excel Gauge Chart (AKA Speedometer Charts)

You might think you need some fancy add-ins to create charts that look like a speedometer. In this tutorial, you’ll learn how to create this snazzy image from a pie chart and a doughnut chart.

Download CreatingGaugeChart.xlsx to follow along.

The Steps

1. Create the data for the speedometer.

If a circle is 360 degrees, then a half-circle is 180. The number and value of intervals depends upon how detailed you want to be. In the sample file, we’ve set up 6 intervals which add up to 180. We’ll need all 7 cells, including the total.

2. Create the data for the needle.

We’ll also need 3 cells that contain:

• Position of the needle, your actual data value
• Width of the needle, which is always 1
• A formula which subtracts the sum of these values from 360

3. Create the speedometer with a doughnut chart.

Next, we’ll create our first chart. Select the 7 cells for your Speedometer and insert a Doughnut Chart. You’ll find this in Excel 2010 on the drop down button named Other Charts. In later version, you’ll find it in the drop down button for Pie Charts.

Note: While you can use the F11 shortcut to create a chart. For this activity, it’s best to create the chart in the same worksheet as the data to make all the necessary modifications easier.

4. Modify the chart to look like the half-circle dial.

Right click the chart and choose Format Data Series. We’ll make two changes in the next dialog box. First, change the Angle for first slice by typing in the number 270 and pressing Enter. Next, change the doughnut Hole Size to 50%. Finally right click the wedge on the bottom and choose Format Data Series. Change the Fill to none.

5. Add needle data as a pie chart.

Right click the gauge chart you just created and choose Select Data. Click the Add button and Name the new series “Needle.” Navigate to the Series values field and select the Position, Needle and End data. Click OK out of this dialog box. The next steps vary depending upon whether you are using Excel 2010 or earlier and Excel 2013 or later.

Excel 2010 and Earlier:

• Right click the outer doughnut and choose Change Series Chart Type.
• Choose Pie.
• Right click the pie or, in the Layout tab in Excel 2010, in the first field in the ribbon, choose Series “Needle”, then Format Selection, below it.
• Change the Angle of the first slice to 270. Then, click the radio button for Secondary Axis.

Excel 2013 and Later

• Right click the pie chart and choose Change Series Chart Type.
• Choose Combo Chart.
• Change the Chart Type for the Needle series to Pie.
• Check the Secondary Axis check box.

Note: If right clicking proves tricky, click in the Chart Elements field on the Layout tab (2010) or Format tab in later versions. Then, use the Shift+F10 shortcut to show the “right click” menu.

6. Modify the pie to look like a needle.

Whether it’s obvious or not, there are 3 slices: two large ones and 1 tiny one that looks like a chart and choose Format Data Series to remove the Fill and Border. (Tip Shift F10, Right Click button). If you want to change the color of the needle, it might be best to zoom in to make sure you’re just selecting the 1 point wedge.

Finishing Touches

As you change the value in Position, you’ll see the needle move. However, it would be helpful to see some numbers here to complete the picture. Next to the values we used for the doughnut chart, cumulative values have been entered from 0 through 180. We’ll use the non-zero values to add data labels which will be the meter positions against which we’ll view the needle. Using the Layout tab method, select Series1 (In 2013, choose the Format tab). Use the Shift+F10 shortcut or use the “right click key” on your keyboard. Choose Add Data Labels. Click the bottom (180) label and delete it. For each of the remaining labels, click the label. Then, in the Formula bar, type and “=” and the click on the appropriate meter label value. Drag them to the right places on the gauge.

The legend really doesn’t inform anything here. So, we can delete it. Let’s add or modify the Chart Title. The sample data already has chart titles. Select the words Chart Title and click into the Formula bar. Type an equal sign “=” and then click on the cell with the appropriate chart title.

Download CreatingGaugeChart-Complete.xlsx to see how you did!

Note: Advanced users — Try using Data Validation and a VLookUP to select the values for the “Needle” Position value and Chart Title data.