How To Create A Burndown Chart In Excel

A Burndown Chart is used to visualize the work remaining in the time available for a Sprint.

If your Project Management tool suite does not include an easy method for tracking your Effort Points and generating a Burndown Chart out-of-the-box, you may want to generate your own using Microsoft Excel.

This blog post will show you an example of how we generate Burndown Charts at Coria.

Sprint_Burndown_Chart_Template.xlsx_-_Excel000240.png

Step 1: Create the Shell of Your Burndown Template in Excel (or Just Download Ours)

Your Burndown Chart should include columns to track:

Your list of Product Backlog Items (PBIs) - The PBIs should be groomed, prioritized by your Product Owner, and listed in order of importance.
The number of "Effort Points" for each PBI - Your team should determine the number of Effort Points for each of these PBIs using an arbitrary scale (like a modified Fibonacci sequence). When you first create your Excel template, just leave these values blank until you've assigned Effort Points during Sprint Planning using a technique like Planning Poker.
The number of working days available in the Sprint - We like to include the calendar date as well as the count. Your spreadsheet should skip calendar dates for all non-working days (example, Day 1 = July 11th and Day 6 = July 18th).

Short on Time?

Get a blank Burndown Chart template to help kickoff your next Agile Sprint.

DOWNLOAD NOW

Step 2: Add a Spot to Total Your Effort Points

Your Burndown Chart should include rows for:

Actual Remaining Effort (by day)
Ideal Trend of Remaining Effort (if spread equally across each available day)

The Remaining Effort is calculated by getting the sum of all the Effort Points completed on a specific day in the Sprint and subtracting that total from the number of Effort Points remaining.

Example Excel Formula:

=E13-SUM(F6:F12)

Where:

Cell E13 = Total Effort Points for all remaining PBIs on the previous day
Cells F6 through F12 = Total Effort Points completed on a specific day in the Sprint

The Ideal Trend of remaining effort is calculated by first dividing the number of total points by the available number of working days, and then subtracting that from the total remaining Effort Points for each day.

Example Excel Formula:

=E14-($E$13/10)

Where:

Cell E13 = Total Effort Points for All PBIs
Cell E14 = Total Effort Points from the Previous Work Day
10 = Number of Work Days in the Sprint

Step 3: Create a Chart to Track Your Progress

Your Burndown Chart should also include a graph that shows:

The actual trend of work completed over time
The ideal trend work completed over time

This chart will allow you to see, at-a-glance, whether you are on target to complete the work items you've committed to in the current Sprint.

Sprint_Burndown_Chart_Template.xlsx_-_Excel000238.png

The chart will quickly show you when and how much churn is introduced during the Sprint. For example, if new Product Backlog Items are slipped in part-way through the iteration, your trend line will show an obvious blip:

Step 4: Enter Your Effort Points in the Initial Estimate Column

Once your team has determined an estimate of the number of Effort Points for each PBI, enter the value as a positive integer in the column you've added for tracking your initial estimates.

Make sure that you only include PBIs and Bugs that your team has committed to working on during the current Sprint.

Total up these Effort Points at the bottom of the spreadsheet. You will use this initial total to calculate your Remaining Effort and Ideal Trend.

Step 5: Track Your Progress Daily (and Honestly)

When a work item (PBI or Bug) is completed, enter the number of Effort Points it was estimated at during Sprint Planning in the column for the day on which it was completed. This should be a positive integer that matches the value listed in the Initial Estimate column of your spreadsheet for the work item.

It's worth noting that an item must be developed, tested, and validated before it'sreally 100% complete and any completion points are awarded. It doesn't count that a developer finished coding unless a QA person verifies that it works.

You're not allowed to cheat so that you can make your chart look nicer!

Step 6: Make Sure to Account for Change

If everything always went according to plan, you would never add any new work items after Sprint Planning. If you work in a dynamic environment with evolving priorities and demanding stakeholders, this might not always be the case.

You may also want to track the addition of new bugs that are discovered during the Sprint to gather a more accurate picture of how much work your team can accomplish in a given Sprint.

To account for any added PBIs or Bugs that are introduced during a sprint, enter the estimated number of effort points using a negative number on the day the item is accepted into the Sprint.

Visually highlight any items that were added after Sprint Planning by adding them to the bottom of the list. Change the background color of the Initial Estimate cell and leave the contents of the cell blank.

For example, if a new Product Backlog Item with an estimated effort of 3 points is added to the Sprint on Day 3, change the background color of the Initial Estimate cell to orange and add a -3 value to the cell that lines up with Day 3:

Sprint_Burndown_Chart_Template.xlsx_-_Excel000237.png

We also change the background color of the corresponding Effort Points cell so that it's easy to see when new items were added:

Sprint_Burndown_Chart_Template.xlsx_-_Excel000235.png

You can optionally include a color-coded value to reflect projected completion dates if you'd like to do some what-if analysis before the end of the Sprint.

Step 7: Analyze Your Team's Velocity

Keep a running tally of how many Effort Points you complete over time to track your progress. As your team works together for multiple Sprints, it will be easier to:

Determine how many Effort Points your team can safely commit to in a given Sprint
Show stakeholders the impact of adding new work items during an active Sprint
Identify trouble areas with your work items (for instance, if your burndown chart shows that everything is completed at the very end of the Sprint, it may indicate that your PBIs should be broken out into smaller, more manageable chunks)

Your velocity will increase over time as you work together as a cohesive unit and become more familiar with the technical challenges specific to your environment. In addition to helping pinpoint opportunities for improvement for each individual Sprint, keeping an historical record of your Burndown Charts is an excellent way to quantify and show off your progress.