Bulk add or modify work items with Excel
When you have a lot of work items to add or modify, using Excel can save you time. Use a flat list to bulk add or modify several types of work items at once, such as backlog items, tasks, bugs, or issues. Use a tree list to bulk add or modify work items and their parent-child links.
You can also bulk add and modify work items using Microsoft Project.
If you don't have Microsoft Excel 2007 or later, install it.
If you haven't installed Visual Studio or Team Explorer, you'll need to install one of these versions to get the Team Foundation add-in to Excel.
In Excel, start with a blank worksheet. If you don't see the Team ribbon (or the Team menu if you use Excel 2007), see step 2.
If the Team ribbon no longer appears, you might need to re-enable it.
Connect to TFS and the team project where you want to add work items. If you can't connect, get added as a team member.
If it is your first time connecting to TFS from Excel, you might have to add TFS to the list of recognized servers.
Your worksheet is now bound to your team project as a flat list. What this means is that you can add work items to the team project from the worksheet or add work items to the worksheet from the team project.
Specify the titles of the work items you want to add and their work item type.
Notice how the State and Reason fields automatically fill in with default values.
Publish your worksheet.
Notice how IDs are now assigned to your work items.
Also, note how you can open a work item in Team Web Access to add more information.
You can make changes to work items in Excel, Project, Team Web Access, or Visual Studio Team Explorer.
Follow these tips to keep your work in sync:
You can bulk add a nested list of work items, such as a work break down structure or a hierarchical set of user stories and customer experiences. For example, you can add a nested list of tasks, subtasks, and bugs, as shown in the following illustration, or linked tasks to product backlog items, as described in the following steps.
Follow steps 1 through 6 from the previous procedure.
Convert your flat list to a tree list by adding a tree level.
Notice how the list type is now labeled Tree, and an additional Title 2column has been inserted.
Enter titles for backlog items under Title 1 and for tasks, under Title 2. Also, select the corresponding work item type for each.
Publish your worksheet.
TFS assigns IDs to each work item and creates parent-child links for each task listed under a backlog item.
Useful tips when working with a tree list:
Q: Which list type should I use?
A: Use the following table as a guide for selecting your list structure.
Q: How do I enable the Tree group of commands?
A: If the Tree group commands are not available, your worksheet is configured as a flat list. You can convert the flat list to a tree list as described in step 2 in Add backlog items and tasks and their parent-child links using a tree list.
Q: How do I convert a tree list to a flat list?
A: First, publish whatever changes you have made. Then, on the Team ribbon, choose Configure, List, and then choose Refresh work items only. This will flatten the tree structure and change the query list to an input list.
Q: How do I remove a tree level?
A: Remove any content entered under the tree-level Title number column you want to remove—the highest numbered column—and, then Refresh your worksheet.
Q: How do I convert from an input list to a query list?
A: On the Team ribbon, choose Configure, List, and then select the query you want to use. The worksheet will refresh with only those work items returned by the query. Also, if you select a tree query, then the list becomes a tree list.
Q: How do I add existing work items to my list?
A: If you are working with a non-query input list, you can add work items by choosing from the Team ribbon.
Q: Can I use Excel cut and paste functions?
A: Yes. You can use many Excel features, such as cut, paste, automatic fill, format, sort (flat list only), filter, and add formulas.
You can cut and paste rows to re-sequence items within a list and change link relationships among work items.
To drag a work item, select the work item or contiguous set of work items that you want to move, open the shortcut menu and choose Select, Table Row, point to the border of the selection, and—when the pointer becomes a move pointer —drag the row to another location.
Q: How do I quickly update a select set of work items?
Select the work items listed in a work item query in Team Explorer, open the short cut menu for one of the selected work items, and then choose Open Selection in Microsoft Excel, or choose the Excel icon in the toolbar.
Q: Can I use Office Excel for the Mac?
A: No. You need to use Excel on the same computer where you have installed Visual Studio or Team Explorer in order to get the Team Foundation add-in. These applications require a Windows OS.
Q: How do I resolve publishing errors?