Partager via


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.

Add work items using a flat list

  1. If you don't have Microsoft Excel 2007 or a more recent version, install it.

  2. 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.

  3. 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.

    Create a list connection between Excel and TFS

    Tip

    If the Team ribbon no longer appears, you might need to re-enable it.

  4. 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.

    Connect to Team Foundation Server dialog box

    If it is your first time connecting to TFS from Excel, you might have to add TFS to the list of recognized servers.

    Servers button on the Connect to Team Foundation Server dialog box. Add button on the Add/Remove TFS. Name of server in the Add TFS dialog box. OK button.

  5. 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.

    Empty flat list connected to a team project

  6. Specify the titles of the work items you want to add and their work item type.

    Add work items to Excel

    Notice how the State and Reason fields automatically fill in with default values.

  7. Publish your worksheet.

    Publish work items from Excel to TFS

    Make sure your cursor is in a cell that contains data. Otherwise, the Publish button might appear disabled.

    Notice how IDs are now assigned to your work items.

    Published work item IDs show in Excel

  8. Also, note how you can open a work item in Team Web Access to add more information.

    Open a work item in TWA from Excel

    Work item displayed in Team Web Access

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:

  • When you first open a saved worksheet, use Refresh icon in Excel on Team ribbon (Refresh) to download the latest data from TFS.

  • Enter data for additional fields by adding columns to the worksheet using Choose Column icon in Excel on Team ribbonChoose Columns.

  • To avoid data conflicts, publish your additions and modifications often.

  • To prevent loss of data before you publish or refresh, save your workbook periodically.

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.

Tree list that shows how the column layout displays parent-child links

Microsoft Excel work item tree list

  1. Follow steps 1 through 6 from the previous procedure.

  2. Convert your flat list to a tree list by adding a tree level.

    Convert flat list to tree list

    Notice how the list type is now labeled Tree, and an additional Title 2 column has been inserted.

    Empty tree list connected to TFS

  3. Enter titles for backlog items under Title 1 and for tasks, under Title 2. Also, select the corresponding work item type for each.

    Tree list of work items to publish

  4. Publish your worksheet.

    Publish a tree list of work items

    TFS assigns IDs to each work item and creates parent-child links for each task listed under a backlog item.

    Published tree list of work items

Useful tips when working with a tree list:

  • TFS interprets the data in the Title columns to determine the pattern of links between work items. When you publish changes, any of the following conditions can result in an error, an invalid link, or a tree link to be created between incorrect work items:

    • A row between parent and child work items is blank.

    • The Title of a work item is in the wrong column. Make sure you enter a title for each child work item.

    • Within a row, multiple Title columns contain data. Enter text in only one of the title columns within each row.

    • The list was sorted. Don't sort a tree list. Sorting a tree list can change the hierarchical link relationships. If you do sort a tree list, you can recover from this operation by immediately refreshing.

    To resolve an error, see Resolve invalid links in an Excel tree list.

  • You can use item in tree or Outdent item in tree to demote or promote a work item within the tree hierarchy. Verify that the column to the left or right of the parent work item's title is a Title column. The header at the top of the column should read Title <number>, if it does not, add a tree level.

  • A parent-child linked work item can only have one parent. You can't add the same work item task to two backlog items. Instead, you need to define distinct work item tasks.

  • If you receive error TF208104, changes you made to the fields are published, but all changes you made to the link hierarchy are not published. At least one of the link relationships defined for the work item is locked by another process, such as Project Server integration. For more information, see Addressing Error TF208104: Hierarchical Link Relationship Is Locked.

  • When you move a work item, make sure that you select the entire table row.

Q and A

Q: Can I use Excel when I only work in the web client: Team Web Access or Visual Studio Online?

A: Yes. You can’t open a query from the web client; however, you can open the query from Excel. To use Excel, you must get the Team Foundation add-in, which installs when you install Visual Studio or Team Explorer. If you don’t have one of these versions installed, install it now. You can install Team Explorer for free.

Once you’ve installed Team Explorer, open Excel and look for the Team menu or ribbon.

Q: Which list type should I use?

A: Use the following table as a guide for selecting your list structure.

Task

List structure

List refresh

Create and publish many unrelated work items

Flat list

Input list

Perform bulk edits on many unrelated work items

Flat list

Query list or input list

Perform bulk edits on many work items and their dependent or related work items

Tree list

Query list

Perform top down planning and publish parent-child linked work items

Tree list

Input list

View and modify the hierarchy and parent-child link relationships of many work items

Tree list

Query list

Review reports based on a filtered set of work items that change over time

Flat or tree list

Query list

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 Titlenumber column you want to remove—the highest numbered column—and, then Refresh your worksheet.

Tip

Always publish changes that you have made to work items before you remove a tree level. Removing a tree level requires a refresh, which overwrites data in the work item list. You will lose any data you have not published.

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 Get work items icon 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 context menu and choose Select, Table Row, point to the border of the selection, and—when the pointer becomes a move pointer Move Pointer—drag the row to another location.

Tip

When you refresh the work item list, not all formats may be retained. For example, date formats are set by TFS. Any changes you make to a date format field will be overwritten with the date format used by TFS.

Q: How do I quickly update a select set of work items?

A: Select the work items listed in a work item query in Team Explorer, open the context 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 Excel for 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?

A: See these topics to resolve a data conflict, data validation error, or invalid link.

Q: How do I disable the Team Foundation Add-in?

A: If you want to disable the add-in, see Add or remove an add-in.

Q: What other tools can I use to bulk modify work items?

A: You can use Team Web Access or Project.

Q: Want some tips for working in Excel?

A: See Basic Excel tasks.