How to Create a Burndown Chart in Excel

Step-by-step

A burndown chart plots the work left to do against time, so you can see at a glance whether a task is ahead of or behind schedule, and predict when it will be finished. Remaining work goes on the vertical axis and days along the horizontal.

1. Set up the schedule

You need two pieces of data for each day: the planned hours and the actual hours worked. The example task runs 15 days (starting from day 0), with 8 planned hours a day, so 120 planned hours in total. It assumes the actual total is also 120.

Add three calculation columns: Remaining planned, Remaining actual and Done today.

2. Calculate the remaining hours

• Remaining planned: start at the total and subtract each day’s planned hours. After day 1 that’s 120 − 8 = 112. Use absolute references for the total so the formula copies down correctly.
• Remaining actual: use IF so that a day with no hours entered shows not available instead of a number, for example =IF(D6="",NA(),previous remaining − D6). A #N/A keeps the line from dropping to zero on days you haven’t worked yet.
• Done today: the hours actually worked that day.

3. Insert a line chart

Select the Remaining planned and Remaining actual columns and go to Insert > Line Chart. Use the Day column for the horizontal axis labels.

Reading it: the planned line is a straight diagonal (8 hours a day). Where the actual line sits above it, the project is behind schedule, with more work left than planned. Where it sits below, you’re ahead.

4. Start the axis at day 0

Right-click the chart, choose Select Data, click Edit under the horizontal axis labels and select the days including day 0. Delete any extra line you don’t need, and the title.

5. Add the daily hours as columns

Right-click the chart, choose Select Data > Add, name the series Done Today, and select its values (G4:G20 in the example). Then right-click the new series, choose Change Series Chart Type and set it to Clustered Column.

6. Finish

Remove the gridlines, adjust the colours and add a legend if you need one. Your burndown chart is ready.