Turn Spreadsheet Data Into Sprint Burndown Charts With Datadeck

How to use a reporting dashboard to pull Sprint data from Excel or Google spreadsheets. Share Sprint burndown charts with company leadership and keep everyone informed.

Not all small companies use project management software, especially startups.  It is not just a factor of cost of the software, often there just isn’t time to migrate to one for teams already taxed with many tasks.  This leaves program managers no choice but to use Excel or Google Spreadsheets.  

While it is easy to create, update and track Sprint activities in a spreadsheet, sharing the Excel graphs with teams and leaderships is not convenient.  Using a Dashboard is a low-cost way to do this.  With a dashboard can easily create a Sprint Burndown chart or calculate and display velocity results, all from a ‘dashboard’ that can be shared with the manager via a web URL.  

Furthermore, the dashboard checks the Excel file for updates.  So changes to the Burndown chart are updated, keeping the team or leadership up to date, just as though you had project management software.  A data dashboard also will have countless uses beyond presenting Sprint data to management.  Often dashboard applications have free trials that also allow using Excel or Google Spreadsheets as a data source.  This is what I will be using in this example.

First a little background.

What is Sprint? What is a Burndown chart? Why is it important?

Ever since Dr. Winston Royce published a paper title “Managing the Development of Large Software Systems,”, questioning the use of a sequential style process in developing software; more iterative approaches to the development lifecycle have been defined and utilized.  All attempt to manage the development process in such a way as to ensure the end product will still be relevant before it was actually finished.  Agile methodology is common solution.  

Often referred to as ‘Agile’, ‘Sprint’ or ‘Scrum’ product development, it emphasizes three main factors: empirical feedback, self-managed teams, and short iterations.  The self-managed team is a ‘Scrum’, the short iteration is a ‘Sprint, and empirical feedback: Burndown charts, velocity charts or a myriad of other reports in order to track the work and anticipate progress risks.

“A burndown chart is a very fundamental metric in agile scrum. Helps the team to track the progress, since it shows the progress on a daily basis it help scrum master to predict if a team will be able to achieve the target.”*

*Yodiz Agile Management Software “Understanding Burndown Chart in Agile Scrum Methodology”

A burndown chart is invaluable in tracking the teams progress of work compared to what they originally estimated it to be.  It provides an early warning to the team and stakeholders as to if project work will be completed on time.

A burndown chart is of little use if it can be easily shared and understood by team members and stakeholders.  It is imperative to “bring the burndown chart and other metrics diagrams to the retrospective meeting. Do not just leave them in Jira, so no one except the Scrum master looks at”; TISSquirrel Blog.

Teams using Excel or Google Spreadsheets to track agile project development work face a challenge.  Excel and Google Spreadsheets are outstanding tools for storing and performing calculations against tabular data, but terrible to share; especially for teams spanning the globe.  

Ever shared an Excel file only to have it altered and rendered useless by a well-meaning colleague? Solution – a dashboard.

What is a dashboard? Why use it?

A dashboard is simply an application that allows you to connect to multiple data sources, perform analysis, and easily share a report of results; emphasis should be on easy.  A well design dashboard should allow you to create great visuals from the underlining data, and be highly interactive and highly customizable.  I will be using Datadeck free dashboard.  It is web based, connects to Excel or Google Spreadsheets, allows you to share results without anyone monkeying with Excel calculations.

Sprint Data

There are lots of tutorials on how to create Sprint Burndown charts in Excel, a good one is located here.  A Burndown chart contains calculations against estimates to complete a task v’s actual time taken, here is our Excel Sprint team data:

		Actual Hours
Task	Estimated Hours	Hours – Day 1	Hours – Day 2	Hours – Day 3	Hours – Day 4	Hours – Day 5	Total hours
Task 1	8	2	4	0	0	2	8
Task 2	8	2	0	0	5	1	8
Task 3	4	4	0	0	0	0	4
Task 4	6	3	0	0	2	1	6
Task 5	7	5	0	0	1	1	7
Task 6	8	2	0	0	3	3	8
Task 7	5	1	0	0	2	2	5
Task 8	8	1	0	0	2	2	5
Task 9	9	5	0	0	2	1	8
Task 10	3	0	0	0	2	5	7

For convenience, the calculations for estimated time and actual time taken per day are conducted in a different sheet.  

	Actual Remaining hours	Estimated remaining hours
Estimated Hours	66	66
Hours – Day 1	41	52.8
Hours – Day 2	37	39.6
Hours – Day 3	37	26.4
Hours – Day 4	18	13.2
Hours – Day 5	0	0

In a vertical format with days in chronological order, you can easily check to see that by day 5, the team will be done with the Sprint – estimated hours for that day are zero.  While you could create a burndown chart in Excel with this data, it would be difficult to share.  This is where the dashboard helps you out.

Dashboard Burndown Chart

Now we connect the Excel or Spreadsheet Sprint data to a dashboard, create the Burndown chart, and share with team and leadership.  

1. Using a Datadeck free dashboard account, create a new dashboard.  I named mine “Sprint Dashboard”.
2. Add a data source, in this case I am adding an Excel data source.  Ensure the sheet with the estimated and actual time calculations per day is recognized.
3. Next add a widget which is the area where the burn down chart will exist in the dashboard.   Each widget can be a different data source on a dashboard.  In this case the widget will be using the Excel data source with the sheet with the estimated and actual time calculations per day.
4. With widget still open, select a line chart.  Drag the column that contains the hours labels to the x-axis, and remaining hours and estimated hours data to the y-axis.   Datadeck line chart has a ‘colored by’ option that allows you to color certain columns of data, remove any columns that are in ‘colored by’ option, we will not need this functionality.  Below is a screen shot of the widget setup.

In four steps we have a Sprint Burndown chart.

Next we do some tweaks to the chart so has great labels and title:

• Update the chart description:
• Name the data columns so they are understandable.  I named mine “Actual Remaining Hours” and “Estimated Remaining Hours”

• Under display tab uncheck ‘Summary value’, we do not to have a summary total for the chart

Hit save and close the widget.  You can expand the widget to make the chart more visible in the dashboard which I have done.  

The beauty of a dashboard is as the team updates the Excel data, the dashboard is connected to it.  So as the team enters in hours per day for a task, the connected dashboard checks to see if there are changes and updates automatically.  In this example I displayed data for the entire Sprint, but I didn’t have to.  The Excel file could have been set up with estimated hours for the Sprint, the dashboard and widget created.  As the team enters in actual hours per task each day, the dashboard Burndown chart updates.  

Using a public share link I can send this dashboard to members of the team or leadership.  

If you had problems follow this setup, Datadeck has some great help documentation for connected to Excel and Google Spreadsheets.  A great help article about formatting the tabular data for Excel and Google Spreadsheets can be located at here.

Conclusion

If you are using Excel or Google Spreadsheets to track Sprint activities, creating a Burndown chart using a dashboard that you can easily share with team members (without having to share the spreadsheet itself) is both easy and quick.  For startups on a limited budget, a free Datadeck dashboard Sprint burndown chart is hard to beat.  This is only the tip of the iceberg in regards to what you can do with dashboards as a Product Manager.  You can track Sprint data out of Excel or Google Spreadsheets, key website usage metrics out of Google Analytics or success or failure of Facebook Ads campaigns to name a few uses.  All this data can be combined, displayed and shared allowing you to track growth and success of your product.  I will be back soon exploring other Product Manager uses of dashboards.