How to Create and Customise Gauge and Speedometer Charts in Microsoft Excel for KPI Dashboards

Executive dashboards, operational key performance indicator (KPI) trackers, and sales scorecards frequently require intuitive visual indicators to display progress towards quantitative targets. While Microsoft Excel provides dozens of native chart types—including column, bar, waterfall, and treemap visualisations—it does not offer a built-in speedometer or gauge chart widget. However, financial analysts and business intelligence practitioners can construct high-precision, dynamic gauge charts by layering two standard Excel charts: a doughnut chart to represent colour-coded benchmark zones and a pie chart to serve as the directional indicator needle.

When properly configured, an Excel gauge chart updates instantaneously whenever underlying source metrics fluctuate, displaying whether performance is critical, acceptable, or exceptional. This tutorial walks through the underlying mathematical logic, the step-by-step setup of the underlying data tables, chart formatting techniques, dynamic needle angle calculations, and best practices for embedding gauges cleanly within corporate dashboards.

Anatomy and Mechanics of a Dual-Layer Excel Gauge Chart

A dual-layer gauge chart relies on geometric alignment across two concentric series plotted on a 360-degree radial plane:

Layer Component	Underlying Chart Type	Mechanical Role in Visualisation	Rotation Angle
KPI Dial / Scale Ring	Doughnut Chart	Displays coloured risk/achievement bands (e.g., Low, Medium, High). The bottom half (180 degrees) is hidden to create a semi-circle.	270°
Indicator Needle	Pie Chart (Secondary Axis)	Displays an ultra-thin slice representing the indicator needle flanked by transparent slices.	270°
Transparent Half-Base	Placeholder Values (Both Series)	A calculated slice equal to the sum of all visible dial values, positioned on the bottom 180-degree hemisphere and formatted with no fill.	270°

Step 1: Structuring the Dial and Needle Data Tables

To ensure consistent scaling and responsive metric updates, arrange two adjacent tables on your worksheet: one for the dial bands and another for the needle calculations.

1. Dial Ring Data Table

Assuming a performance scale ranging from 0 to 100%, define three qualitative bands along with a mandatory base buffer equal to 100%:

Cell	Dial Segment Description	Value / Formula
A2:B2	Low / Red Band	30
A3:B3	Medium / Amber Band	40
A4:B4	High / Green Band	30
A5:B5	Hidden Lower Half	=SUM(B2:B4) (evaluates to 100)

2. Needle Calculation Data Table

The needle requires three distinct slices: the offset before the needle, the needle thickness, and the balance of the remaining 200-unit circle:

Cell	Needle Slice Component	Value / Formula
D2:E2	Current KPI Value (Score)	65 (dynamic input metric)
D3:E3	Needle Width / Thickness	2 (fixed constant)
D4:E4	Upper Circle Balance	=200 - E2 - E3

Step 2: Generating and Formatting the Doughnut Dial

Follow these steps to construct the semi-circular coloured gauge scale:

1. Highlight the dial data range (B2:B5).
2. Navigate to the Insert tab on the Excel ribbon, click Insert Pie or Doughnut Chart, and select Doughnut.
3. Right-click the doughnut ring and select Format Data Series to open the sidebar pane.
4. Set the Angle of first slice to exactly 270°. This rotates the slices so that the large 100-value bottom slice sits entirely across the lower horizontal hemisphere.
5. Adjust Doughnut hole size to approximately 60% to achieve a sleek, modern gauge thickness.
6. Click twice slowly on the bottom 100-value slice to select only that individual data point. In the Fill & Line tab, set Fill to No fill and Border to No line.
7. Format the remaining three visible slices with appropriate corporate dashboard colours: set the first slice to crimson/red, the second slice to amber/yellow, and the third slice to forest green.

Step 3: Overlaying the Needle Pie Chart

Now plot the needle series over the existing dial:

1. Right-click the chart area and choose Select Data.
2. Under Legend Entries (Series), click Add.
3. In Series name, select cell D2. In Series values, select cells E2:E4. Click OK.
4. With the chart selected, click Change Chart Type from the Chart Design tab.
5. Select Combo at the bottom of the dialogue box.
6. Set the Dial series to Doughnut (uncheck Secondary Axis) and the Needle series to Pie, checking the Secondary Axis checkbox beside the Needle series. Click OK.
7. Select the new pie chart series, right-click, choose Format Data Series, and verify that the Angle of first slice is set to exactly 270°.
8. Click twice on the large offset slice (cell E2 equivalent) and set its fill to No fill and border to No line.
9. Click twice on the trailing balance slice (cell E4 equivalent) and set its fill to No fill and border to No line.
10. Click twice on the thin needle slice (cell E3). Select Solid fill, assign a sharp contrasting colour (such as charcoal black or dark navy), and set a subtle drop shadow under Series Options > Effects.

Step 4: Adding a Central Digital Readout Label

To enhance chart readability, place a linked dynamic text callout directly at the centre of the gauge:

1. Navigate to Insert > Text Box and draw a small rectangular box inside the doughnut’s central void.
2. Click on the border of the text box, click into Excel’s formula bar at the top, type =, and click on your target KPI metric cell (e.g., $E$2). Press Enter.
3. Format the font to Segoe UI or Calibri, set the size to 20pt Bold, and centre-align the text.
4. Set the text box fill to No fill and line to No outline. The digital readout will now update synchronously with needle movements.

Common Calibration Pitfalls and Remediation

Issue Encountered	Underlying Cause	Corrective Action
Needle points in the opposite direction	Pie chart rotation angle mismatch	Confirm that both the Doughnut and Pie chart series have their Angle of first slice configured to identical values (270°).
Dial displays as a complete 360° circle	Missing lower-half placeholder sum	Ensure the hidden lower slice equals the mathematical sum of all upper visible segments.
Needle distorts or wraps past 100%	KPI value exceeds defined maximum threshold	Wrap the KPI score formula in =MIN(100, MAX(0, TargetCell)) to clamp values safely within the visible dial limits.
Chart resizing misaligns the digital text box	Floating text box unlinked from chart object	Hold Ctrl, select both the chart perimeter and the text box, right-click, and select Group > Group.

Mastering dual-layer combo charts enables Excel users to deliver executive-grade visual telemetry that communicates critical operational metrics clearly and concisely without relying on third-party plugins or external dashboarding tools.