Work in Excel connected to Azure Boards FAQs

All versions from TFS 2013 through Azure DevOps Server 2020 and Azure DevOps Services/Azure Boards support integration with Microsoft Excel.

You must get the Azure DevOps Office Integration add-in available from the Downloads page, Other Tools, and Frameworks. This add-in typically installs when you install any version of Visual Studio or Team Explorer. Also, you need to use Microsoft Excel 2010 or later version, including Microsoft Office Excel 365.

Once you've installed the add-in, open Excel and look for the Team ribbon.

No. macOS isn't supported. Even if you've installed Visual Studio for Mac, connection to Azure DevOps from Excel or other Office client isn't supported.

Yes. To open Excel from the web portal, install the Azure DevOps Open in Excel Marketplace extension. Otherwise, you can open Excel and then open a query that you've created in the web portal or from Team Explorer.

Yes. For TFS 2018 and earlier versions, you can use Microsoft Project to import and update work items in bulk. See Create your backlog and tasks using Microsoft Project.

See Connect Azure Boards to an Office client.

If you want to share an Excel workbook that has work items listed within it, you may want to disconnect the connection to Azure DevOps to prevent accidental publishing of changes by others. You can disconnect the workbook, share it or work offline, and later reconnect the workbook. For more information, see Connect Azure Boards to an Office client, Disconnect a document file from the network.

If your network uses TLS 1.1 or TLS 1.2 security protocol, then you may have network connection issues. To resolve these issues, see Allowed address lists and network connections, Domain URLs to allow.

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

The following macros are only supported from the web portal: @CurrentIteration, @CurrentIteration +/- n, @Follows, @MyRecentActivity, @RecentMentions, @RecentProjectActivity, @TeamAreas. Queries that contain these macros don't work when opened in Visual Studio/Team Explorer, Microsoft Excel, or Microsoft Project.

No. You receive an error message with error code TF208015. You can only view work items defined in the team project that you connect to from Excel. To view work items from other team projects, create a query and open it in a separate Excel workbook. Each Excel workbook can only connect to one team project at a time.

Follow the instructions provided in Bulk add or modify work items with Excel, Import work items, tree list

When you open a direct-links query in Excel, the add-in converts the list to a flat list. While you can modify values for the fields and add work items, you can't view nor modify link relationships.

No. Excel only supports adding and modifying hierarchical links. To bulk edit links of other types, you can use the following clients:

• Use the web portal, to map backlog items to portfolio backlog items which creates parent-child links.
• Use either the web portal or Team Explorer, to modify parent-child links by dragging items within a hierarchical backlog page or within a tree query.

No. You can't use Excel to export and import test case steps or other test artifacts. Instead, use the grid view to bulk edit test cases supported via the web portal.

If you start your worksheet with a New List, you see only a set of default field columns. You can add columns using the Choose Columns on the Team menu.

If you start your worksheet from an existing query, you see all the column fields defined for the query. From there, you can add columns using the Choose Columns on the Team menu. However, your additions don't modify the underlying query.

To resolve publishing errors that arise when working in Excel, see one of the following articles:

• Resolve data conflicts: A data conflict occurs when one team member changes a field value in Excel at the same time another team member changes the same field in Azure Boards.

• Resolve data validation errors: A data validation error occurs if a team member changes a work item in a way that violates the rules for that type of work item.

• Address inaccuracies published for summary values: If you determine that hours are counted twice in reports that contain task hours, you can correct the problem by using the Work Items With Summary Values team query.

If you try to publish a tree list that contains an invalid link, the Work Item Publishing Errors dialog box appears and displays an error message that states why the tree is invalid. When you work with work items in a tree in Excel, the tree must be in a valid state before it can be published. In Excel, an invalid link occurs in a tree list of work items. It occurs if the title of a work item title is missing or occurs in the wrong title column.

To update work items, you must be a member of the Contributors group or have your View work items in this node and your Edit work items in this node permissions set to Allow. For more information, see Change project-level permissions.

Error message TF208000: Duplicate titles

If you add a value to multiple Title columns of a work item, when you try to publish the tree, the error message TF208000 appears in the Work Item Publishing Errors dialog box. The error message specifies the row number of the invalid link.

1. Note the row number that appears in the dialog box.
2. Close the Work Item Publishing Errors dialog box.
3. In the work item list, find the row that the error message specified.
4. Delete the duplicate title or titles so that only one title column has a value.
5. On the Team tab, in the Work Items group, choose Publish.

Error message TF208001: Child work item disconnected from parent

