Excel Xpert · Lesson 95b
A progress donut is the ring you see on every dashboard: it fills as work gets done, the percentage sits in the hole, and the color shifts from red to amber to green as the number climbs. Excel has no progress chart type, so all of it is built out of an ordinary doughnut chart and a small helper table. Four and a half minutes of video below, then the full build written out.
Lesson 95b · 4 minutes 45 seconds. The video builds a color changing progress donut chart from scratch, showing how the helper table drives the ring, how the remainder slice is hidden so only the completed portion shows, and how to make the color switch at your own thresholds. The written steps below cover the same build in detail, including the live percentage in the center and how to reuse the chart for other metrics.
The same free Excel Xpert companion workbook used in the video, one tab per lesson. No login, no email required.
There is no progress chart in Excel's chart menu, which is why this looks harder than it is. What you are really building is an ordinary doughnut chart with exactly two slices, one for the work completed and one for the work remaining, and then hiding the second slice so only the first is visible. The empty part of the ring is not empty at all. It is a slice you made invisible.
That is the whole trick, and everything else follows from it. Because the two values always add up to one whole ring, the visible slice is a direct picture of the percentage. Change the completed figure and the ring redraws itself.
The hole is what makes a doughnut better than a pie here. A pie can show the same proportion but has nowhere to put the number, and a progress indicator without a number forces people to estimate. Dropping a text box in the center that is linked to a cell gives you a live figure that updates with the data.
The color change is where people get stuck, because a single data point in one series can only have one color. Excel has no equivalent of conditional formatting for chart slices. The way around it is to split the value across several columns, one per color band, with formulas that put the number in exactly one of them and leave the rest empty. Each band gets its own fixed color, and since only one is ever populated, the ring appears to change color as the number crosses your thresholds.
Understanding that last point is what separates a chart you can rebuild and adapt from one you copied and cannot change. Nothing about the color is conditional. You are drawing several differently colored charts on top of each other and only one has any data in it at a time.
Six steps. One through five build a working donut, and step six is what makes it change color.
Off to the side of your data, set up two cells. One holds the percentage complete, the other holds the remainder as =1-B2. Label them so nobody deletes them later, because a helper table with no explanation is the first thing somebody tidies away.
Keep the progress value as a decimal formatted as a percentage, so 0.65 displaying as 65%, rather than typing 65 as a whole number. Mixing the two is the single most common reason a finished ring looks wrong: 65 alongside a remainder of 0.35 gives a ring that is 99.5 percent full.
If progress comes from real data rather than a typed number, calculate it here too, something like =COUNTIF(D:D,"Done")/COUNTA(D2:D200). Doing the arithmetic in the helper cell keeps the chart pointed at one stable location no matter how the source data moves around.
Select only the two value cells, then Insert > Pie or Doughnut Chart > Doughnut. Including the labels or a header row in the selection makes Excel guess at a series structure you do not want.
Delete the chart title and the legend immediately. A progress ring has one number and no categories worth naming, so both are noise. Click each once and press Delete.
Also remove the chart border and set the chart area fill to none, otherwise the ring sits inside a white box that will not blend into a dashboard background.
This is the step that turns a doughnut into a progress bar. Click the ring once to select the whole series, then click the remainder slice again to select just that one slice. The selection handles should be on one slice only.
With it selected, open Format Data Point and set No fill. The completed slice is now the only thing visible, sitting on nothing.
A pale grey instead of no fill is often the better choice. It gives you a visible track showing the full ring the progress is working toward, which reads more clearly at low percentages than a lone sliver floating in space.
Right click the ring, choose Format Data Series, and adjust Doughnut Hole Size. The default of 75 percent gives a thin ring; dropping toward 60 thickens it. Somewhere in the 60 to 75 range keeps the ring substantial while leaving room for a number in the middle.
Set the slice border to no line. The default thin white outline between slices is visible at the boundary between filled and empty and makes the progress edge look ragged.
If you want the fill to begin somewhere other than the top, Angle of first slice in the same panel rotates the whole ring. Leaving it at zero starts progress at twelve o'clock and fills clockwise, which is what people expect.
Insert a text box over the center of the ring. With the text box selected, click into the formula bar, type =, click the percentage cell, and press Enter. The text box now displays that cell and updates whenever it changes.
The order matters. Select the text box itself, not the text inside it, and type the formula in the formula bar rather than into the box. Typing directly into the box gives you static text that will be wrong by next week.
Center the text horizontally and vertically, size it large, and match the font color to the ring. Then group the text box with the chart, or set it to move and size with the chart, so the number does not drift off-center when the chart is resized.
Extend the helper table with one column per color band, say Red, Amber and Green. Each holds an IF formula that returns the progress value only when it falls inside that band, and an empty string otherwise.
Red might be =IF(B2<0.5,B2,""), amber =IF(AND(B2>=0.5,B2<0.8),B2,""), and green =IF(B2>=0.8,B2,""). Only one ever holds a number, so only one slice ever has anything to draw.
Point the chart at those three cells plus the remainder, then color each band's slice in its own color once. Nothing is conditional at the chart level. Three fixed colors, and the data decides which one has a value, which is why this survives being copied to another sheet where conditional formatting rules often do not.
Adjust the thresholds to match whatever your team actually treats as on track. The formulas are the only place the numbers 0.5 and 0.8 appear, so changing them is a two second edit.
| Step | What You Do | What Happens to the Chart |
|---|---|---|
| 1. Helper cells | B2 = 0.65 as a percent, B3 = =1-B2 | Two values that always total one full ring |
| 1. From live data | =COUNTIF(D:D,"Done")/COUNTA(D2:D200) | Ring tracks real completion, not a typed number |
| 2. Insert | Select B2:B3, Insert > Doughnut | A two slice ring, plus a title and legend to delete |
| 3. Hide remainder | Click ring, click remainder slice, No fill | Only the completed portion is visible |
| 3. Alternative | Pale grey instead of No fill | Empty track stays visible behind the progress |
| 4. Hole size | Format Data Series, 60 to 75 percent | Ring thickness set, room left for the number |
| 4. Borders | Slice border set to No line | Clean edge where progress meets empty |
| 5. Center number | Text box, then =B2 in the formula bar | Live percentage in the hole, updates with data |
| 6. Color bands | =IF(B2<0.5,B2,"") and one per band | Only the matching band holds a value |
| 6. Color each | Format each band's slice separately | Ring appears to change color at thresholds |
Rows 1 through 5 give a working progress donut in a fixed color, which is enough for most dashboards. Row 6 is the part worth understanding rather than copying: nothing about the color is conditional, you are simply making sure only one colored slice ever has data in it.
The formatting decisions that separate a dashboard ring from a default chart.
Right click the chart, choose Format Chart Area > Properties, and select Don't move or size with cells. Without it, filtering or resizing rows shifts and squashes the chart, which is especially obvious once there is a text box centered in it.
A doughnut in a rectangular chart area still draws a circle but leaves uneven margins, so the ring looks off-center. Set the height and width to the same value in Format Chart Area > Size.
If the ring sits on a colored dashboard panel, a grey track will not blend. Either use no fill so the panel shows through, or set the track to a slightly darker tint of the panel color.
Large, bold, and a color that works against your ring colors. If the ring shifts from red to green, a neutral dark grey number stays legible in every state, while a colored number has to be readable against all of them.
Once the basic ring works, these are small changes.
Build one small donut per metric and line them up rather than stacking concentric rings on one chart. Multiple rings look impressive and are genuinely hard to read, since the eye cannot compare arc lengths at different radii.
When the rings genuinely belong together, add a second series and Excel draws it as an inner ring. Keep it to two, label them clearly, and use color rather than position to distinguish them.
A ring cannot exceed a full circle. Cap the charted value with =MIN(B2,1) so the ring fills completely, and let the center text box show the true figure, so 112% appears in the middle of a full ring.
The same technique with a third slice worth exactly half the total, set to no fill and rotated so it sits at the bottom, produces a semicircular gauge. It is the same helper table logic with one more hidden slice.
65 in one cell and 0.35 in the other gives a ring that is almost entirely full. Keep progress as a decimal formatted as a percentage and calculate the remainder as one minus that value.
One click selects the series and setting no fill then hides the entire ring. Click once for the series, once more for the individual slice, and check the handles before formatting.
Text typed directly into the box is static and silently goes stale. Select the box, then build the reference in the formula bar so it stays linked to the cell.
Chart slices have no conditional formatting. The color change comes from several fixed color bands where only one holds a value at a time.
The copy stays pointed at the original cells, so two rings show identical numbers. Copy the helper block and the chart together, then repoint the formula.
Filtering or resizing rows drags and distorts the chart, and the centered text box ends up off-center. Set Don't move or size with cells once and it stays put.
The ring is the easy part. Keeping the number underneath it true is the work.
Free 14-day trial. No credit card required.
Build a two row helper table holding the percentage complete and the remainder, insert a doughnut chart from those two cells, then make the remainder slice no fill so only the completed portion is visible.
Insert a text box inside the hole, click it, then type an equals sign and click the cell holding the percentage. The text box then displays that cell and updates whenever the value changes.
A single series cannot change color by itself, so use one column per color band with an IF formula putting the value in only the matching band. Format each band's slice in its own color.
Right click the ring, choose Format Data Series, and adjust Doughnut Hole Size. A smaller percentage gives a thicker ring, and around 60 to 75 percent leaves room for a number in the middle.
By default the first slice begins at the top and fills clockwise. Angle of first slice in Format Data Series moves the starting point if you want the fill to begin somewhere else.
A doughnut is a pie with the center removed, and that hole is the reason it works for progress, because the number goes in the middle. Doughnut charts also support multiple concentric rings.
Either add more series to make concentric rings, or build one small chart per metric and line them up. Separate small charts are easier to read than three rings on one chart.
The remainder slice is still filled. Select that slice on its own and set it to no fill, or to a pale grey if you want the empty track visible behind the progress.
Use a decimal such as 0.65 formatted as a percentage, and calculate the remainder as one minus that value. Mixing 65 and 0.35 in the same helper table is a common cause of a wrong looking ring.
Not directly, since the ring is a whole and cannot exceed it. Cap the charted value with MIN and display the true figure in the center text so overperformance is still visible.
Right click the chart, choose Format Chart Area, open Properties and select Don't move or size with cells so filtering and row changes leave it in place.
Yes. Copy the chart and its helper cells together, then point the copied helper formula at a different source cell. Copying the chart alone leaves it linked to the original cells.
Every business starts with a spreadsheet. Updoot is where you scale past it, with progress that tracks itself instead of being charted by hand.
Start Your Free Trial →