How to Create a Burndown Chart in Excel (with Easy Steps)

Microsoft Excel is a powerful software. We use Excel Charts for our educational, business, and other daily purposes. We build burndown charts for any project that makes observable progress over time. With this in mind, we will show you step-by-step procedures to create a burndown chart in Excel.

What Is a Burndown Chart?

A burndown chart plots the amount of work remaining versus the amount of time. Time usually runs along the horizontal axis, with the amount of unfinished work (or backlog) on the vertical axis. A run chart showing unfinished work is a burndown chart. Agile software development approaches like Scrum frequently use this chart. Additionally, project managers can identify the source of the wrongs and jump to the bottom of the problems with this chart.

In this tutorial, we will learn to build a burndown chart step by step from scratch. Since Microsoft Excel doesn’t support this chart by default, we will generate a dataset to develop the chart. The dataset will contain the necessary information and we will plot them in the chart. Furthermore, we will use the SUM function to calculate the time needed. To illustrate, the dataset contains 5 weeks of scheduled hours for a car-building company. Let’s go through the whole process step-by-step to create a burndown chart in Excel. For this demonstration, we have used Microsoft 365. Although, you can use any version prior to 2007 to implement this.

Step 1: Preparing Dataset to Create a Burndown Chart in Excel

Preparing a dataset to develop a burndown chart is as important as any other step. Firstly, we take a dataset that contains 2 tables. Let’s try to understand what each table element represents.

Actual data: It refers to the original data assigned for 5 weeks to the agile teams.
Chart formatting: We will generate a line chart based on this table formation.

Now, let’s go through the elements of the 2nd table.

Scheduled Hours: This summarizes the weekly time duration assigned to the agile departments.
Completed Hours: It particularly shows the amount of time used to complete some parts of the project.
Effort Left: Amount of time left to complete the whole project.
Total Burndown: The total burndown displays the time duration to complete the project before the deadline.
W represents a week.

Step 2: Tracking Sprint Timeline Using SUM Function

Let’s focus on the 2nd table. To build the burndown chart, we need to calculate the totals of the elements of the Category header. We will use the SUM function for the calculations. The SUM function adds up all the cells in a range and returns an integer value. Let’s jump right into it.

Hours Left: Calculate the total number of hours in 5 weeks and subtract it from the total estimated hours. To find the remaining hours left in cell I5, type:

=C5-(SUM(D5:H5))

Next, press the Enter or Tab button to see the left hour.

Now, to AutoFill the rest of the column, drag the formula cell down to I8.

Total Burndown: This category shows the optimal trend for how many hours per week you should dedicate to your project in order to fulfill your deadline.
Efforts Left: In this category, we determine the total hours left before the completion of the project. To calculate both Total Burndown and Efforts Left in the Start section, write the formula in C14 & C15:

=SUM(C5:C8)

Efforts Left: This displays the number of weekly hours assigned to the project. Add up the Estimated Hours and divide them by the total number of weeks to get the resulting hours. Therefore, type the formula:

=SUM(C5:C8)/5

Also, type the formula in E11, F11, G11, H11, and I11 and hit Enter.

Further, to count the completed hours each week, type:

=SUM(D5:D8)

Afterward, AutoFill the rest of the rows up to H12.

Similarly, to display efforts left per week, insert:

=C13-D12

Furthermore, drag the formula cell to the right.

Lastly, to calculate the total burndown for each week separately, write:

=C14-D11

Thus, you get the desired number in the display. AutoFill the rest of the range.

Step 3: Creating an Excel Line Chart to Create a Burndown Chart in Excel

Excel provides the line chart to display any trends versus the time assigned to it. This step will thoroughly show you how to convert your dataset into a 2-D Line Chart. To do so, follow the procedures carefully.

First, go to Table 2 and select B11:H14.
Afterward, take your cursor to the Insert tab and navigate to the Charts group.
Click Insert Line and Area Chart like shown below.

Consequently, a dropdown box will pop up.
Choose the Line Chart option.

As a result, a line chart appears in the dataset.

Step 4: Inserting Estimated Hours into Horizontal Axis

Like any other line chart, ours has a horizontal axis and a vertical axis. In this step, we will set up a timeline on the horizontal axis. Let’s plot the estimated hours on the horizontal axis. To change the axis labels, go through some important steps carefully.

First, tap the horizontal axis labels and right-click on them.
Subsequently, a context menu box will open up.

After that, the Select Data Source menu appears, click the Edit option box.
See the picture below to understand better.

Eventually, an Axis Labels dropdown box appears.
Here, we will select the cell array that we want to display on the chart.
Therefore, select range $C$10:$H$10.
Hit OK to complete the step.

Hence, the headers appear on the horizontal axis.

Step 5: Pushing Burndown Hours to Secondary Axis

Now, in this step, we will modify the data series of Scheduled Hours and Completed Hours to a chart named clustered column chart. A Clustered Column in Excel is a virtual column chart that returns data series in vertical columns. Further, this chart makes the comparisons of multiple data series easier to visualize and thoroughly understandable. Let’s follow these easy steps to make the transformation perfect.

To begin with, right-click on the line of either Schedule Hours or Completed Hours.
Subsequently, a context menu shows up on the display.
Here, select the Change Series Chart Type… option.

As a result, a dropdown box pops up.
Tap the Combo option.
Now, choose the chart type as Clustered Column for Scheduled Hours.
Also, tick the Secondary Axis box.
Similarly, do the same for Completed hours.
Finally, hit OK.

As the clustered columns take up a lot of space in the chart, we will take back the attention from them by reducing their size.
For this purpose, click on the secondary axis labels and press Format Axis…

Navigate to Axis Options and locate Bounds.
There, set the Maximum value to 350.
Therefore, the top range of our primary axis is set to 350 now.

Step 6: Customizing the Burndown Chart

In the final step of the article, we will add some finishing touches to our chart. Since we have two lines representing Efforts Left and Total Burndown, we will customize them to give the chart a better look. Let’s do it.

First, right-click on either of the two lines to open up a context menu.
Also, press Format Data Series…

Eventually, the Format Data Series dropdown box pops up.
Here, go to the Fill & Line tab and tap Color > Dash type > Begin Arrow type.
In each type, choose your desired options and customize the chart in your own way.
Similarly, do the same for the other line.

Hence, we get our desired burndown chart.
Since the Efforts Left line is showing up over the Total Burndown line, it means the team is behind the deadline.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.

Create a Burndown Chart.xlsx

Conclusion

In conclusion, we have discussed some easy steps to create a burndown chart in Excel. Please leave any further queries or recommendations in the comment box below.

<< Go Back to Burndown Chart in Excel | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!