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**.

**Table of Contents**hide

## 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.

## 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 uses this chart. Additionally, project managers can identify the source of the wrongs and jump to the bottom of the problems with this chart.

## Step-by-Step Procedure to Create a Burndown Chart in Excel

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: Prepare Dataset 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 elements represent.

**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: Track 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
**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-C12`

- Furthermore, drag the formula cell to right.

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

`=C14-C11`

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

**Read More: How to Create Sprint Burndown Chart in Excel (with Easy Steps)**

### Step 3: Create Excel Line Chart

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
**Insert**tab and navigate to**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.

**Read More: How to Create Risk Burndown Chart in Excel (with Easy Steps)**

### Step 4: Insert 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: Push 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 perfectly.

- 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
**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 up to**350**. - Therefore, the top range of our primary axis is set to
**350**now.

### Step 6: Customize 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
**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, which means the team is behind the deadline.

## Conclusion

In conclusion, we have discussed some easy steps to create a **burndown chart** in Excel. Not to mention, our **ExcelDemy website** shows various simple problem-solving methods like this. Please leave any further queries or recommendations in the comment box below.