How to create a burndown chart in Microsoft Excel

Try this easy-to-implement Microsoft Excel chart to keep you and your team members on track.

Whether working with a team or alone, you need to maintain a project’s schedule. One tool that can keep you on track is a burndown chart created in Microsoft Excel. These are line charts that compare the amount of estimated time to complete each task with the time actually used to complete each task.

In this tutorial, I’ll show you how to create a burndown chart in Excel and discuss what each line shows in regard to scheduling and meeting deadlines.

SEE: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions of Excel.

What is a burndown chart?

The key to effective time management is to maintain a sustainable pace, and that’s where burndown charts can help. A burndown chart is a line chart that visually represents the remaining tasks versus the amount of time left to complete. Burndown charts allow team members to view progress and adapt their efforts to meet their goals, going forward.

Perhaps initial expectations were unreasonable. When this is the case, personnel can reassess priorities and make the necessary changes before things get out of hand.

These charts can help your team meet deadlines, but they are a warning or a “clear sailing for now” message. The key is to report frequently and reallocate resources as necessary.

How to prepare the data in Excel

A burndown chart is easy to create in Excel, but setting up the data takes a good understanding of what the chart represents. You’ll need three columns, as shown in Figure A. As you can see, the data represents your goal to complete all five tasks within two weeks.

Figure A

A burndown chart requires three columns in Excel.

The first column contains the dates. The second column estimates the number of days each task should require. You’ll start with the total number of tasks, five, and end with one:

• Task 5 should require two days but stretches across a weekend and a holiday.
• Task 4 should require one day.
• Task 3 should require two days.
• Task 2 should require two days but stretches across a weekend.
• Task 1 should require two days.

The number of days in the second column should total 14, and they do. The yellow highlight denotes weekends and holidays. Highlighting days that you won’t work helps avoid scheduling those days accidentally.

The third column is a countdown of what you’ve completed each day. You completed task 5 in one day instead of the two you scheduled. That means you’re a day ahead of schedule.

Now that you understand what the data means, let’s build the chart.

How to create the chart in Excel

You’re ready to create the chart by basing it on the three-columns of data shown earlier in Figure A. To do so, select the entire data set B2:D16 and do the following:

1. Click the Insert tab.
2. In the Charts group, click the Insert Line or Area Chart option and choose Line With Markers (Figure B).

Figure B

Choose a line with markers chart.

Figure C

This chart compares estimated and actual times.

As you can see in Figure C, the blue line charts the estimated number of days. The red line represents the actual days per task. The two lines start together on July 1 and continue together through July 4. On July 5, you start task 4 a day ahead of schedule, so the red line dips below the blue line.

As you complete the Actual column, the chart updates accordingly:

• The red line is above the blue line when you are behind.
• The red line is steady with the blue line when you are on schedule.
• The red line dips below the blue line when you are ahead of schedule.

Figure D shows the red line heading back to the blue because you took two days instead of one to complete task 2. You’re still on schedule but you’re no longer a day ahead.

Figure D

You’re still on schedule, despite losing a day on task 4.

Figure E

You finished on time.

You met the two-deadline for all five tasks, as shown in Figure E, but you did get behind a day because you spent three instead of two days on task 2. Fortunately, you made up time and completed task 1 in one day.

How to use a burndown chart created in Excel

With a quick glance, you can review progress — or lack thereof. Your aim will be to keep the red line above the blue line, but most likely it will dip down and meet the blue line occasionally. That’s okay, but ideally, you’ll want to see the red line above the blue, as much as possible.

If the red and blue lines run together too much, consider reallocating resources to keep the red line above the blue line. Doing so will allow for unplanned delays. When the lines run together, you’re always in danger of falling behind schedule.

Use burndown charts to keep up with your schedule and intercede as necessary to keep the project on schedule.