Progress Doughnut Chart with Conditional Formatting in Excel - Excel Campus
39

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 Chart with Conditional Formatting 640x360

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.

Progress Doughnut Chart With Conditional Formatting.xlsx (45.0 KB)

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.

Add Progress Doughnut Charts to a Dashboard

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%.

Data Range Setup for Progress Doughnut Chart

  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.

Insert the Progress Doughnut Chart in Excel

  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.

Progress Doughnut Chart Formatting and Text Box Label

  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.

Change the Size of the Doughnut Hole in the Chart

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.

Data Range Setup for Progress Doughnut Chart with Conditional Formatting

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.

Insert the Progress Doughnut Chart with Conditional Formatting

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.

Text Box Label for Each Progress Level in the Doughnut Chart

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.

Align Chart Titles to Top Left Corner

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! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 39 comments
Julio Rodrigues - November 10, 2017

Awesome! Very clear and detailed explanation. Really solved my problem,
creation of a visual dashboard for my indicators. Thanks!

Reply
problem - September 2, 2017

Greetings! Very useful advice wіtһin this post! It’s
the little changes which will make tһe biggest changeѕ.
Thanks for sharing!

Reply
sree - August 21, 2017

Hello Jon, Thanks for explaining this function.
How to complete below conditional formatting? This is partially explained in video2 to setup specific colour for specific value. But how to ensure the colour changes in the below ranges.
• Less than or equal to 75% is Orange.
• 76% to 95% is Blue.
• Greater than 95% is Green.

Reply
Suresh - May 30, 2017

A really impressive and value adding tip ! Thanks for sharing Jon! You’re doing a great job !

Reply
John - April 6, 2017

Hi Jon , nice tips here and explained very well. A little question. When I create the Progress Doughnut and make the Actual greater than 100% the text box shows the real value i.e 105% Is there any way around this? That is, when the actual is less than 100% the text box displays the actual , but if the actual is greater than 100% the text box is empty?

Reply
    Jon Acampora - April 10, 2017

    Hi John,
    The textbox will still display the percentage over 100%. It does this in the example file. However, the chart really doesn’t display this well. That is why I added the formula to make the remainder amount 0% for anything over 100% progress. I hope that helps answer your question.

    Reply
Abode - April 5, 2017

Thank you a lot Jon i love so much 🙂

Reply
charles - April 4, 2017

Just what i was looking for to show in my data.

Reply
Usha - March 30, 2017

Jon : I appreciate very impressive way of adding valuable Infor by way of donut chart.

Please help me how to incoperate Slicers to navigate the donut chart values by selecting a month etc.

January 35% 65%
February. 10% 90%
March. 40% 60%

Can I use donut chart for data series as above.

I appreciate all your tutorials . Thanks a million.

Reply
Jake - March 29, 2017

I learned something new today. Thank you every much!

Reply
Rob Vermelis - March 27, 2017

Great video! Thanks for the helpful tips.

Reply
Mahmoud - March 26, 2017

Hi, thanks for videos, but one issue with textbox is not supported in browsers.

do you have solution for it.

Reply
    Jon Acampora - March 26, 2017

    Hi Mahmoud,
    Great question! The Excel Web App does not support those text box shapes yet. We can use the built-in data labels for the chart instead. The label for the Remainder bar can be deleted by left clicking on the label twice, then pressing the delete key. That just leaves the data label for the actual progress amount. Here is a screenshot.

    Progress Doughnut Chart Data Labels

    The only drawback here is that the label will appear on top of the bar. You can drag it to the center, but it does change positions as the progress % changes. So you might want to leave it over the bar if the actual progress % is going to change with some type of interactive control on the dashboard. Here’s a screenshot of what it looks like on the Web App (Excel Online).

    Progress Doughnut Chart Data Label Web App

    I think it still looks pretty good with the data label. Let me know if you have any questions.

    Reply
      Mahmoud - March 26, 2017

      Hi,

      Thanks for your reply, it is a good solution but still, sometimes you need to add in the center. added in the bar will not satisfy the customer 🙂

      Reply
        Jon Acampora - March 27, 2017

        Hey Mahmoud,
        I totally understand. 🙂 Another option is to change the Fill color of the chart AND plot area to “No Fill”, then use a cell for the data label. The chart will be placed over the cell that contains the data label. The cell will just be a formula that references the cell with the actual value (=B2). Here is a screenshot. I have the cell selected in the screenshot to show the formula, but once you change the selection to another cell, the chart will look just like the original.

        Then you can apply conditional formatting to the cell so the font color changes as the progress value changes. Hopefully that satisfies the customer… 😉

        Reply
          Mahmoud - March 27, 2017

          Thanks for quick reply, I have done that before also but it has some limitation in designing. but it is still better that nothing.

          thanks and keep it up.

          Reply
Peter Buyze - March 26, 2017

Jon, a little gem !! I shared it on G+ here https://plus.google.com/+PeterBuyze/posts/VnngQR1k7JY.

Reply
Firas - March 25, 2017

Thank you for making Excel so easy to implement

Reply
bhushan - March 25, 2017

thnx Jon for valuable information

Reply
Muzzafar - March 24, 2017

You are a champ Jon…

Reply
BerniceS - March 24, 2017

Hi Jon, Thanks for another good video. Question on the formulas for the different levels. For level 1, would you not want to make sure that it would not go below 0%? The same would be for level 3, would you not want to ensure it does not exceed 100%? or am I missing something? Was that because this was just a simple example?

Reply
    Jon Acampora - March 29, 2017

    Hi Bernice,
    That depends on the metric you are calculating or measuring. If your percentage of completion could be less than 0%, then you could add another level to account for that. This chart is going to work best for progress between 0 and 100%. Anything over 100% will show in the label and the full circle will be shaded with the green color.

    Reply
ben mosbeh Mohamed Slim - March 24, 2017

Thanks for sharing, amazing chart tip. Very useful for showing any project progression

Reply
Jim Berglund - March 24, 2017

John, this exercise suggested an idea. I’d like to have a chart looking at the minute-by-minute stock price for a few (5?)stocks. It would have the day opening price, high, low and would allow for alerts at an upper and a lower boundary for each stock. Is there an easy way to do that, assuming access to the data feed? Do you think it would be of general interest to your audience?

Reply
Walter E. - March 24, 2017

Sr. Acampora, reciba usted mis reconocimientos por su excelente capacidad para la enseñanza a traves de videos !!!!!

Un Abrazo !!!

Reply
DEBRA - March 24, 2017

Great video, thanks for sharing. I will put this in my Library for future use.

Reply
Tim - March 24, 2017

Jon, this is awesome! Thanks for sharing!

Reply
Mel Orecklin - March 24, 2017

Great video. I learned several new tricks that will become part of my Excel skill set. For example, I’ve never used Ctrl-D to duplicate an figure or used conditional formatting with a graph.

Thank you Jon.

Reply
sam sammito - March 24, 2017

Good Job Jon….Thank You

Reply
Indzara - March 24, 2017

Nice trick with the conditions to change colors. I am sure I will use this often. Thanks for sharing.

Reply
Caleb - March 24, 2017

Jon,

Thank you for the video! A couple people I create reports for will love the progress doughnut chart. A simple yet powerful visual tool.

Video was well done.

Thanks again,
–Caleb

Reply
Steve - March 24, 2017

Jon, Thanks, another great job explaining Excel functions, keeping it simple and to the point. Have a great day!

Reply

Leave a Reply: