How to Create a Dynamic Progress Circle Chart in Excel

In this tutorial, I'll guide you through the steps to create a dynamic Progress Circle chart in Excel, perfect for tracking your budget or any other progress-based data. Follow along with the steps below, and you'll have a visually appealing chart that updates automatically as your data changes. Be sure and watch the video so you can follow along: How to Create a Progress Circle Chart in Excel.

If you want to follow along, you can download the Workbook here and open it in Microsoft Excel: https://docs.google.com/spreadsheets/d/1QYL7-w8DSSqFMDOwy9OHR4PtbrGOZbcL/edit?usp=drive_link&ouid=105753634361526165736&rtpof=true&sd=true

Step 1: Prepare Your Data

1. Open Your Spreadsheet: Start with a spreadsheet where you have your data. For this example, I'll use a spreadsheet to track gift spending.

2. Sum the Budget:

   • Select the cell where you want to sum your budget.

   • Go to the Home tab and click on Sum.

   • Ensure the range selected is correct and hit Enter.

3. Auto-Fill Formulas:

   • Select the cell with the sum.

   • Drag the fill handle (bottom right corner) across the adjacent cells to autofill the formula for the actual cost and over/under budget columns.

4. Format Cells:

   • Make these cells bold to stand out.

Step 2: Calculate Percentages

1. Create Percentage Labels:

   • Type "percent budget spent" and "remainder" in two cells.

   • Format these cells to be bold and aligned.

2. Calculate Percent of Budget Spent:

   • Select the cell for the percentage.

   • Enter the formula: = (Total Actual Cost / Total Budget Amount).

   • Press Enter.

   • Format this cell as a percentage using the Home tab.

3. Calculate Remainder:

   • Use a formula that accounts for over 100% budget: =MAX(100%,E7)-E7 where E7 is the cell where the & Budget Spent is calculated

   • Adjust the cell format to decrease the number of decimals.

Step 3: Insert the Progress Circle Chart

1. Select Data:

   • Highlight the cells with the percentages.

2. Insert Chart:

   • Go to the Insert tab.

   • Select Pie Chart and choose Doughnut.

3. Position and Format Chart:

   • Drag the chart to your desired location.

   • Delete the legend by selecting it and pressing Delete.

   • Rename the chart title to "percent of budget spent".

Step 4: Customize the Chart

1. Adjust Donut Hole Size:

   • Right-click on the chart and select Format Data Series.

   • Adjust the Doughnut Hole Size to 65%.

2. Change Colors:

   • Select the progress portion of the chart and change the fill color to represent the spent percentage.

   • Double-click the remainder portion and change its fill color to gray.

Step 5: Add Dynamic Text Box

1. Insert Text Box:

   • Select the chart.

   • Go to Insert > Text Box.

   • Draw the text box in the middle of the donut.

2. Link Text Box to Percentage:

   • Select the text box.

   • In the formula bar, type = and select the cell with the percent budget spent.

   • Press Enter.

3. Format Text Box:

   • Center-align the text.

   • Increase the font size, make it bold, and match the color.

Step 6: Advanced Formatting

1. Remove Outline:

   • Select the chart.

   • Go to Format > Shape Outline > No Outline.

2. Add Drop Shadows:

   • Select the chart, then Format Data Series > Effects > Shadow.

   • Choose an outer shadow preset.

   • Apply similar shadows to the text box and chart for consistency.

Step 7: Test and Adjust

1. Test Chart Updates:

   • Change the values in your actual cost column to see the chart update dynamically.

   • Ensure it correctly reflects percentages over 100%.

2. Conditional Formatting (Optional):

   • Create additional cells to display conditional colors based on percentage ranges.

   • Use IF statements to determine color ranges (e.g., green for <50%, yellow for 50%-75%, red for >75%).

Step 8: Final Touches

1. Final Adjustments:

   • Ensure all text and colors are consistent.

   • Test with different data to confirm functionality.

2. Save and Share:

   • Save your Excel file.

   • Share your newly created Progress Circle chart with your team or audience.

Now you know how to create a dynamic Progress Circle chart in Excel! This visual tool is perfect for tracking budgets, project progress, or any other metric over time. If you found this tutorial helpful, be sure to share it with others. For more tips and resources, visit the tutorials on my website.

Thanks for reading, and happy charting!