How to Create a Burndown Chart in Excel – 6 Steps

What Is a Burndown Chart?

A burndown chart plots the amount of work remaining versus the amount of time. Time is usually in the horizontal axis and the amount of unfinished work (or backlog) in the vertical axis.

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

The sample dataset contains 2 tables.

The first table showcases:

• Actual data: the original data assigned for 5 weeks.
• Chart formatting: a line chart based on the table.

The second table includes:

• Scheduled Hours:  the weekly time duration assigned to the departments.
• Completed Hours:  the amount of time used to complete parts of the project.
• Effort Left: Amount of time left to complete the whole project.
• Total Burndown: the time duration to complete the project before the deadline.
• W represents a week.

Step 2 – Tracking Sprint Timeline Using the SUM Function

Consider the 2nd table.

To build the burndown chart, calculate the totals of the elements in the Category header. Use the SUM function.

• 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 I5, enter:

• Press Enter or Tab.

• Drag down the Fill Handle to see the result in the rest of the cells.

• Total Burndown:  shows the optimal trend for how many hours per week you should dedicate to your project in order to meet the deadline.
• Efforts Left: determine the total hours left before the completion of the project. To calculate both Total Burndown and Efforts Left in the Start section, enter the formula in C14 and C15:

• Efforts Left:  the number of weekly hours assigned to the project. Add the Estimated Hours and divide them by the total number of weeks to get the resulting hours. Use the formula:

• Enter the formula in E11, F11, G11, H11, and I11 and press Enter.

• To count the completed hours each week, use:

• Drag the Fill Handle to see the result in the rest of the cells.

• To display the efforts left per week, enter:

• Drag the formula to the right.

• To calculate the total burndown for each week separately, enter:

• Drag down the Fill Handle to see the result in the rest of the cells.

Read More: How to Create Sprint Burndown Chart in Excel

Step 3 – Inserting an Excel Line Chart to Create a Burndown Chart in Excel

• Go to Table 2 and select B11:H14.
• Select Insert and choose Charts.
• Click Insert Line and Area Chart as shown below.

• Choose Line Chart.

• A line chart is displayed.

Step 4 – Entering the Estimated Hours into the Horizontal Axis

• Click the horizontal axis labels and right-click.

• In Select Data Source, click Edit.

• In the Axis Labels dropdown, select the cell array to display on the chart. Here, $C$10:$H$10.
• Click OK.

• The headers are displayed on the horizontal axis.

Step 5- Entering the Burndown Hours into the Secondary Axis

Modify the data series in Scheduled Hours and Completed Hours to a clustered column chart. It returns a data series in vertical columns.

• Right-click the line of either Schedule Hours or Completed Hours.
• Select Change Series Chart Type… .

• Choose Combo.
• Choose Clustered Column for Scheduled Hours.
• Check Secondary Axis.
• Do the same for Completed hours.
• Click OK.

• To reduce the size of the clustered columns, click the secondary axis labels and select Format Axis…

• In Axis Options select Bounds.
• Set the Maximum to 350.

Step 6 – Customizing the Burndown Chart

• Right-click either of the two lines.
• Select Format Data Series…

• Go to Fill & Line tab and select Color > Dash type > Begin Arrow type.
• In each type, choose an option and customize the chart.
• Do the same for the other line.

This is the burndown chart.

The Efforts Left line is showing over the Total Burndown line: the team is behind the deadline.

Download Practice Workbook

Download the practice workbook.

Related Articles

• How to Create Budget Burndown Chart in Excel
• How to Create a Burn-up Chart in Excel

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