The burndown chart displays the amount of work still to be done, while the amount of time from the project’s start, representing the past and the future, is displayed on a horizontal axis. The burndown chart is visible to all members of the agile project management team and is constantly updated to ensure accuracy.
To create a burndown chart in Microsoft Excel, follow these steps:
- Open the Microsoft Excel spreadsheet you want to work with.
- On the left-hand side of the spreadsheet, click on the Tools tab.
- Click on the Chart Tools button.
- On theChart Tools tab, click on the Burndown Chart button.
- In the resulting window, you will be asked to select a data source for your burndown chart. You can either choose to use your own data or use one of our recommended data sources. We recommend using our recommended data source because it is easy to work with and has a lot of features that are useful for burndown charts. If you choose not to use our recommended data source, you can still create a burndown chart by following these steps:
- In the Data Source field, enter your own data or one of our recommended data sources that we have recommended for you in step 5 above.
- Click on OK to close the Chart Tools window and return to your spreadsheet main screen where you can continue working with your spreadsheet as usual!
Steps to create burndown chart in Microsoft Excel
Step 2: In the Data Analysis tab, under the Columns heading, click on the down arrow next to “Deal remaining work hours” and select “Average”. Step 3: In the Data Analysis tab, under the Rows heading, click on the down arrow next to “Actual remaining work hours” and select “Median”. The average and median of a data set show how many hours are in the middle of that data set. The average is calculated by adding up all of the values in a column and dividing that number by the number of values in that column. The median is calculated by taking the middle value in a column and dividing it by the number of values in that column. ..
Step 2: Beside the A10 cell, type this formula =SUM(C2:C9) into the cell C10. Press Enter key to get the total working hours. ..
Step 3: In cell D10, type the formula C10-($C$10/5). Then drag the autofill handle to fill the range you need.
Step 4: Enter the total working hours into cell C11 (which is beside the cell type “Actual remaining work hours”). ..
Step 5: Type this formula =SUM(D2:D9) into D11, and then drag fill handle to the range you need.
Step 6: Click on the Line button to insert a new line into the text.
Step 7: Right click at the blank line chart, and click Select Data in the context menu.
In the Edit Series dialog, click the Add button to open the Edit Data Source dialog. In the Series Name field, enter “Deal remaining work hours” and click the OK button.
Step 9: Click OK to go back and select the Data Source dialog. Step 10: Click Add button again, then add “Actual remaining work hours” as the second series in the Edit Series dialog.
Step 10: Click OK, go back to the Select Data Source dialog and click Edit button in the Horizontal (Category) Axis Labels section. In the Axis Labels dialog, select Range C1:H1 (Total and Dates labels) to the Axis label range box.
The burn down chart has been created.
You can click the chart then go to Layout tab and click Legend > Show Legend at Bottom to show the burn down chart more professionally.
Final Words
To create a burndown chart in Microsoft Excel, follow these steps:
- Open the Microsoft Excel spreadsheet you want to use for the burndown chart.
- On the left-hand side of the spreadsheet, click on the Tools tab and then click on Data Analysis.
- In Data Analysis, click on the Burndown Chart button.
- In the Burndown Chart dialog box, enter a name for your burndown chart and then click on OK.
- Click on OK to close the dialog box and then open your project again.