How to Create a Speedometer (Gauge) Chart in Excel

Step-by-step

The speedometer is two doughnut-style charts layered on top of each other: one draws the coloured dial, the other draws the needle. Each chart has its own small data table.

1. Set up the two data tables

• Speedometer data (title in A2, values in B3:B12): a start value, the values for the red, yellow and green areas, and a large blank area that becomes the hidden bottom half of the dial. A Series Level column next to it holds the labels shown around the dial.
• Pointer data (title in A15, values in B16:B18): the needle value, the needle size, and an end value. The end value is a formula that keeps the needle in balance with the dial: the total of B3:B11 minus the total of B16:B17, i.e. =SUM(B3:B11)-SUM(B16:B17).

You can change any of these cells later, except the formula cell.

2. Create the dial

Click an empty cell and go to Insert > Pie > Doughnut. Then go to Chart Design > Select Data > Add. Use A2 as the series name and B3:B12 (including the blank area) as the series values. Then edit the horizontal (category) axis labels and select the Series Level cells.

3. Rotate and shape the dial

Right-click any slice and choose Format Data Series. Set Angle of first slice to 270° and Doughnut Hole Size to 40%. Select the large blank slice and set its fill to No fill, so only the top half of the dial is visible.

4. Colour and label the dial

Colour the remaining slices from dark red through red, orange and yellow to light yellow and green. Delete the legend. Then click the + button next to the chart, choose Data Labels > More Options, tick Category Name only, and make the labels 12 pt bold. For extra depth you can add a shape effect such as a bevel from the Format tab.

5. Add the needle data

With the chart selected, go to Select Data > Add again. Use A15 as the series name and B16:B18 as the values. A second ring appears around the first.

6. Turn the second ring into a needle

Go to Chart Design > Change Chart Type > Combo. Set the pointer series to Pie and tick Secondary Axis. In Format Data Series, set its angle of first slice to 270° as well. Give the two large pie slices No fill, colour the thin needle slice black, and remove its border.

7. Show the needle value

Edit the pointer series’ axis labels so they point at B17 first, then B16 and B18 (hold Ctrl). Click the needle, add a data label showing the Category Name, delete the other labels, and make the needle’s label larger and bold (red works well).

Test it

Change the needle value in B16 and watch the needle move. Speedometers like this are a staple of professional dashboards.

Go further

My Excel Dashboard & Data Analysis Masterclass shows you how to build 10 interactive dashboards from scratch, where gauges like this one are a regular feature.