Inside VSTS

Using Excel To Manage Your Work Items

Learn how to integrate VSTS' Team Explorer into Project and Excel.

• By Mickey Gousset
• 10/06/2008

TFS allows you to do just that. By integrating Team Explorer into Project and Excel, you can use those applications to manipulate data in the work item tracking system.

In this article, we'll look at how you can use Excel 2007 to manage your work items. When you install Team Explorer on a computer with Excel 2003/2007, it will automatically integrate into the application. It will add new menu and toolbar options allowing you to access TFS, retrieve work item information and push changed information back to TFS.

Excel is great to use when you need to make bulk changes to work items. The first thing you might want to do is retrieve a list of work items into Excel. When you open Excel, click on the Add-Ins menu option. You'll see a ribbon similar to Figure 1.

Click the New List button. You'll be prompted to connect to your TFS and select a team project. Once you select a team project, the New List window will appear, as shown in Figure 2.

This window provides you two options. The first is the Query List option, which allows you to retrieve existing work items using a work item query. The second is the Input List option, which connects your spreadsheet to the Team Project, allowing you to add new work items without having retrieved any existing work items. With either option, you'll always have the ability to retrieve work items later from the TFS.

Select the Query List option, select the All Work Items query, and then click the OK button. This will retrieve all the work items for your team project and pull the results into Excel, as shown in Figure 3.

Now you're ready to start manipulating your work items. You'll notice that only certain work item fields are initially shown as columns. You can easily add more fields by clicking the Choose Columns button from the toolbar. This will open a window where you can choose more fields to display.

Excel makes use of the work item type definition to enforce any rules and workflows you have created in your work item types. For example, you may default the value of certain fields when a work item is first created. To create a new work item in Excel, simply add a new line to the spreadsheet. In the Work Item Type field, select the appropriate work item type.

If you have any rules set up in your work item type to set default values in a column -- such as defaulting the Assigned To field to the name of the person creating the work item -- those rules will execute automatically. This means that by using Excel, all of the rules or workflows defined in your work item types are enforced.

Where using Excel to manage work items really shines is making bulk changes. Let's say you wanted to reassign all the work items in the project to a particular person. Using Team Explorer, you'd have to open each work item individually, modify the Assigned To field, then save the work item. Using Excel, you can change the Assigned To field for one row, then copy and paste those changes to the remaining rows.

Once you've finished manipulating your work items, you need to push your changes back to the TFS. To do that, click the Publish button on the toolbar. This will synch your spreadsheet with TFS. If you've created new work items, you'll see the ID for those work items appear in Excel.

If someone else has been working with the same work item data, you may encounter conflicts. For example, you change a work item in your spreadsheet to be assigned to Jack, and someone else changes the same work item, in Team Explorer, to be assigned to Bob. When you publish your changes back to TFS, it will detect that conflict and open the Work Item Publishing Errors window. This window allows you to review and resolve any conflicts. In this instance, it would give you the option of choosing who the work item should be assigned to.

Another nice thing about using Excel to manage your work items is the ability to manage work items offline. You don't have to stay connected to the TFS to work; you can just grab all the work items from TFS, then unplug your laptop and take it home with you. The next morning, you can publish any changes you made back to the TFS.

Excel is a great tool for working with the TFS work item tracking system. It allows you to make bulk changes to work items, as well as work with the work item information offline. It's also a great way to get power users, such as business analysts and project managers, involved in using TFS, while allowing them to use the tools with which they're already comfortable.