Soup to Nuts – TFS 2010 Agile Iteration Backlog Workbook utilized in the Real World

It seems that many people will get the first iteration backlog workbook working and then they will copy & paste it for future iterations.  This is what we started doing until we found out just how messy this really is.  The Agile iteration backlog workbooks are very useful for Agile (and SCRUM) teams to understand their capacity with interruptions.

image These workbooks are very useful, though, there is some confusion often by those using the workbooks what to do when a new “Sprint” (or “Iteration” – used interchangeably in this post) is started.  The copy & paste exercise *might* work but often it locks the workbooks up due to the macros getting unhappy.  This is the point of today’s post.

I will walk you software dev managers (project, pm’s, or whatever title you have) through starting from scratch at each sprint and utilizing the workbooks to track progress.

Step 1:  Move any un-completed work from Previous Sprint to Backlog

The first step is to select the iteration backlog tab of your previous sprint’s workbook by click on it, then locating the Iteration Path column and moving all work items back to the Backlog iteration.  To do this, here are the step-by-step pieces -

  1. Open Sprint N Workbook
  2. Click Iteration Backlog tab in the Workbookimage
  3. Locate the Iteration Path column to the far right of the workbook, and click the dropdownimage
  4. Do this for all remaining work
  5. Click Team tab in the Excel Workbook
  6. Click Publishimage

Step 2:  Create New TFS Workbook Query for new Sprint

The next step, often overlooked, is to create a new List-based query that pulls all the work items and columns for your upcoming sprint.  In this step, I will walk you through step-by-step creating the new query that will be utilized by your new Iteration backlog workbook.

  1. In Visual Studio 2010, open your Agile Project
  2. Click Work Items
  3. Click Team Queries
  4. Click Workbook Queries
  5. Assuming you’ve still got the original queries shipped with TFS 2010, right-click on Iteration 1 Backlog and select Copyimage
  6. Right-click on Workbook Queries and select Paste and provide the new Query item nameimage
  7. Right-click on the newly created query, select Editimage
  8. In the query editor, locate the Iteration Path and change it to the iteration you’ve defined for this new sprintimage
  9. Click Save Query

Step 3:  Open Iteration Backlog Template & Bind to new Query

The next step actually lights up your iteration backlog by binding the workbook to the query you’ve recently edited and saved.  This tells the workbook what data to pull in to manage for this sprint.  Let’s walk through this binding process…

NOTE:  This step assumes you’ve got a folder under documents called Process Templates.  If you do not, then you will need to locate where you’ve got a file named Document Template – Iteration Backlog.xlsm and open it.  This file is bound to the project automatically and should be made available as part of your creation of a new project.

  1. Open Visual Studio 2010 and connect to your Project
  2. Drill-down into the Documents, Process Templates, Project Management folder by clicking on the arrowsimage
  3. Right-click on Document Template – Iteration Backlog.xlsm and select Copyimage
  4. Locate where you would like to save this new workbook, right-click and select Paste (it must live somewhere within the Documents scope as it should be saved to SharePoint)
  5. Right-click to Rename the workbook to Sprint N Iteration Backlog.xlsm
  6. Double-click to open the Workbook
  7. In the workbook, click the Team tab and then the Configure –> Listimage
  8. In the Configure List Properties, click the … and then Select the query created in Step 2
  9. Click OK
  10. Click Apply
  11. Click on the Save icon to save this workbook back to SharePoint

That’s it.  You are now ready to start pulling items from your backlog and assigning them to the sprint and they will now show up in your workbook.  Please keep in mind that you utilize the Product Backlog workbook to set the iteration path and then, once you’ve selected your sprints scope, you then open the Iteration backlog and start setting your Remaining Work & Completed work, Capacity, etc.


In today’s post, I wanted to share with project managers how they can best utilize the great workbooks that are part of TFS 2010.  The unfortunate time comes when you’ve got to understand how to take a template all the way to a workbook and this wasn’t something that necessarily just “made sense.”  Today, I walked you through the steps to go from a template (shipped with TFS) to an actual workbook with actual data. 




Comments (2)

  1. ChrAd says:

    Hi Steven-

    We installed Urban Turtle and I think the Planning Board & Task Board are nice.  However, we found them to be a bit ackward and ultimately abandon them.  The planning aspects & task board are nice…

    Our team's ultimate review said that it just didn't make sense to use it since we spend zero time utilizing the web interface.  All of our work is done in Visual Studio and we are ultimately looking for a planning/task board that aids where we spend time.  

    The drag & drop was nice but overall we ended up uninstalling 🙁

    Thanks though for the comment – everyone has their opinions and it sounds like Urban Turtle is working for you.


  2. Steven Laszczewski says:

    If you want to simply your life try Urban Turtle. They provide a great planning board and task board over the web to manange your Agile Project.

    I like that tool with the drag and drop over the web !


Skip to main content