Succeeding with Agile - Mike Cohn's Blog

A Sample Format for a Spreadsheet-Based Product Backlog

Jul 1, 2011
by Mike Cohn
40 Comments

I want to show a real easy way to put user stories in a spreadsheet-based product backlog. I wrote this after seeing someone tweet a screen capture of a product backlog I made nine years ago and thought to myself, “Yikes, that's out of date for how I do it today …” So in this post, well look at an agile product backlog template in Excel.

As you probably know, I'm a big fan of writing the product backlog in the form of user stories and of writing user stories in the form: “As a _____, I ______, so that _______.” An example being: “As a frequent flyer, I really want to be able to connect to the internet while flying so that I can update my blog while traveling rather than having to save this as a text file and updating my blog later.” (Can you guess where I am while writing this?)

What I've found makes a user story in this format very easy to work with in an agile Excel spreadsheet is to take the boilerplate parts and put them into column headings. So we'll have column headings like “As a” and “I” and “so that”. The meat of each story is then clearly visible in each row. Additional columns can be added for things like a unique identifier, notes, status and such. In this example, I've also included a column for the theme or grouping of which the story is a part. You can see this in the screen capture below.

You can click the image for a larger view of the agile Excel spreadsheet.

Thanks Mike for sharing ... ;)

Posted by Hossein on 2013-03-18 10:09:44 Hi Agile Newbie- I'm glad you found this post helpful and that you found it in time to use in your new role. Good luck on the project.
Posted by Mike Cohn on 2012-08-20 16:07:47 Thank you Thank you Thank you! I have just taken on a new role in our IT group - sort of like a Product Owner and am new to Agile - just as we are beginning to "thaw out" from a project freeze. Looking through our enhancement request system which has a multitude of cases requesting certain features be developed in various forms of "Do this" and "this doesn't work" to some pretty well-fleshed out requirements and some "epics" that aren't written in story form at all, I wanted to create a product backlog but didn't really know where to start. This template (and the user story format) will help me tremendously when meeting with my customers to prioritize all of these old requests - as well as get something we have failed to do in the past: The "so that...." and conditions of satisfaction seem to have always been skipped by our users- so we really don't ever know what the 'definition of done' is - which is likely why some projects just drag on. I've bookmarked your blog to read more!
Posted by Agile Newbie on 2012-08-20 15:40:04 Thanks, Rahul. Unfortunately I can't really say what the difference between a product backlog and your MRD is. An MRD seems a bit different everywhere I see that document. I suggest you look at the various page on this blog and the rest of this site describing what a product backlog is and compare to what you have as an MRD. You can start with these pages on all Scrum artifacts and specifically about the product backlog.
Posted by Mike Cohn on 2012-08-02 07:52:35 Hi Mike, your blog is really nice just want to know what is the difference between Product Backlog and MRD(Market Research Document.)
Posted by Rahul on 2012-08-02 01:41:28 Broken link: clicking the sample spreadsheet leads to a page titled "Handling Requirements from Architects Outside the Team", even though the URL says it is for the JPG.
Posted by Bob Clarke on 2012-07-18 15:01:27 Hi Mike Coincidentally, that spreadsheet very closely resembles the interface of easyBacklog, an application that we have recently released. If you take a look at the screen shot at easybacklog.com/images/beta_signup/screen_shots/complete-backlog-with-calculations.png, you will see that all the columns (other than priority) are catered for. When we designed easyBacklog, we were trying to replace the spreadsheet based systems that we had been using, so the inspiration was to build something that was fast as Excel, but without the mess of maintaining formulas, versions and layout. Following on from David's comment about having a template to print story cards, that is what easyBacklog does as well. It automatically prints double sided cards so that you can still use physical cards on your boards. As the founder of easyBacklog, for any of you using spreadsheets now, I'd love to hear what you think of easyBacklog and the way we deal with backlog management. You can sign up at http://easybacklog.com. Thanks, Matt
Posted by Matthew O'Roirdan on 2011-11-16 07:48:23 Hi Attila-- I wouldn't take any of the other columns too seriously here. This is just one sample from one small project. A different project would show different columns. However, in this example, you're right that the "priority" column is really the same as the notes column with the exception that it was "notes about priority." And in this example, we found it helpful to separate notes about functionality from notes about priority--so we used two columns.
Posted by Mike Cohn on 2011-10-19 16:28:24 Mike, Regarding the priority column, is it no longer the convention to use unique priority numbers? Or are those unique values simple assigned during Sprint Planning? I'm a little confused as to how you are using the priority value here, since they look similar to general notes. Thanks, Attila
Posted by Attila on 2011-10-19 10:59:45 Hi Christophe- I've never been a fan of the BDD version of writing user stories. It's not a natural way of speaking. I don't think I've ever started a sentence that way in a conversation. I've blogged about why I like the older "as a user, I want..." format at http://blog.mountaingoatsoftware.com/advantages-of-the-as-a-user-i-want-user-story-template But, I don't want to ever become slavishly devoted to either template. But I found "as a user, I want" a better way to start a converstation, which is really the intent.
Posted by Mike Cohn on 2011-10-10 16:46:24 Hi Mike, Because I had to work with external customers, I had to use Microsoft Word and a user story template that contains CoS following the template: Given [context], When [Event], Then [Assertable outcome] allowing us to add scenarios to the user story (set of acceptance criteria). Then, using a simple macro, I generated an excel product backlog like yours reading each user story in the Word document and copying user stories in the excel document. I have found using the format [Given, When, Then] as very efficient for discussions with customers ("oh this is not what I meant!") and very useful for the test team. Now, as a scrum practice in my company, I am trying to coach people use such format. Cheers Ch
Posted by Christophe on 2011-10-10 06:13:26 Hi, great backlog. I made some similar on. If you'd like to check it, and share your thoughts on it just click: http://scrumyum.blogspot.com/2011/07/simple-product-backlog.html I did some extra burndown charts and based on the calculated or assumed velocity, it does some prediction to the stories.
Posted by Tom on 2011-08-07 10:55:55 Kai-- I keep saying this: You can put whatever you find helpful in a spreadsheet like this.
Posted by Mike Cohn on 2011-07-22 13:29:18 Is the acceptance criteria say "what needs to be tested or how it will be tested"? For example What is like "Test ability to enter various data in Name field" How would be "Test blank,characters in name field" Test "Invalid name" popup box display" "Test Old name popup is removed
Posted by kai on 2011-07-22 08:24:51 It sounds like you have made them too small.
Posted by Mike Cohn on 2011-07-21 11:18:59 Ok because I find when talking about a story the user(knowledgable user) will start hashing out the requirement and what processes need to be kicked off etc...during the initial requirements so I have been placing them in the notes..when i am done i realized that they pretty much gave me the requirement. Also I noticed that when trying to hash out a story it piggy backs or relates to another story are dependent on each other or when doing a visio diagram I relize that this one diagram in essence combined 5 user stories. Does this happen or have i broken them down too small?
Posted by Kai on 2011-07-20 20:20:53 Hi Kai-- No, I wouldn't use "notes" to avoid written requirements. The "notes" column is for things like "Be sure to talk about how we'll do the..." Think of the written form of a user story as a pointer to a requirement. The true requirement can come out in the form of a discussion (often ideal) or a Visio document, an Excel spreadsheet showing a calculation, a Word doc, etc.
Posted by Mike Cohn on 2011-07-20 13:02:45 I think I need to drop the module field because a feature can cross over multio modules :( I was thinking that would be great so over time we can see which modules we have been making revisions to and do a report if anyone wanted to see all revisions to the "Main Page" modoule. Is your Notes section to replace the need for writted requirments? 2. What else do you provide as a deliverable other than user stories..Like do you give developers diagrams, nonfunctional spec, screen shots of revised forms to have a clear picture of what the user whats, what it should look like and how the system should respond?
Posted by kai on 2011-07-20 13:00:03 Thanks for all your help. I just bought your book as well. Trying to use my spread sheet in Sharepoint -all the stories for EVERY release will be in this spreadsheet so I will be able to filter it by release for release notes or reporting. I am new at this so let me know if my thinking is wrong. I did not break out the story because not all Business Analyst on my team are doing stories. ID 1 Story Add new field to xyz screen Theme (Change Request) Module/Feature (Payroll) Acceptance Criteria Status (dev, test,prod) Release Number 2.0 Then I will have a separate task master for each story in a release Story ID Task EST Status 1 setup screen 2hrs Research 1 Field in database 5hrs In progress 2…… Need to see if sharepoint lets me create relationships to connect all these together…like in Access database. What would be the difference in a status of s story and a status in a task master?
Posted by kai on 2011-07-20 07:57:02 Hi Kai- I can't imagine putting the tasks in this same spreadsheet. This sheet is just for tracking a product backlog. I'd ideally use a taskboard on the wall for the tasks but could use a different spreadsheet if needed. Yes, put the test cases there if you'd find that helpful. I was just trying to show a few simple columns that separate the user story template from the important parts. You can extend it as much as you'd like.
Posted by Mike Cohn on 2011-07-20 07:41:19 In general I do use that template but I don't ever want to become a slave to it. If "Add new field to xyz screen" is adequately descriptive to create the right conversation around the user's needs then just write that.
Posted by Mike Cohn on 2011-07-20 07:39:22 Do you use user story(iam-Sothat) for edits/maintance projects like a release that is soley for the purpose of adding edits, fields to a screen or a error handling identified in prod?
Posted by kai on 2011-07-20 07:32:13 Would the testcase also go in the excel sheet with the story? Would the tasks also go on this spreadsheet?
Posted by kai on 2011-07-20 07:26:40 Fantastic. I'm glad you've found it helpful. Thanks for letting us know.
Posted by Mike Cohn on 2011-07-14 06:14:47 Hi Mike, We have changed our spreadsheet format to one you defined in this post. I was just wondering why didnt i get this simple idea before and hating myself for that :(. Interestingly i did not find even one agile tool (the ones i have seen so far) taking a story in this simple format giving a purpose and consistency the way we enter backlog. Thanks for the great post.
Posted by Raju on 2011-07-14 02:13:14 >I recommend taking a look at User Stories Applied. It should answer many more of your questions. :) I read that one. But still eager to see a lot of "real world" examples and discussions about structuring and working with a backlog. ;) btw - I print my excel backlogs using the "serial letter" feature of office. You can fully design the layout of your cards..
Posted by Heiko on 2011-07-07 09:18:56 Hi Heiko-- Yes, those are real world stories. They were from a 2.0 version of www.PlanningPoker.com. Sprints work best with small stories but I wouldn't go as far as saying "small as possible." To keep the big picture you do two things: write large stories (epics) and break the epics into small stories as late as possible. Nothing restricts this to only two levels. I can have a huge epic that I split into "smaller" stories that are still epics. Later those epics split into sprint-sized stories. Each row in this example is a story, not just the notes column. When collocated I highly prefer index cards for the sprint backlog but I don't mind a product backlog in a tool as long as we can print the user stories onto index cards for meetings. In the case shown here, however, the team was distributed. I recommend taking a look at User Stories Applied. It should answer many more of your questions.
Posted by Mike Cohn on 2011-07-07 06:48:54 Hallo, thanks for sharing - I like the concept. Are these realworld stories? I ask this, cause I am looking for examples how backlogs are structured. In my experience a sprint works better if the stories are as small as possible. But the drawback is, that you often loose track of the "big picture" when you keep the stories small. I like to hear how you manage to work with "only" two levels and still keep the big picture. and... do you write the stories still on index cards? or is the notes column enough? Thanks Heiko so many questions ;)
Posted by Heiko Stapf on 2011-07-07 00:56:06 Thanks, Jacob. And, yes, I wrote that and just about everything I get to write on an plane.
Posted by Mike Cohn on 2011-07-04 15:56:21 Mike, thanks very much for this. It's always great to have some nice examples in front of you, and another example to show clients. Oh, and since no one else guessed, I think you wrote it on an aeroplane ;-)
Posted by Jacob Creech on 2011-07-04 15:38:39 Hi Camilo-- I've never thought of the Three Cs as a "format." I just think of them as a reminder that there is more to a user story than the simple text on a card. But, I can definitely see adding a Confirmation column, where conditions of satisfaction (acceptance criteria) for a story can be listed. And a Notes column can be equated to Conversation. The main thing I wanted to show with this example was that put text like "As a" and "I want" into the heading lets us take the boilerplate out of the spreadsheet. We still read the story that way (and get the associated benefits) but we can also very quickly scan a lengthy backlog when it's structured this way.
Posted by Mike Cohn on 2011-07-04 06:09:41 Hello Mike, If I would compare this sample format to the Card-Conversation-Confirmation format, the "Card" part was split, and "Conversation" & "Confirmation" where combined in Notes. Is this understanding right? I have used spreadsheet-based backlog to run undergrad courses (hands-on dev projects) using a template that splits each "C" in a different column. I've tried once to put Conversation and Confirmation in a single cell, but students too often got confused. Thanks for sharing! I'll update my template to split "Card" information.
Posted by Camilo on 2011-07-04 05:39:00 Hi Santanu-- I wouldn't object if a team wanted to include those but they weren't factors we considered important to see at a glance on that project.
Posted by Mike Cohn on 2011-07-04 04:14:41 Hi Mike, Why wouldn't you have 4 more columns to capture the "ratings" for complexity, effort & uncertainty in the product backlog? That way, you can always see those relative values and keep those updated and use while prioritizing the backlog for a sprint. Just a thought. Thanks & Regards, Santanu
Posted by Santanu on 2011-07-04 00:34:47 Hi Fabrice-- Thanks for translating this. Mike
Posted by Mike Cohn on 2011-07-03 12:25:42 Thanks, Michael. Yes, notes could be replaced with "acceptance criteria." I'm always a big fan of showing real stuff, though, and that is very clearly a real product backlog from right in the middle of the project. (The project incidentally was to do some updates awhile back to the Planning Poker website.) I tried to check out your Product Roadmap Template but Google Docs said it couldn't create an image at the time and for me to check back later, which I'll do. Thanks. It sounds good.
Posted by Mike Cohn on 2011-07-03 12:01:28 Hey Mike - I Like it! It's a very pragmatic approach to maintain a Product Backlog. Maybe you should drop the "notes" column and replace it with "Acceptance Criteria"? Essentially the examples for notes you have in the Screenshot above can all be characterized as such. So it might make sense to rename it. I developed a "Product Roadmap Template" recently which might be of help for one or two as well. You can view / download it here: https://docs.google.com/leaf?id=1zKuw2egst9FeQ4KbatjBtI9AgkCh3uetRneZgg7LE4E&sort=name&layout=list&num=50 It's essentially like your template but it's focused on Themes instead and their scoring / prioritization combined with a timetable view for further (release) planning. I hope somebody might find it useful as well. It's working nicely for me. Cheers, Michael
Posted by Michael Romer on 2011-07-02 12:01:03 Hello Mike, Thank you for your product backlog format : K I S S ! I have translated your post into french : http://www.fabrice-aimetti.fr/dotclear/index.php?post/2011/07/01/Un-exemple-de-backlog-produit-sous-forme-tabulaire Regards, Fabrice
Posted by Fabrice Aimetti on 2011-07-02 00:44:25 Hi David-- Excellent! Thanks. This is really the best of both worlds--being able to keep the product backlog in a tool but then print it onto cards for use in meetings. Thanks for sharing.
Posted by Mike Cohn on 2011-07-01 18:19:52 Hi Mike! I have written a Google Spreadsheet Index Card Generator that helps you create printable index cards from a Product Backlog Spreadsheet, almost like the one you have made. If you are interested, check out this blog post (in both Swedish and English) for more info: http://blogg.idg.se/agile/2011/06/27/visa-vad-du-gor-eller-dude-wheres-my-index-cards/
Posted by David on 2011-07-01 08:54:30