Progress Doughnut Chart with Conditional Formatting in Excel

Bottom line: Learn how to create a progress doughnut chart or circle chart in Excel.  This chart displays a progress bar with the percentage of completion on a single metric. We will apply conditional formatting so that the color of the circle changes as the progress changes.  This technique just uses a doughnut chart and formulas.  It is pretty easy to implement.

Skill level: Intermediate

Progress doughnut charts have become very popular.  We see them on mobile apps, television broadcasts, sporting events, and financial reports.  The progress doughnut (circle) chart is a simple visualization that typically just displays one metric.

This makes it a great addition to any dashboard because the chart (graph) is easy to understand.  The reader can quickly see the percentage of completion towards a goal.

We can add the progress doughnut charts to our reports and dashboards in Excel too.  In this post, we'll take a look at how to create the chart, and also apply conditional formatting so the color of the progress bar (circle) changes as the percentage of completion changes.

Required Ingredients

The setup is pretty simple and just requires an Excel doughnut chart and a few formulas.  This technique will work in all versions of Excel including both Windows and Mac Editions.

Download the Excel File

Download the Excel file to follow along with the videos.

Video #1 – How to Create the Progress Doughnut Chart

Video #2 – How to Add Conditional Formatting to the Progress Doughnut Chart

When to Use the Progress Chart

The progress doughnut chart displays the percentage of completion on a single metric.  It can be used for measuring the performance of any goal or target.

The chart is a great addition to any dashboard because it is very easy to understand.  The reader can quickly see the progress of metrics that they are familiar with.

The conditional formatting makes it even easier to read because the changes in color alert the reader that a metric might need additional attention if it is not performing well.

How to Create the Progress Doughnut Chart in Excel

The first step is to create the Doughnut Chart.  This is a default chart type in Excel, and it's very easy to create.  We just need to get the data range set up properly for the percentage of completion (progress).

Step 1 – Set Up the Data Range

For the data range, we need two cells with values that add up to 100%.

1. The first cell is the value of the percentage complete (progress achieved).
2. The second cell is the remainder value.  100% minus the percentage complete.

This will create two bars or sections of the circle.  One for the percentage complete and one for the remainder.  We can fill these bars with different colors to display the progress complete in a bold color, and the remainder in a lighter tone or gray.

Both cell values are formatted as percents.

We can use the following formula to calculate the remainder value.

=100%-B2

If the maximum percentage complete value can go over 100%, then it's best to use the following formula for the remainder value.

=MAX(100%,B2)-B2

This will change the remainder value to zero if the progress is greater than 100%.  The entire chart will be shaded with the progress complete color, and we can display the progress percentage in the label to show that it is greater than 100%.

Step 2 – Insert the Doughnut Chart

With the data range set up, we can now insert the doughnut chart from the Insert tab on the Ribbon.  The Doughnut Chart is in the Pie Chart drop-down menu.

1. Select both the percentage complete and remainder cells.
2. Go to the Insert tab and select Doughnut Chart from the Pie Chart drop-down menu.
3. The doughnut chart will be inserted on the sheet.

Step 3 – Format the Doughnut Chart

Now we need to modify the formatting of the chart to highlight the progress bar.  The default chart will look something like the following.  Here are the steps to clean it up.

1. Remove the legend.  Select the legend and press the delete key.
2. Change the colors of the progress and remainder bars.
   1. Left-click on the progress bar twice to select it.
   2. Go to the Format tab in the ribbon and change the fill color to a bold color.
   3. Repeat steps 1 & 2 for the remainder bar, and select a light color or gray.

The doughnut chart should now look like more like a progress chart.  The last step is to add a label with percentage complete value.

I like to add a Text Box shape to the chart that displays the number in the middle of the circle.

1. Select the chart.
2. Go to the Insert tab on the ribbon and select the text box shape from the Insert Shapes menu.
3. Draw the text box inside the chart.  This will add the shape to the chart, and the shape will move and size with the chart.
4. Select the outside border of the text box.
5. In the formula bar, type the equal sign =, then select the cell that contains the progress value.  Press Enter.  This links the text box value to the cell value.  When the cell value changes, the text box value will automatically be updated to reflect the change.
6. Finally, change the formatting of the text box so the text color matches the progress bar color.

The width of the doughnut circle can also be changed by right-clicking the bar and selecting Format Data Series…  Then modify the Doughnut Hole Size property.

We should now have a progress doughnut chart that displays the percentage complete for a single metric.  This is great for actual vs budget, target, and goal oriented metrics.

Getting hungry yet? 🙂

Apply Conditional Formatting to the Doughnut Progress Chart

Now we might want to spiff up our chart with different colors to indicate each level of progress achieved.  This is called conditional formatting, and it makes it even easier for the reader to quickly see what level of performance has been achieved.

Here are the levels and colors we'll use for this example.

• Less than or equal to 75% is Orange.
• 76% to 95% is Blue.
• Greater than 95% is Green.

You can add as many levels as you'd like, and also change the percentages for each level.  I prefer to use three levels to keep the rules simple for the readers.

Step 1 – Set Up the Data Range for Multiple Levels

The first step is to create a cell that contains the progress value for each level.  In this example we have 3 levels, so we will create 3 rows of cells for the progress levels and 1 cell for the remainder value.

We use an IF formula to display the progress value in the row for its corresponding level/tier.  If the progress value is in the tier, then a number will be displayed.  If not, then the IF formula will return a blank.

This creates a separate bar for each level.  However, the bar will only be displayed if the progress level is within the tier.  Otherwise, a blank cell will be returned and the bar will not be displayed in the donut chart.

Step 2 – Insert the Doughnut Chart for All Levels

The next step is to extend the data series to include all the cells in the Data Series column.  This includes the 3 cells that contain the IF formulas and the remainder cell.

The doughnut chart will only display bars for cells that contain a value.  If the cell is blank, then a bar will NOT be displayed on the chart.

This is the trick to creating the conditional formatting.  When the actual value changes, the IF formulas will recalculate and show the value in the cell of the corresponding level.  The chart will also update to display that bar.  The Remainder bar will always be displayed on the chart, since the two visible values in the range add up to 100%.

Now we just need to change the color of each bar in each level.  Check out the part 2 video above for instructions on how to color each bar.

Step 3 – Apply the Formatting & Data Labels

Finally, we need to clean up the formatting.  This is the same basic process as step 3 above.  The only difference is that we create three separate text boxes, one for each level.  This allows us to change the color of each textbox to match the bar color.

The text boxes are linked to each cell in the data range for the chart.

Free Chart Alignment Add-in

My FREE Chart Alignment Add-in allows you to move the chart elements (titles, labels, legend) with the arrow keys and alignment buttons.  This can be very helpful and save time when building dashboards where we want the titles and labels to lineup perfectly between charts.

Are Your Dashboards Hungry for Doughnuts?

The progress doughnut chart makes a great addition to any dashboard or infographic.  It provides a simple summary of progress towards a goal.  We can then add other chart types to help identify trends and further analysis around these metrics.

What type of metrics will you use this chart for?  Please leave a comment below with suggestions or questions.  Thank you! 🙂