If you remove the title of a child work item, when you try to publish the work item list the error message TF208001 appears in the Work Item Publishing Errors dialog box. The error message specifies the row number of the invalid link. This error message also appears if you create an invalid link structure by putting the title of a child work item into the wrong column.

If you put a title in the wrong column, the resulting tree structure might be valid but not match your intent. The system can't detect this problem, therefore, an error message doesn't appear.

Resolve an orphaned work item

1. Note the row number that appears in the dialog box.
2. Close the Work Item Publishing Errors dialog box.
3. Find the row that the error message specified. The misplaced title might be in this row, or it might be in the next row.
4. Move the title to the correct column to fix the invalid link.
5. On the Team tab, in the Work Items group, choose Publish.

Error message TF208017: Missing Title 1 in the first row

If the first work item in the list has no value in the Title 1 column, the error message TF208017 appears in the Work Item Publishing Errors dialog box when you try to publish the work item list.

1. Close the Work Item Publishing Errors dialog box.
2. Determine why the first work item lacks a Title 1 value. The analysis of the cause of the error determines what you must do to repair the work item list.
3. If the first work item should be at the top of the list, move its title value into the Title 1 column.
4. If the first work item should be lower in the list, move the correct work item to the top of the list. For more information about how to move work items, see Bulk add work items with Excel.
5. On the Team tab, in the Work Items group, choose Publish.

Error message TF208022: Sorted tree list

If you haven't installed Service Pack 1 for Visual Studio 2010 or later version, the following error message appears when you choose Publish after you've sorted the work items in a tree list:

TF208022: Can't publish a sorted tree list. Before you can publish, you must clear any sort criteria applied to this work item list. The order of work items has changed. Removing sort criteria won't return the list to its original order. Verify that all of the parent-child relationships in the tree are correct before you publish.

You can't publish your changes until you re-establish the tree hierarchy. You can resolve this error either by discarding your changes and refreshing the list or by manually restoring the hierarchy and then publishing the list.

Resolve sorted tree list issues

Choose Refresh to discard your changes and restore the tree hierarchy. If you refresh the tree list, you remove all your changes other than the sort. To refresh the tree list, on the Team tab, in the Work Items group, choose Refresh.

Manually restore the tree hierarchy by moving the row entries of child items under their parent items. Then, on the Team tab, in the Work Items group, choose Publish.

Error message TF208102: Excel sort on a tree list

The following error message appears if you sort the work items in a tree list in Excel:

TF208102: You have performed an Excel sort on a tree list. This action removed the modified or newly introduced hierarchical link relationships of the tree. You can still publish the changes you have made to individual work items. After you publish, the list restores to previous hierarchy. In general, you shouldn't sort a tree list whose hierarchy has been modified.|

This message indicates that you can publish the changes that you made to the fields, but all changes that you made to the link hierarchy have been discarded. The tree hierarchy automatically reverts to its original structure.

Publish changes and retrieve the tree hierarchy

1. On the Team tab, in the Work Items group, choose Publish.
2. Choose Refresh.

Error message TF208104: Hierarchical link Relationship is locked

If you publish a worksheet that contains work items that are synchronized with Project Server and whose hierarchical link relationships are locked (), the following error message may appear:

TF208104: You have modified one or more hierarchical link relationships that might be locked by other processes, such as Project Server. Changes that you made to individual work items were published. Changes that you made to locked links were autocorrected.

This error appears when you change the link hierarchy that contains locked links. This message indicates that the changes that you made to the fields are published. All changes you made to the link hierarchy, whether locked or not locked, aren't published and were reverted to their original assignments.

To modify locked hierarchical, make your changes in the enterprise project plan mapped to the project. For more information, see Manage project details.

To publish changes to links that aren't locked:

For work items that aren't synchronized, you can modify the hierarchical link relationship from Team Explorer or the web portal. For more information, see Bulk add or modify work items with Excel.
To modify unlocked hierarchical link relationships in Excel, revise the query that you use to export the work items to exclude all work items with locked links. For example, you can add a clause to the filter criteria to omit items whose Project Server Is Linked field is set to Yes.

No. You can't delete work items from Excel. The only way to delete work items is from the web portal, or with the az boards work-item delete or witadmin command-line tools. For more information, see Remove or delete work items.

Yes. Each worksheet in Excel can contain a different input list or query. However, all worksheets within the workbook must connect to the same project within an organization or project collection.

To bulk add or modify work items in a different project, open a new Excel workbook.

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 resequence 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 —drag the row to another location.

See Show the Developer Tab on the Ribbon.

Related articles

• Bulk modify work items (web portal)
• Bulk import or update work items using CSV files
• Query FAQs
• Create your backlog
• Azure DevOps Office integration issues
• Basic Excel tasks