Creating Burndown Charts in Google Sheets

A burndown chart is a simple chart that is used to visually represent completed tasks versus due tasks. This chart is used for agile project management to visually represent remaining work against time. In this article, we will show how to create burndown charts in Google Sheets.

Step 1: Prepare Your Data

Let’s consider a simple sprint timeline with ideal burndown and remaining tasks. To create a burndown chart, you need to list the following columns in Google Sheets.

• Date: The timeline for your project.
• Ideal Burndown: The total number of tasks that need to be completed.

For sprint task allocation you can use the following formula to determine the ideal burndown.

Formula:

=B2-($B$2/9)

The formula calculates the ideal burndown by subtracting a fixed daily decrement from the previous day’s value.

• $B$2: Refers to the initial value (100 in this case), made absolute with $ so it doesn’t change when copied to other rows.
• $B$2/9: Divides the initial value (100) by 9, representing the equal daily decrement over the 9-day burndown period.

• Remaining Tasks: The number of tasks left to complete at each point in time.

Step 2: Insert the Burndown Chart

• Select the data range (e.g., A1:C9).
• Go to the Insert tab >> select Chart.

• In the Chart Editor panel >> from the Chart type >> select Line Chart.
• Under Series tab >> ensure Apply to all series is selected.

Step 3: Customize the Chart

Once the chart is created, you can customize the chart to make it visually appealing.

In the Chart Editor panel;

• Chart Title:
  • Click on the Chart Title.
  • Rename it to “Sprint Burndown Chart” or any relevant title.
• Legend:
  • Under Legend >> position it at the Top, Bottom, or Right as preferred.
• Chart and Axes Title:
  • Set the Horizontal Axis (Dates) and Vertical Axis (Ideal Burndown) labels for clarity.
  • You can also format the dates to make them more readable.

Step 4: Add a Trendline

You can add a trendline to visualize the ideal progress for the Ideal Burndown series.

• Select the Chart >> open the Customize tab.
• Under the Series tab >> select Ideal Burndown.
• Select Trendline >> choose the Type: Linear.
• Format the line color to differentiate it from the actual progress line.

The trendline helps to determine the ideal pace at which tasks should be completed, helping you visualize any deviation from the plan.

Step 5: Analyze the Burndown Chart

Once the trendline is added you can analyze the chart to visually inspect the progress of the assigned and remaining tasks.

• Above the Trendline: If the burndown is above the trendline that Indicates the sprint is behind schedule.
• Below the Trendline: If the burndown is below the trendline that means the sprint is ahead of schedule.
• On the Trendline: It means the sprint is on track.

Dataset Analysis:

• 02/01 to 04/01: Initial sprint progress was slow as the actual remaining decreased from 100 to 82.
• 05/01 to 08/01: In this sprint, progress improves, with the actual remaining falling to 75, reducing the gap with the ideal trend.
• 09/01 to 12/01: Sprint progressed significantly with actual remaining reaching 11.
• 13/01 to 15/01: Final tasks completed on time, with the actual remaining hitting 0, aligning with the plan.

Initial delays were recovered mid-sprint, ensuring timely completion.

Conclusion

By following these steps, you can create visually appealing burndown charts in Google Sheets. This burndown chart will help you visualize sprint progress and identify potential issues early. Google Sheets offers real-time collaboration and accessibility which will help you update the burndown chart dynamically in real time. Based on your project type customize the chart with available options. Make your burndown chart a valuable tool for your project progress tracking.