How to Make a Progress Chart in Excel

Tracking progress toward a goal is fundamental to any project, campaign, or business objective, and nothing communicates that progress better than a single, clear visual. Instead of forcing your team or clients to scan a table of numbers, a progress chart shows them exactly where things stand in a split second. This guide will walk you through how to create three different types of effective progress charts in Excel, step-by-step.

What Exactly Is a Progress Chart?

A progress chart is a data visualization that shows how close you are to completing a goal. It typically compares the current value against a target value, often expressed as a percentage. Think of it like the loading bar for your project's success.

They are incredibly versatile and can be used to track just about anything with a defined endpoint, including:

• Quarterly sales targets
• Project completion percentages
• Budget spending and allocation
• Fundraising goals
• Event registration numbers

While there are endless ways to visualize this data, we'll focus on three popular and easy-to-create methods: the modern progress donut chart, the classic progress bar chart, and the detailed "waffle" chart. Each offers a different visual feel suitable for different types of dashboards and reports.

How to Make a Progress Donut Chart

The donut chart is perfect for dashboards and executive summaries. It's clean, modern, and provides an immediate, high-level view of a single, crucial KPI. The main feature is a clear percentage displayed in the center of the ring.

Step 1: Set Up Your Data

First, you need a simple table with two values: the percentage of the task that's complete and the percentage that remains. Let's say your project is 75% complete.

1. Open a new Excel sheet.
2. In cell A1, type "Metric." In cell B1, type "Value."
3. In cell A2, type "Complete." In B2, enter your progress, for this example, 75%.
4. In cell A3, type "Remaining." In B3, enter the formula =1-B2. This automatically calculates the remaining portion, so your chart will update dynamically as you change your progress in cell B2.

Step 2: Insert the Donut Chart

With your data ready, you can insert the chart.

1. Highlight your data range (from A1 to B3).
2. Go to the Insert tab on the Excel ribbon.
3. In the Charts group, click the icon for "Insert Pie or Doughnut Chart."
4. Select Doughnut from the dropdown menu. Excel will generate a basic donut chart. Now, it's time to refine it into a proper progress visual.

Step 3: Format the Chart

This is where we turn a generic chart into a clean progress indicator.

1. Remove any title and legend: Click the title and the legend one by one and press the Delete key.
2. Change the colors: Double-click the donut chart to open the "Format Data Series" panel. Click on one of the slices to select it individually. Go to the "Fill & Line" tab (the paint bucket icon). We want the "Complete" slice to be a strong color (like blue or green) and the "Remaining" slice to be a neutral, faded color (like light gray). Set the "Remaining" slice's color first - select it, go to Fill & Line > Fill, choose Solid fill, and select a light gray color. Then, repeat for the "Complete" slice but choose your primary brand color.
3. Adjust the Doughnut hole: In the "Format Data Series" panel, click the "Series Options" tab (the pie chart icon). Use the Doughnut Hole Size slider to set the thickness that best suits your needs. A value around 60%-75% usually works well.

Step 4: Add a Clear Percentage Label

A good progress chart is not complete without a clear and large percentage label in the center.

1. Insert a text box: Go to Insert tab > Text Box, and place it inside your donut chart.
2. With the text box selected, click inside it and type your formula in the formula bar, for example, =B2.

How to Make a Progress Bar Chart

The filled progress bar is another excellent and straightforward way to track progress. It's essentially a bar inside another bar, often used to show a value climbing towards a 100% target. Creating it involves a quick trick with stacked bar charts.

Step 1: Get Your Data Ready

Your setup will be identical to the donut chart. You need a "Complete" percentage and a "Remaining" percentage.

1. In your worksheet, set up two labeled percentages: "Complete" and "Remaining."
2. For your "Complete" value, enter your progress in cell B2, such as 75%. The "Remaining" will automatically be calculated as =1-B2.

Step 2: Insert a Stacked Bar Chart

With your data highlighted:

1. Go to the Insert tab, choose Insert Column or Bar Chart, and select 100% Stacked Bar Chart.

Step 3: Format the Stacked Bar into a Progress Bar

This is where the bar takes shape as a progress indicator:

1. Swap Rows and Columns: Click on your chart, go to the Chart Design menu, and click Switch Row/Column to change bar orientation to horizontal.
2. Remove titles and axes: Click on the title, the gridlines, and the axes, then press Delete to simplify the chart layout.
3. Change the colors: Similar to the donut chart example, change the "Remaining" bar to a faded background color and the "Complete" bar to a prominent color. Select each section of the bar, go to Fill & Line, choose Solid fill, and select a light gray for the "Remaining" bar and your chosen color for the "Complete" part.
4. Add Data Labels: Right-click on the bar, select Add Data Labels, then click on the label again to Format Data Label in the pane. Go to the Label Options tab and check Category Name. You can also format the text to enhance readability.

How to Create a Waffle Chart

Waffle charts provide an innovative way of displaying progress using a grid of squares, ideal for showing parts of a whole using a 10x10 grid representing 100.

Set Up Your Grid

1. In a new sheet, create a grid of 100 cells by adjusting the width and height to form squares. Select 10 columns and 10 rows, and set their dimensions to 2x2.
2. Fill this grid with numbers from 1 to 100 using the formula: =ROW(A1)*10+COLUMN(A1). Drag the formula across the entire 10x10 grid.
3. Enter your progress value in a separate cell (e.g., 75).

Apply Conditional Formatting

Create your waffle chart using Conditional Formatting to color the grid cells:

1. Highlight the grid. Go to Conditional Formatting > New Rule.
2. Use a formula to determine which cells to format. Enter the formula: =$A1<=ProgressValue, where ProgressValue is the cell containing your progress value.
3. Choose the fill color for these cells and click OK.
4. Hide the Numbers: Select your grid cells, change the font color to match the cell fill background, making the numbers invisible.

Best Practices for Effective Progress Charts

• Keep your charts simple and uncluttered to maintain visual clarity.
• Use contrasting colors to highlight the progress segments.
• Engage the audience by annotating the chart with text that explains what's being represented.
• Update Data: Ensure your data source is always accurate and updated regularly.

Creating progress charts in Excel - from the donut to the bar and waffle chart - is a valuable skill to enhance your project's dashboards. These visual aids convert numbers into insights at a glance. With these techniques, you can build clean, dynamic, professional-looking charts that convey meaningful content.

While creating these charts does take a degree of effort and skill, the payoff in terms of process management and data representation is immense. Rather than manually creating and updating spreadsheets, use tools like Google Sheets, Microsoft Excel, and others to streamline your dashboard creation and analytics, allowing you to present real-time updates clearly and efficiently.