How to Make a Gauge Chart in Excel for a KPI Target

A gauge chart in Excel shows one number against a target as an arc, like a speedometer. Excel has no gauge chart type, so you build one from a doughnut chart with three helper cells and hide the bottom half. This guide walks through it step by step, uses MIN to keep the arc at 100% or less, and uses IF to colour it by status. It suits a KPI sheet or dashboard where one number matters most.

What a gauge chart shows#

A gauge chart shows how far one value has got towards a target. The filled part of the arc is the progress, the empty part is what's left, and a label in the middle gives the exact figure.

All the examples use this small KPI table:

Sales are 84,000 / 120,000 = 70% of target. That 70% is what the gauge will show. If percentages are new to you, the guide to the percentage formula explains the divide-then-format step.

A gauge works best for one KPI. To compare several values, a bar chart is easier to read, as the chart basics guide explains.

Step 1: Set up the gauge data#

The gauge needs three cells: the achieved share, the remaining share and a hidden half. Add them below the KPI table:

Here is why each cell is there:

• Achieved divides sales by target. MIN returns the smaller of its inputs, so if sales go over target, the result stops at 1 (100%) and the arc is simply full. The MIN function guide covers the syntax.
• Remaining is whatever is left of 100%.
• Hidden half equals achieved plus remaining, which is always 1. In a doughnut, a slice as big as all the others put together fills exactly half the ring. That half becomes the invisible bottom of the gauge.

Format B4:B6 as percentages (Windows: Ctrl+Shift+%; Mac: Control+Shift+%).

Step 2: Insert a doughnut chart#

Select only the three values, B4:B6, so Excel draws three slices.

1. Go to Insert > Insert Pie or Doughnut Chart and pick Doughnut.
2. Delete the chart title and the legend: click each one and press Delete.

You now have a full ring with three slices: 70%, 30% and a big 100% slice that takes up half the ring. The doughnut chart works like the pie chart, and the guide to pie charts in Excel covers the shared settings.

Step 3: Rotate the ring and hide the bottom half#

The trick is to turn the ring so the hidden slice sits underneath, then make it invisible.

1. Right-click any slice and choose Format Data Series.
2. Under Series Options, set Angle of first slice to 270°. The first slice now starts at the 9 o'clock position, so achieved and remaining fill the top half, and the hidden slice fills the bottom.
3. Set Doughnut Hole Size to about 50–65% for a thicker or thinner arc.
4. Click the bottom slice once to select the series, then once more to select only that slice. Under Fill, choose No fill, and under Border, choose No line.
5. Colour the achieved slice in a strong colour and the remaining slice in light grey.

The chart is now a half circle that fills from left to right. Drag the chart's bottom edge up to trim the empty space.

Step 4: Show the number in the middle#

A gauge needs a clear number, and a linked text box keeps it up to date. First, build the label in a cell. In B7:

=TEXT(B2/B3,"0%")&" of target"

This shows 70% of target. It uses the real ratio, not the capped one, so a result of 115% still says 115%.

Then link a text box to it:

1. Go to Insert > Text Box and draw a box in the middle of the arc.
2. Click the border of the text box so the box itself is selected.
3. Click in the formula bar, type =B7 and press Enter.

The text box now shows whatever B7 shows. Change sales to 96,000 and the arc and label both move to 80%.

Colour the gauge by status with IF#

You can't set a rule on a chart slice's colour, but you can split the achieved value into two slices and let IF decide which one gets the number. Replace row 4 with two rows:

Only one of rows 4 and 5 is ever above 0. Colour row 4's slice green and row 5's slice amber, and the gauge changes colour by itself when sales cross 90% of target. A slice of 0 doesn't show, so you never see both. Move the label formula down to B8 and link the text box to it. The IF function guide explains the test, value-if-true and value-if-false parts.

For a text status next to the chart, a plain IF is enough:

=IF(B2>=B3,"Target met","Below target")

With 84,000 against 120,000, it shows Below target.

When to use a gauge chart, and when not to#

Use a gauge chart for a single headline KPI against a target, such as sales to date, survey completion or budget used. It's quick to read in a meeting.

Don't use a row of gauges to compare many numbers. Each gauge takes a lot of space, and people can't compare arcs as accurately as bar lengths. For a full dashboard, a KPI summary sheet with clear numbers, or a sales dashboard with bar charts, usually works better. Some dashboards add a needle by putting a small pie chart over the doughnut; it looks impressive but takes far longer to build and doesn't add information.

Gauge charts also hide the size of the gap. If the target matters, show the actual and target numbers next to the gauge.

Practise it#

The gauge is only as good as the numbers behind it. Practise percentages and percent change in a live spreadsheet to get the ratios right, then practise the IF function to drive status labels and colours. Each lesson checks your formula as you type, and the exercise on this page builds the capped value that feeds the gauge.