How to Create a Burndown Chart in Excel

Tracking a project's progress is often easier said than done. With multiple tasks, moving deadlines, and unexpected hurdles, it's easy to lose track of whether you're ahead or behind schedule. A burndown chart is a simple yet powerful visual tool that makes this process incredibly clear. This guide will walk you through exactly how to build one step-by-step in Microsoft Excel.

What Exactly Is a Burndown Chart?

A burndown chart is a graph that shows the amount of work left to do over a period of time. It's a fantastic way to quickly visualize your team's progress and see if you're on pace to finish by the deadline. It's a staple in Agile and Scrum project management, but its simplicity makes it useful for anyone managing a project, from launching a marketing campaign to producing a series of blog posts.

Every burndown chart has two primary lines:

• Ideal Work Line: This is a straight line showing a perfect, consistent rate of progress from the start of the project to the end. It represents the path you'd take if everything went exactly as planned.
• Actual Work Line: This is the line that tracks the real amount of work remaining. It zigzags above and below the ideal line as your team completes work, giving you a real-time look at your actual pace.

By comparing where the 'Actual' line is relative to the 'Ideal' line, you can know in a five-second glance whether you're on track, ahead, or falling behind schedule. This early feedback loop is what makes it so valuable - it helps you spot problems early enough to fix them.

Setting Up Your Project Data in Excel

Before you can build the chart, you need to organize your project data in a simple table. This table will be the engine that powers your burndown chart. Open a new Excel sheet and create the following columns.

Step 1: Create a Data Table

Let's imagine you're managing a project to publish 20 new articles on your company blog over a 10-day period. Your table should have three core columns:

• Day: This column represents the timeline of your project. For our example, this would be Day 0, Day 1, Day 2, etc., up to Day 10.
• Ideal Remaining Tasks: This column represents the "perfect" burndown path.
• Actual Remaining Tasks: This column is where you will manually track the number of tasks your team actually has left at the end of each day.

Your blank table should look something like this:

Step 2: Calculate the Ideal Burndown Rate

First, fill in the Ideal Remaining Tasks column. On Day 0, you have all 20 of your tasks remaining. You need to finish these 20 tasks over 10 days, so your ideal "burn rate" is 2 tasks per day (20 tasks / 10 days).

Don't calculate this manually! Let Excel do the work.

1. In the first cell under "Ideal Remaining Tasks" (B2 for Day 0), enter your starting number of tasks: 20.
2. In the cell for Day 1 (B3), enter the formula to subtract the daily burn rate from the previous day's total. Since our burn rate is 2, the formula will be =B2-2.
3. Click on the small square at the bottom-right corner of cell B3 (the "fill handle") and drag it down to the last day of your project. Excel will automatically apply the formula down the column, creating your perfect burndown line from 20 to 0.

Step 3: Track Your Actual Remaining Tasks

This is the part that requires daily or regular updates. The Actual Remaining Tasks column is where you record how many tasks are truly left at the end of each day. This column won't be formula-driven because project progress is rarely predictable.

On Day 0, your actual remaining tasks are the same as your ideal: 20.

From Day 1 onwards, you'll update this column based on your team's real output. For example:

• End of Day 1: Your team was on fire and completed 3 articles. You update the cell to 17.
• End of Day 2: A meeting marathon slowed things down, and only 1 article was completed. You update the cell to 16 (17 - 1).
• End of Day 3: Back on track, the team finished 2 more articles. You update the cell to 14.

Continue this process for the duration of the project. Your data table might end up looking like this:

Creating Your Burndown Chart in Excel

With your data table ready, creating the visual chart takes just a few clicks.

Step 1: Select Your Data

Click and drag your mouse to highlight your entire data table, including the column headers (from cell A1 to C11 in our example).

Step 2: Insert a Line with Markers Chart

With your data selected, navigate to the Insert tab on Excel's top ribbon. In the Charts section, click on the icon for Line or Area Chart. From the dropdown menu, choose the Line with Markers option. This chart type is perfect for a burndown chart because the markers clearly pinpoint the data for each day.

Excel will instantly generate a basic chart on your spreadsheet.

