[Update 20 Nov 2018: The very latest version of the Google Sheet for creating burn-up charts is available at Github. It also has detailed instructions. Please go there instead. Otherwise there is a history of articles which are out of date: from 2011, 2014, 2015, 2016 and 2018.]

I’ve never met a burn-up chart I didn’t like. I find them to be simple but effective visual guides which show not just current status, but also trends. They also place a focus on value delivered rather than activities done.

Traditionally burn-up charts are used to track agile-specific work like story points. One line shows the expected scope, which may change over time. Another line shows work done over time. Then you get to see past performance, current state, and you also have a way of seeing projected progress without varnish.

I find burn-up charts so useful that I don’t think they should be confined to special tools (however excellent) such as Jira, Mingle or Rally. In fact it’s really easy to create a burn-up chart in a spreadsheet. You just need three columns (time, total estimate at that time, actual delivered at that time) and a line chart covering them.

But I find it’s useful to track historical changes, as well as the aggregated data. For example, I’d like to see not just the total story points estimated at any particular date, but also when the various user stories got introduced, got removed, or got re-estimated. That can also be put into a spreadsheet, but getting from that to our three-column table is very tricky.

A while ago I described a simple method for doing that using any spreadsheet. It allows you to keep your history and still get a nice burn-up chart, but it is rather manually-intensive because every change requires a lot of copy and paste. It works fine until you get to a certain level of complexity.

So now I’ve refined this. It’s designed for Google Spreadsheets, because it uses its scripting capabilities to create useful functions, but Google Spreadsheets is simple and widely available. It works by having one table for the historic data (our user stories as they change over time) and one table for the burn-up chart data, which aggregates the historic data using a new function.

You can see an example of this spreadsheet in action. To see the script you’ll have to take your own copy of the spreadsheet (File > Make a copy…) and then to to Tools > Script editor. Or look over here. (You’ll be pleased to see the spreadsheet also carries automated tests for the code.)

Here’s how to create the historic data table:

• Put your user stories into a table, one row per user story.
• Make sure the table has columns named “Valid from” and “Valid to”. The “Valid from” date is the day on which the story was created. The “Valid to” date is initially some date far in the future.
• Each time a user story changes create a new row with the new version of the data. The “Valid from” date is the date on which this latest change happened. The “Valid to” date is some date far in the future. And change the “Valid to” date on the previous version of the user story to be the day before this “Valid from” date. So now you have two versions of the user story: one which is valid from its creation and valid to the day just before the latest change, and one which is valid from when this last change was made and on to the foreseeable future.

You can filter and sort this table til your heart’s content. Just keep a header row at the top.

Then you can summarise this historic data in a second table. It has three columns, as usual:

• The first column is dates. It can be day by day, or a the first day of each week, or the first day of each sprint, or whatever.
• The second column is a formula for the scope: =aggregateDimension(…). This will aggregate the relevant data as it was on the given date. It’s named after the data warehousing concept of dimension tables, which store historic data. The aggregateDimension function is specially defined in the spreadsheet’s accompanying script. Look at the example spreadsheet for more details. But in short it takes four parameters:
  • The date in question (from the previous column);
  • The table of historic data;
  • The name of the column in which we have the estimate of each user story;
  • A string describing the calculation we want to do across the estimates. Usually this is “sum”, because the sum of the estimates will give us the total scope as it was on that date.
• The third column is just like the second column, except it references the name of the column in which we list the actual work done. This will be populated for any story completed, and empty (or zero) otherwise.

The aggregateDimension function also allows several columns to be specified for the calculation, and other kinds of calculation—specifically “sumproduct”, “count” and “counta”.

Clearly this isn’t a replacement for major project tracking systems. But I find it is a good lightweight alternative when a big system seems like overkill.