A Tool for tracking Kanban projects (that you can cut out and keep)

Reading Time: 2 minutes

I have been looking around for the right digital tool that will support my physical project wall. I want something that allows me easily to back up information for reference, but more importantly outputs the useful metrics that are important for continuous improvement.

A lot of people I know use Trello, which is not bad as a tool that creates a visual representation of the cards, but it doesn’t give me the data I want to be able to plan or improve the team’s flow. I also had a quick look at KanbanTool, which does offer cycle time and cumulative flow diagrams, but again is more of a replacement to a wall rather than a tool to support one and it’s not the prettiest of designs.

My solution was to create my own tool in Google Docs, this is a first pass at something that will do what I need and I’ve started testing it out with my own project already. I’ll change it as I need to as I go.

I’m sharing it here for anyone to take a look under this Creative Commons Attribution-ShareAlike licence (full terms of usage are here, by creating a copy of the speadsheet you are agreeing to these). If you want to use it, you can create your own copy and edit to make it suitable for your needs. You may will need some knowledge of formulas and scripts (ask me if you need some pointers). I’d love to know if you find it helpful and do actually use it for your own project(s).

** disclaimer: this was the original blog post that went with the tool, the tool has changed a bit since then **

How to use it:

Add your work items in the tab called “Work items”

When a card on your physical wall moves into a new column, update the sheet to reflect the date by adding it into the relevant column. (This automatically updates the stage column and the count of cards in each column at the top)

Every time you want to record the data in the current state, go to the “data over time” sheet and press the “backup stages” button (you will want to schedule the script for this to automatically backup if you are using it regularly, I haven’t done this here as the sheet is not in use). This will build your data and feed your graphs over time. Doing this will update the cumulative flow diagram, the WIP / delivery rate chart, the lead time data / curves, the forecasting tool and the process control chart.

To hide done stories from your view on the “Work items” sheet to make it easy to read, use the menu item “kanban scripts” and “hide done items”

Once you have some data, to see how adding stories will change your forecast, change the numbers in the blue boxes on the “forecasting tool”

Adding new dates also updates the Lead Time sheets, which will record how long things take from being committed to, which gives us averages to plan with and charts to review to see how process improvements help (see the lead time charts)

There is also a “blockers” sheet, so you can have a single view of what the current issues are and what has happened in the past.

Updates

To keep this post neat, there is a change log here

Big Thanks to….

Hakan Foss http://hakanforss.wordpress.com/2012/10/31/getting-started-with-collecting-kanban-process-metrics/ for the maths behind the confidence graphs

Dan Brown (aka @kanbandan) for pairing with me on the forecast tool

If you have any questions, please ask

Happy Kanbanning!

27 Comments

  1. Hi Emily, great post, thanks.

    We started using Kanban at the start of the year and started using a CFD based on instructions by Hakan Forss (see http://hakanforss.wordpress.com/). However, he also has guides of how to create a control chart and histogram which give some interesting output. Give me a tweet at @bigpinots if you want to discuss further…

  2. Hi Emily, Your tracking tool has been shared with me … it’s great! I am planning to use it for my team’s reporting and wanted to know how easy it is to add additional columns and would I need to update any of the formulae? Thanks 🙂

  3. I’ve spent last two days customizing the tool for my workflow, and now it’s good to go! Thank you a lot for sharing it with everybody, it’s amazing!

  4. Hi Emily

    Great tool, this is what the Kanban community needs, not a replacement of the wall but a complement.

    I´m wondering if you have documented more pointers related to column changes? That will really be useful !!

    Thanks!!!
    Manuel

    • ewebber

      13 March 2015 at 11:08 am

      Hi Manuel,
      there is a fair bit of description within the document itself and some detail in this post. Further than that it will require some knowledge of formulae to be able to update to meet your needs.
      Let me know how you get on
      Emily

  5. Nicolas Zapata

    23 April 2015 at 7:39 pm

    Hi Emily! This is a great kanban tool!
    I have a question,
    How would you do if, for instance, in “review” state, a developer is asked to modify some things? which dates should this dev change? I would clear the “review” date, and update the “in progress” date to that day.. then update all graphics. Is this approach correct?

    regards!

    Thanks!!

    • ewebber

      11 May 2015 at 10:19 am

      Hi Nicolas,
      it all depends on how you want to regard this in your own workflow and how you are tracking the work done. The common view (which I have held during projects) is that you don’t move cards backwards, in which case you would need to decide what fixes you would accept under the review column and when that is a bigger change and needs a new card.

  6. Hi Emily,

    Thanks for creating, sharing, and maintaining this great tool!

    Cheers,

    G

  7. Emily, I get an error trying to get this spreadsheet. Something about a certificate.

    • ewebber

      7 March 2016 at 6:32 pm

      Hi Tom,
      I need a bit more information from you to understand what the problem is.
      When you say “get this spreadsheet” what exactly are you trying to do? Can you walk through it step by step and what errors do you get.
      Thanks

  8. Hi Emily,

    I have already made a copy of your spreadsheet and it is brilliant.
    The only question I have for you is about the Projected Dates column. Apparently it is not updated automatically, and if I have to fill it up manually (honestly) I don’t know which day to put there. As I’m leading an Operations kanban board, we don’t have a projected finish date.
    Could you give me your opinion on this?
    Thanks

    • ewebber

      27 January 2017 at 2:07 pm

      Hi Eugenia, If you are referring to the data over time sheet, the projected dates column shouldn’t need updating, it’s there to help understand what number of stories might be delivered on that day in the future and when it might end based on current data. It then populates the graph on the forecasting tool sheet. The column title might be misleading and should probably just be “date”. This column has a formula in it in the original spreadsheet to calculate weekdays.
      This whole sheet shouldn’t need anything to be added manually as long as the script is set up to run every week day.

      • Hi Emily, thanks for your quick answer.
        In my case, the value in cell G7 stays fixed as “19 May 14” that is the value you have in your template. That’s why I asked you if I needed to update it manually.
        The rest of the values on that column have a formula as you explained above.
        If my project started on the 1st of Jan, which should be the value on G7?

        Thanks!

        • ewebber

          28 January 2017 at 8:41 am

          Ah yes that’s right, actually you can update G7 to contain “=A7”. Then as long as you are recording data every weekday then the dates in column G should match the dates in column A.

  9. I have introduced kanban systems to the DevOps teams at Elsevier. We are using both physical boards and reflecting those electronically in JIRA so that we can automatically track the timestamps as it moves through the workflow. We then create an excel extract out of JIRA and enter the numbers into your workbook.

    Thanks for making this open source Emily. It’s awesome!

  10. Hi Emily,

    Tired to use the Kanban Tracker but when I click on the backup stages but it doesn’t do anything and there doesn’t seem to be a script behind it.

    Any ideas where I can get the script?

    Barry

  11. AMAZING!!! Thank you so much for sharing! It’s Genius!!!

  12. Hi Emily, this is a great sheet! I love it. I’m unable to run the backup stages script on the “data over time” sheet, just due to network and access restrictions with my company. Is it possible to get a source view of the script so I can reproduce or perhaps another way to populate those columns?

    • ewebber

      30 April 2019 at 5:40 am

      Hi Lola,
      I’m not sure what restrictions you have, but I would expect you to be able to see the scripts in the copy you took of the sheet. Essentially the script takes cells A3:F3 in “data over time” and pastes them in the next available cells in the same row in the table below, something you should be able to easily replicate manually.
      Emily

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Verifying Mastodon