Azure Boards | Azure DevOps Server 2020 | Azure DevOps Server 2019 | TFS 2018 - TFS 2013
Find answers to frequently asked questions when using Microsoft Excel to add or modify work items defined in Azure DevOps.
If you're having connection issues, make sure you meet the prerequisites as listed in Bulk add or modify work items with Excel. Also be sure you've reviewed the information in Azure Boards and Office integration.
Connect and versioning support
What versions of Azure DevOps work with Excel?
All versions from TFS 2013 through Azure DevOps Server 2020 and Azure DevOps Services/Azure Boards support integration with Microsoft Excel.
What do I need to use Excel to add or modify work items?
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.
Microsoft Project Integration and the TFSFieldMapping command aren't supported for:
- Visual Studio 2019 and Azure DevOps Office® Integration 2019
- Azure DevOps Server 2019 and later versions, including Azure DevOps Services.
However, full support for Microsoft Excel integration is maintained and is the recommended alternative.
Once you've installed the add-in, open Excel and look for the Team ribbon.
Can I use Excel on my Mac?
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.
Can I open a query in Excel from the web portal?
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.
Can I import or update work items without using Excel?
Yes. You can do a bulk import of new work items or update existing work items without using Excel. See Import work items.
No. For Azure DevOps Server 2019, Excel is the only supported method.
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.
How do I connect an existing Excel workbook to Azure DevOps?
How do I share an Excel workbook with others?
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 details, see Connect Azure Boards to an Office client, Disconnect a document file from the network.
How do I connect when special protocols are in use on my 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.
How do I disable the Team menu?
If you want to disable the add-in, see Add or remove an add-in.
What query macros aren't supported in Excel?
The following macros are only supported from the web portal: @CurrentIteration, @CurrentIteration +/- n, @Follows, @MyRecentActivity, @RecentMentions, @RecentProjectActivity, @TeamAreas. Queries that contain these macros won't work when opened in Visual Studio/Team Explorer, Microsoft Excel, or Microsoft Project.
Can I view queries that list work items from different team projects?
No. You will 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.
Work with linked work items
How do I publish to a tree?
Follow the instructions provided in Bulk add or modify work items with Excel, Import work items, tree list
Why does my direct-links query appear as a flat list in Excel?
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.
Can I bulk-edit link types other than tree-topology link types?
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.
- Use the az boards work-item relation add command.
Work with test work items
Can I bulk add or edit test cases with Excel?
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.
Publish and refresh
How can I show other fields?
If you start your worksheet with a New List, you'll 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'll 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.
How do I resolve publishing issues?
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.
Resolve invalid links in a tree hierarchy: An invalid link occurs if a team member views work items in Excel as a hierarchy or tree list, and moves a work item or sorts the list so that it breaks the dependencies between work items. You can resolve this error by reviewing the error message and repositioning work items to reflect the work item structure.
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.
Can I delete work items from Excel?
No. You can't delete work items from Excel. The only way to delete work items is from the web portal or the
az boards work-item delete command-line tool. For details, see Move, change, or delete work items.
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 details, see Remove or delete work items.
Use built-in Excel functions
Can I use multiple worksheets within Excel?
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.
Can I use Excel cut and paste functions?
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.
When you refresh the work item list, not all formats may be retained. For example, date formats are set by the server data store. Changes you make to a date format field are overwritten with the date format used by the server.