1. Set up data with three segments: Left (empty), Progress, and Right (empty) totaling 100 to form a semi-circle gauge, and link to a dynamic value cell. 2. Insert a doughnut chart using the three segments, set the first slice angle to 270° and adjust hole size to ~70% to create a half-circle appearance. 3. Format the left and right empty segments with no fill to hide them, leaving only the progress segment visible as a gauge. 4. Add a needle by creating X and Y coordinates using SIN and COS functions based on the value’s angle (Value/Max * 180), then overlay an XY scatter point with a straight line, formatted as a thick red or black line with no marker. 5. Add labels using data labels or a centered text box linked to the value cell, and optionally include min, max, or target indicators with text boxes, while applying conditional colors and clean design for dashboard use, resulting in a functional, reusable gauge chart in Excel despite it not being a built-in chart type.

Creating a speedometer or gauge chart in Excel isn’t built in as a standard chart type, but you can build one using a combination chart—typically a doughnut chart with some clever data setup. Here’s a step-by-step guide to make a functional and visually appealing gauge chart.

1. Set Up Your Data

First, structure your data to simulate a semi-circle gauge. A common method uses three segments:

• One segment for the "filled" portion (your current value)
• Two background segments to create the appearance of a half-circle gauge

Here’s an example layout:

Note: "Filled" is your actual metric (e.g., 65%), and "Upper Fill" = Max - Filled. "Lower Fill" is set to 0 to position the needle correctly.

But for the chart, we’ll use a 3-part series to create the background:

Wait—here’s a better, more standard approach:

Use three values to create the gauge background:

Total = 100. This creates a semi-circle where the middle slice shows progress.

Also, add a separate cell for your actual value (e.g., 67%) and max value (e.g., 100), so you can dynamically update the chart.

2. Insert a Doughnut Chart

1. Select the three data values (Left empty, Progress, Right empty).
2. Go to Insert > Charts > Doughnut Chart.
3. Click on the chart, then go to Chart Design > Change Chart Type > Doughnut.
4. Right-click the outer ring and set Angle of first slice to 270° (this starts the chart at the bottom, like a speedometer).
5. Set the hole size to around 70% (adjust for visual appeal).

Now you should see a semi-circle with three colored sections.

3. Hide the Background Slices

To make it look like a gauge:

1. Click on the left (or right) segment—this is the empty space behind the progress bar.
2. Format that slice: Right-click > Format Data Point > change fill to No Fill or match the background color.
3. Do the same for the other empty side if needed.

Now only the "Progress" slice is visible, sitting inside a half-doughnut.

4. Add a Needle (Optional, Advanced)

To create a needle:

• Use an XY Scatter Plot overlaid on the doughnut.
• Add a data point that corresponds to the angle of your value.

Steps:

1. Create two helper cells for X and Y coordinates:
   • Angle = (Value / Max) * 180 → since it's a half-circle (0 to 180°)
   • Convert to radians: RADIANS(Angle)
   • X = SIN(radians)
   • Y = COS(radians)

Example formula:

X = SIN(RADIANS(Value/Max*180))
Y = COS(RADIANS(Value/Max*180))

2. Add this XY point to the chart:

   • Right-click chart > Select Data > Add.
   • Series X = X value, Series Y = Y value.
   • Change series chart type to Scatter with Straight Lines.

3. Format the scatter:

   • Line color: red or black, thick line (e.g., 3–5 pt).
   • Marker: none.
   • Make sure the chart is on a secondary axis and aligned.

This creates a rotating needle pointing to your value.

5. Add Labels (Optional)

• Add a data label to the doughnut slice to show the percentage.
• Or insert a text box in the center with a dynamic formula like ="67%" linked to your value cell.
• You can also add min, max, and target markers manually using text boxes.

Tips for a Better Look

• Use conditional colors: green for good, yellow for caution, red for bad.
• Keep the design clean—avoid too many colors or effects.
• Lock the axis and chart size if embedding in a dashboard.
• Update values dynamically using formulas or linked cells.

Limitations

• Excel gauge charts are visual aids, not analytical tools.
• They take up more space than simpler charts.
• The needle method requires some math and careful formatting.

But they’re great for dashboards when you want to highlight a single KPI like performance, completion, or target progress.

Basically, it’s a mix of illusion and formatting—but once set up, you can reuse it across reports. Not built-in, but totally doable with a doughnut and a little patience.

The above is the detailed content of How to create a speedometer or gauge chart in Excel. For more information, please follow other related articles on the PHP Chinese website!