How to Make a Sprint Burndown Chart in Excel

A burndown chart is a tool from Agile, where all the “Stories” or items of work to be completed in a given iteration (a two-week time frame), with their accumulated points (how small or large each story is) are shown on a chart as the points are completed, from their total at the beginning to zero at the end of the iteration.

This video and article shows how to make a sprint burndown chart in Excel. Enjoy!

Step 1

First add the general framing and colours to your sheet:

Step 2

Next, fill out the backlog IDs, feature or story names, dates, and points as necessary.

Step 3

Now we can add in the formulas for Remaining Effort, Ideal Trend, and Points Completed This Sprint:

• Remaining Effort: First =SUM(D7:D18), then =D19-SUM(E7:E18)
• Ideal Trend: =D20-($D$20/COUNT($E$5:$N$5))
• Points Completed This Sprint: =SUMIF(E7:N11,”> 0″)

Step 4

Now we can create the burndown chart. Select Remaining Effort and Ideal Trend rows, go to Insert > Line chart.

And now you have a beautiful sprint burndown chart in Excel!