How to Create a Gauge Chart in Excel

A gauge chart is one of the best ways to visualize progress toward a goal, but Excel doesn't offer one as a standard chart type. The good news is you can build your own beautiful and dynamic gauge chart with a clever combination of two other charts. This tutorial will walk you through the entire process, step-by-step, transforming a standard spreadsheet into a compelling performance dashboard.

What is a Gauge Chart and Why Use One?

Think of a gauge chart as a speedometer for your business metrics. It uses a needle or arc to show a value within a defined range, making it incredibly easy to see if performance is poor, average, or excellent at a single glance. They are perfect for tracking a single, crucial Key Performance Indicator (KPI) against a set target.

Common uses for gauge charts include:

• Tracking sales performance against a quarterly quota.
• Visualizing project completion percentage.
• Showing monthly budget spend vs. the total budget.
• Monitoring customer satisfaction scores against a target.

While they look complex, a gauge chart in Excel is just a layered combo of a Donut chart (for the color bands) and a Pie chart (for the needle). Let's build one.

Setting Up Your Data in Excel

Before creating any charts, you need to structure your data correctly. We'll need two small tables: one to define the colored bands of our gauge and a second to control the needle.

For this example, let's say we're tracking a sales goal where the current achievement is 75%.

1. Data for the Gauge's Colored Bands (Donut Chart)

First, decide on your performance ranges. A common practice is using a Red-Yellow-Green model to represent different levels of performance. For a goal of 100%, we might break it down like this:

• 0-50%: Poor (Red)
• 51-80%: Average (Yellow)
• 81-100%: Good (Green)

To create the donut chart, our data needs to represent these ranges. Critically, we also need to add a final value that represents the bottom half of the donut, which we will later make invisible. The sum of all these values should be 200 (to represent 200%, or the full circle).

Set up your table like this:

Note: The "Poor," "Average," and "Good" values must add up to 100. The "Empty Space" value is also 100, bringing the total to 200.

2. Data for the Needle's Position (Pie Chart)

Next, we need data for the needle. This table will have three parts:

• Value: The current performance value we are tracking (e.g., 75%).
• Needle Width: A very small number that represents the thickness of the needle pointer itself. A value of 2 or 3 usually works well.
• Remainder: The rest of the pie chart. The formula for this will be 200 - (Value + Needle Width).

Here’s how to set up that table. For automation, it's best to have a single cell (let's say F2) where you can input your KPI value. The rest of the table will update automatically using formulas.

With our data correctly set up, we're ready to start building the chart.

Step-by-Step Guide to Creating the Gauge Chart

Follow these steps carefully to assemble your gauge chart.

Step 1: Create the Donut Chart

First, we build the main body of the gauge using our colored band data.

1. Select the values for your color bands (in our example, the cells containing 50, 30, 20, and 100).
2. Go to the Insert tab on the Ribbon.
3. Find the Charts group and click on the Pie Chart icon.
4. Select Doughnut from the dropdown menu.

You’ll now have a basic, full-circle donut chart on your worksheet.

Step 2: Format the Donut Chart into a Gauge

Now, let's transform that donut into the familiar semi-circle shape of a gauge.

1. Click on the donut chart to select it. Then right-click and choose Format Data Series. A formatting pane will open on the right.
2. In the Series Options (the bar chart icon), change the Angle of first slice to 270°. This rotates the chart so that the large "Empty Space" slice is at the bottom.
3. Next, click specifically on the large bottom slice (the one representing the value of 100).
4. In the Format Data Point pane, go to the Fill & Line (paint bucket icon) tab. Under Fill, select No fill.

You now have the upper semi-circle of the gauge. Let's color the other segments appropriately.

1. Click on the first slice (representing "Poor"). In the format pane, select Solid fill and choose a red color.
2. Click the second slice ("Average") and color it yellow or amber.
3. Click the third slice ("Good") and color it green.
4. You may also want to select each colored slice and go to Border, choosing No line to remove the white lines between segments for a cleaner look.

Step 3: Add the Needle with a Pie Chart

This is where things get interesting. We're going to overlay a secondary chart to act as the needle.

1. Right-click anywhere on your existing chart and choose Select Data.
2. In the "Select Data Source" window, under Legend Entries (Series), click the Add button.
3. A small "Edit Series" window will pop up. For Series values, clear out any existing content (like ={1}) and select the cells containing your needle data (the cells for Value, Needle Width, and Remainder).
4. Click OK twice to close the windows.

Excel will add a second donut chart inside your first one. Don't worry, we'll fix this in the next step.

Step 4: Format the Pie Chart into a Needle

Now we convert that new donut series into a pie chart and format it to look like a single pointer.

1. Right-click the chart again and select Change Series Chart Type.
2. At the bottom, you'll see your two series listed (Series1 and Series2). Locate the series you just added for the needle (it's likely called Series2).
3. Change its chart type from Doughnut to Pie using the dropdown menu.
4. Crucially, check the box for Secondary Axis for this pie chart series. Click OK.

You'll now have a pie chart sitting on top of your donut chart. We just need to hide the unnecessary parts.

1. Right-click the pie chart and choose Format Data Series.
2. Like before, set the Angle of first slice to 270° so it aligns perfectly with the donut chart underneath.
3. Click on the largest slice of the pie (the "Remainder" section). In the format pane, under Fill & Line, set its fill to No fill.
4. Now click on the other large slice (the "Value" section) and also set its fill to No fill.
5. This leaves only the thin "Needle Width" slice visible. Click on this small slice, give it a Solid fill (black or dark gray is usually best), and add a Solid line border of the same color to make it look sharp.

Step 5: Add Finishing Touches

Your gauge chart is functionally complete, but a little cleanup will make it look professional.

• Remove the Legend: Click on the chart legend and press the Delete key. It's not serving a purpose here.
• Add a Title: Click the "Chart Title" and give it a descriptive name like "Q3 Sales Performance."
• Display the KPI Value Dynamically: You can add a text box that shows the actual KPI value and updates automatically.

Tips for Effective Gauge Charts

Now that you know how to build one, keep these best practices in mind:

• One Metric is Enough: A gauge chart is designed to communicate one key piece of information quickly. Avoid cluttering your dashboard with too many of them.
• Use Intuitive Colors: The Red/Yellow/Green (or Red/Amber/Green) color scheme is universally understood. Stick with it unless you have a compelling reason not to.
• Label Clearly: Make sure the chart title and any surrounding text clearly explain what the gauge is measuring, what the target is, and what the time period is.
• Context is King: A gauge chart showing "75%" is useful, but it's even more powerful when placed next to previous performance periods or industry benchmarks.

Final Thoughts

Building a gauge chart in Excel is a fantastic skill that boosts your dashboard-building abilities. While it involves several steps, the process is straightforward once you understand that you're just cleverly layering a donut and a pie chart to create a single, powerful visualization.

Of course, building these reports manually in Excel can become tedious, especially when you need to pull data from different platforms like Google Analytics, Shopify, or Salesforce a few times a week. With Graphed , we automate all of that manual work. You can simply connect your data sources once and use plain English to ask for the report you need, like, "Create a gauge chart showing this month's ad spend against our budget." We generate a live, interactive dashboard for you in seconds, saving you hours of spreadsheet work and letting you focus on the insights, not just the setup.