How to create a burndown chart in Microsoft Excel – TechRepublic | Hot Mobile Press

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

Image: hasan/Adobe Stock

Whether you’re working with a team or alone, you need to keep a project on schedule. One tool that can keep you on track is a burndown chart created in Microsoft Excel. These are line charts that compare the estimated time taken to complete each task versus the actual time spent completing each task.

In this tutorial, I’ll show you how to create a burndown chart in Excel and explain what each row shows in terms of planning and meeting deadlines. Download the demo files.

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 maintaining a sustainable pace, and burndown charts can help with that. A burndown chart is a line chart that visually represents the remaining tasks versus the time remaining to complete. Using burndown charts, team members can view progress and adjust their efforts to meet their goals in the future.

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

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

How to prepare the data in Excel

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

Figure A

A burndown chart in Excel requires three columns.

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

  • Task 5 should take two days, but it spans a weekend and a holiday.
  • Task 4 should take a day.
  • Task 3 should take two days.
  • Task 2 should take two days, but will span a weekend.
  • Task 1 should take two days.

The number of days in the second column should total 14, and they do. The yellow mark indicates weekends and public holidays. By highlighting days you don’t work, you can avoid accidentally scheduling those days.

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

Now that you understand what the data means, let’s create 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 this, select the entire data set B2:D16 and proceed as follows:

  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 marker chart.

Figure C

This chart compares estimated and actual times.

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

As you fill in the “Actual” column, the chart updates accordingly:

  • The red line is above the blue line when you are behind.
  • The red line is constant with the blue line if you are on schedule.
  • The red line falls below the blue line when you are ahead of schedule.

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

Figure D

You’re still on schedule, even though you lost a day on Task 4.

Figure E

You finished on time.

They met the two-time limit for all five tasks, as shown in Figure Ebut you’re one day behind because you spent three days instead of two on Task 2. Luckily 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 check progress – or lack thereof. Your goal will be to keep the red line above the blue line, but most likely it will fall off and occasionally hit the blue line. That’s fine, but ideally you want to see the red line as far above the blue as possible.

If the red and blue lines converge too much, you should reallocate resources to keep the red line above the blue line. This allows for unplanned delays. When the lines converge, there is always a risk that you will default.

Use burndown charts to keep up with your schedule and intervene when needed to keep the project on schedule.

Leave a Comment