Step 3: Customize and Refine Your Chart

The default chart works, but a few tweaks will make it much clearer and more professional.

Add a Chart Title and Axis Titles

• Double-click the default "Chart Title" and give it a descriptive name, like "Blog Content Project Burndown."
• To add axis titles, click on your chart, then click the plus sign (+) icon that appears on the right side. Check the box for Axis Titles. Now you can label the vertical (Y) axis "Tasks Remaining" and the horizontal (X) axis "Project Day." This context is essential for anyone reading the chart.

Format a Clean Y-Axis

Your Y-axis might show negative numbers or have an upper limit far above your starting task number. Let's fix that. Right-click on the numbers on the Y-axis and select Format Axis. In the pane that opens:

• Set the Minimum Bound to 0. You can't have negative tasks.
• Set the Maximum Bound to your starting number of tasks (in our case, 20). This makes the chart feel less empty and focuses the view on your actual burndown range.

Differentiate Your Lines

Make your chart easier to read by styling the two lines differently. Conventionally, the "Ideal" line is often styled to look less prominent.

• Right-click the "Ideal Remaining Tasks" line on the chart and select Format Data Series.
• Under the "Line" options, you could change the Dash type to a dotted or dashed line and set the color to a light gray.
• Now, right-click the "Actual Remaining Tasks" line. Make sure it's a solid line and pick a strong color like blue or orange to make it stand out.

How to Read and Interpret the Chart

Now that you have a beautiful chart, what is it telling you?

• If your Actual line is below the Ideal line: Congratulations, you're ahead of schedule! The team is completing work faster than planned. This could mean they're being extra productive or that the tasks were easier than estimated.
• If your Actual line is above the Ideal line: You're falling behind schedule. This is your cue to investigate what's causing the delay. Are tasks more complex than anticipated? Is there an unexpected blocker slowing the team down?
• If your Actual line goes flat (becomes horizontal): This is a major red flag. A flat line means for one or more days, no work has been completed. Something is preventing progress, and it needs immediate attention.

Advanced Tips for Better Burndown Charts

Once you've mastered the basics, here are a few ways to level up your burndown charts.

Use Story Points or Hours Instead of Task Counts

Not all tasks are created equal. One "task" might take 30 minutes, while another takes a full day. For more complex projects, tracking effort is more accurate than tracking the sheer number of tasks. Instead of "Tasks Remaining," you can use "Hours Remaining" or "Story Points Remaining" (if your team uses Agile Scrum methodology). The setup is exactly the same, but the resulting chart gives a much truer picture of progress.

Automate Your 'Actual' Task Count

Tired of manually counting remaining tasks every day? You can automate it with a simple formula. Create a second sheet in your workbook named "TaskList." On this sheet, list all your tasks in one column and their status ("Done" or "To Do") in the next.

Then, back in your main chart data table, you can replace the manual entries in the "Actual Remaining Tasks" column with a COUNTIF formula pointing to your task list:

=COUNTIF(TaskList!B:B,"To Do")

This formula automatically counts how many tasks have the status "To Do," giving you a live, up-to-the-minute burndown figure. Just refresh the sheet, and the chart updates.

Create a Reusable Template

If you're creating burndown charts regularly, save your finished file as an Excel Template (*.xltx). Go to File > Save As, and change the file type to "Excel Template." The next time you start a project, you can just open your template and plug in the new data without having to rebuild the chart and formatting from scratch.

Final Thoughts

Building a burndown chart in Excel is a straightforward process that provides enormous value for project tracking. With a simple table and a line chart, you create a clear visual dashboard that keeps your team aligned, communicates progress to stakeholders, and helps you identify potential problems before they derail your timeline.

While Excel is a great starting point, managing and updating it with live data from different platforms can become part of the manual report-pulling process you're trying to escape. Here at Graphed, we've solved that by taking the manual work out of creating project dashboards. We allow you to connect all your tools - like Asana, Jira, Salesforce, HubSpot, and others - and simply ask in plain English for the report you need. Your request instantly turns into a live-updating dashboard, so you spend your time acting on the data, not building charts.