Add or modify work items in bulk by using Microsoft Excel

Azure DevOps Services | Azure DevOps Server | Azure DevOps Server 2022

This article shows how you can save time by using Microsoft Excel when you need to add or modify many work items, add links and attachments to multiple work items, and more. You can also use native Excel features to perform actions such as summing a column, copying and pasting rows, or filling down data into cells.

Use this article for legacy workflows where the Office integration add-in is still required in your environment.

Choose your scenario

• Add new work items as a flat list
• Add hierarchical work items as a tree list
• Update existing work items from a query
• Manage links and attachments

For information about connecting to Excel, see Connect Azure Boards to an Office client. For answers to specific questions about the integration of Excel and Azure DevOps, see FAQs: Work in Excel connected to Azure Boards.

Prerequisites

Use list and query types

Use list and query types to add, modify, publish, and refresh work items in bulk.

• Use a flat list to bulk add or modify several types of work items at the same time, such as backlog items, tasks, bugs, or issues.
• Use a tree list to bulk add or modify work items and their tree-topology links.

Choose the list and query combination that matches your goal:

Query types

Excel supports the following query list sources:

• None: Uses an input list.
• Query title: Uses the work items returned by the selected query.

Azure Boards supports three query structures, each identified by an icon next to the query:

• Flat list of work items: Imported as a flat list query.
• Work items and direct links: Imported as a flat list query.
• Tree of work items: Imported as a tree list.

Excel imports direct-links queries as a flat list because it doesn't support modifying multiple link types.

Tree lists

Use tree lists to bulk add nested work items, such as a work breakdown structure or hierarchical user stories. For example, you can add tasks, subtasks, and bugs in a hierarchy, or link tasks to product backlog items.

Excel supports the following list types:

• Flat list: A simple list of work items with a single Title column. It doesn't support link management.
• Tree list: A hierarchical list of work items with two or more Title columns. It supports creating and updating tree-topology links, such as parent-child links, between work items.

Parent-child links and other tree-topology link types support a hierarchical backlog structure. The work item types that participate in the hierarchy vary by process, as shown in the following images.

Tree list hierarchies

To import a hierarchical list, see Add or import a hierarchical list of work items as a tree list later in this article.

My queries versus shared queries

You can open any query you defined in Azure Boards in Excel, including queries under My Queries and Shared Queries. However, if you plan to share the workbook with other team members, use a Shared Query. Other team members can't access workbooks or worksheets based on personal queries stored under your My Queries folder.

Use Excel features

Most standard Excel features work with Azure Boards-connected worksheets.

Useful Excel actions that still work

• Format cells or apply conditional formatting.
• Cut and paste values between cells.
• Cut and paste a single row.
• Sum columns and use formulas.
• Fill down cells.
• Filter rows.
• Add multiple worksheets in one workbook.

Each worksheet can use a different input list or query. All worksheets in the same workbook must connect to the same project in an organization or project collection.

Behaviors that differ in connected worksheets

• Every cell maps to a work item field and its data type, so field rules are enforced.
• Insert works one row at a time.
• Copy and paste supports multiple rows.
• To move a work item in a hierarchy, cut the entire row and paste it under the new parent.
• Use Outdent and Indent to reposition items in a tree.
• Undo (Ctrl + Z) might not work reliably. If needed, refresh the worksheet.

To stay in sync with Azure Boards, publish and refresh frequently. For more information about Excel features, see Basic Excel tasks.

Sort work items

Use the following guidance when sorting work items in Excel:

• Sort flat lists by using the Excel sort feature.
• Don't sort tree lists. Sorting a tree list changes the hierarchy and can break links between work items.

Choose the option that matches your goal:

• Reorder items in a team backlog: Use the Stack Rank or Backlog Priority field (Agile or Scrum process). Set values, publish the worksheet, and refresh the backlog. Items appear from lowest to highest number. If someone reorders the backlog later, these values can change.
• Keep a custom order in Excel only: Add a custom sort field and sort your flat list by that field. This approach doesn't change the order shown in the team backlog.

Complete tasks in an Excel worksheet

Use Excel-connected worksheets to perform these common tasks:

• Bulk tag updates: Add the Tags field, and then assign one or more tags separated by semicolons (;). For tagging guidance, see Categorize and filter lists and boards with work item tags.
• Plain-text updates to rich-text fields: Enter simple text in rich-text fields. When you update multiple work items in bulk, some existing rich formatting might be lost.
• Offline edits: Work offline, then reconnect and publish changes. For details, see Connect Azure Boards to an Office client, Work offline, and reconnect.

Import work items as a flat list

Use the following procedure to import work items as a flat list:

1. Open Excel and connect to your Azure Boards project. Use one of the four methods provided in Connect Azure DevOps project to Excel.

   Note

   When you connect to Azure Boards in the cloud, the Team Project Collection is automatically selected as there's only one collection associated with your Azure DevOps Services organization. When you connect to Azure Boards in an on-premises server, choose the Team Project Collection before you choose the project.

2. In Excel, start with a blank worksheet. If the Team ribbon isn't available, ensure that you installed the Azure DevOps Office Integration 2019 tool. For more information, see Azure DevOps Office integration issues.

3. Place the cursor in the first table cell. In the Team ribbon, select New List:

   

4. In the New List dialog, select Input list, and select OK:

   

   Your worksheet is now bound to your project as an input list (Query[None]), flat list:

   

5. Enter Title values for the work items you want to add, and select the Work item type values:

   

   The State and Reason fields automatically populate with default values after you select the work item type.

6. Place the cursor in any table cell that has data. In the Team ribbon, select Publish:

   

   After the worksheet publishes successfully, the work items in the table are assigned ID numbers:

   

7. To assign values to other fields, select Choose Columns in the Team ribbon. Add the fields, make the assignments, and publish your changes.

   Tip

   When you add work items to a team backlog, ensure you specify the team's Area Path and Iteration Path. If you need to add Area Paths or Iteration Paths, select Edit Areas and Iterations. This link opens the Project settings page in a web browser. For more information, see Define Area Paths and assign to a team and Define Iteration Paths and configure team iterations.

8. You can add more information to a work item in the list by opening the item in the web portal from Excel.

   First, publish any unsaved changes to the list. Select the work item and then select Open in Web Access in the Team ribbon:

   

   A web browser opens and displays the work item.

   If you make changes to the work item, immediately refresh your worksheet to capture the changes.

Import work items as a tree list

You can add a hierarchy of work items linked by using parent-child links or other tree topology link types.

1. Start from the step in the previous procedure where you bound your worksheet to your project.

2. Convert the flat input list worksheet into a tree list. Select a cell within the list and then select Add Tree Level in the Team ribbon:

   

   If the Add Tree Level option isn't available, your list is a query list. To convert your list to a tree list, you must first reconfigure your list to an input list.

3. In the Convert to Tree List dialog, select the link type to use when you add work items to a hierarchy, and then select Convert. The most common choice is Parent-Child. You can only select from tree topology link types. For more information, see Link type topologies and restrictions.

   

   The List type changes to Tree, and another Title column appears. The initial Title column is labeled Title 1 and the new Title column is labeled Title 2:

   

4. To add more levels to the hierarchy, select Add Tree Level again. For example, if you plan to add a hierarchy of Epics, Features, and User Stories, you need three Title columns.

   If you want to add tasks, add another tree level to have four Title columns. To remove a column, see Remove a tree level.

5. Save your Excel file.

6. Enter the Work Item Type and Titles for the hierarchy you want to import. The State fields automatically fill in with default values after you select the work item type.

   

7. Place the cursor in any table cell that has data. In the Team ribbon, select Publish.

   After the worksheet publishes successfully, the work items in the table are assigned ID numbers.

   In the background, the link type you selected is used to link each work item in the hierarchy. Epics are linked to Features. Features are linked to User Stories.

8. To check the links, select a work item and select Links and Attachments in the Team ribbon. The following example shows the child and parent links created for an imported Feature:

   

9. To add a child to a work item in a new table row, select the work item, and then select Add Child in the Team ribbon:

   

10. To assign values to other fields, select Choose Columns in the Team ribbon. Add the fields, make the assignments, and publish your changes.

11. To modify the tree hierarchy, cut the entire row for a work item and then paste the row under the new parent in the tree. When you publish the change, old hierarchical links are deleted and new hierarchical links are created.

    You can use the Indent item in tree and Outdent item in tree actions to demote or promote a work item within the tree hierarchy. These actions are available to content in any column labeled as Title <Number>. If you want to work with data in a column that doesn't have the Title <Number> label, add a tree level for the column.

Remove a tree level

To remove a tree level safely:

1. Publish all pending changes.

2. Clear all values under the Title <Number> column you want to remove.

   The column must be the highest-numbered Title column in the tree.

3. Refresh the worksheet.

   Excel removes the now-empty Title column. If you try to delete the column manually, Excel returns an error.

Tips for working with a tree list

Excel uses the Title columns to build tree links. Before you publish, check for these common issues:

• Blank rows in the hierarchy: A blank row between items can break or misapply links.
• Title in the wrong column: Enter each child work item title in the correct Title <Number> column.
• Multiple Title columns populated in one row: Enter text in only one Title <Number> column per row.
• Sorted tree list: Don't sort a tree list. Sorting can change hierarchy relationships. If you sort by mistake, immediately refresh to recover the original structure.

To troubleshoot invalid links, see FAQs.

A parent-child linked work item can have only one parent. If one task needs to appear under multiple backlog items, create separate tasks.

Update work items in bulk with a query list

Use a query list when you want to update many existing work items at once.

1. Create a query that returns the work items you want to update. For more information, see Create and save managed queries with the query editor.

2. Open Excel and connect to your Azure Boards project. Use one of the four methods provided in Connect Azure DevOps project to Excel.

3. Continue with the path that matches where you started:

   • Path A: You opened the query from the web portal or Visual Studio

     1. Select Choose Columns in the Team ribbon to add any fields you want to edit.

     2. Update values, and then publish your changes.

   • Path B: You started in Excel

     1. Open a blank worksheet. You can also add a worksheet to an existing workbook if you use a query from the same bound project.

     2. Place the cursor in the first table cell. In the Team ribbon, select New List:

        

     3. In the New List dialog, select Query list, and then select the query:

        

        The icon next to each query shows the query type. For details, see Query types.

        Select OK.

        The worksheet is now bound to your project and populated from the query:

        

     4. Make your updates, and then publish changes.

        If you're working with a tree list, review the guidance in Import a hierarchical list of work items, earlier in this article.

Enable Tree commands

If Tree commands like Add Child and Indent are unavailable in the Team ribbon, your worksheet is configured as a flat list or a query list:

To enable Tree commands, reconfigure the worksheet to one of these list types:

• Input list
• Query list based on a tree query

For step-by-step instructions, see Change your list type or query.

Change your list type or query

Use this procedure when you need to change how your worksheet is populated.

You can:

• Change a flat list to a tree list
• Change from a query list to an input list
• Change from an input list to a query list
• Change the query your worksheet references

If you need a tree list and Tree commands aren't available, first convert the worksheet to an input list.

Convert a query list to an input list

1. Select Publish to ensure all changes in your worksheet are saved.

2. On the Team ribbon, select Configure > List:

   

3. In the Configure List Properties dialog, select Refresh work items only, and then select Apply:

   

   The worksheet is now an input list.

Convert an input list to a query list

1. In the Configure List Properties dialog, select Refresh from query.

2. Select the query to populate the worksheet, and then select Apply:

   

Add existing work items to your worksheet

Use the path that matches your worksheet type.

If your worksheet uses a query list

Modify your query to include the work items you want, and then refresh the worksheet.

If your worksheet uses an input list

Complete the following steps:

1. In the Team ribbon, select Get Work Items:

   

Get Work Items dialog

In the Get Work Items dialog, choose how you want to find work items:

If the work items are in another project, first select the project. Then choose one of these options:

• Saved Query: Use a saved query that returns the work items you want.

• IDs: Use this option when you already know the work item IDs.

  • In the IDs box, enter IDs separated by commas or spaces.

• Title contains: Find work items by a word or phrase in the Title field. In the and type list, select the work item type to return.

1. Select Find.

   • Results include only work items from the selected project and work item type.
   • The checkmark indicates a work item already in the worksheet.
   • To sort results, select a column header. You can also resize columns to see more details.

2. In the results list, select the checkbox for each work item you want to add.

   • Include any related child nodes you also want in the worksheet.
   • Use Shift + Select to select a range, and Ctrl + Select to add or remove individual items.
   • Use Select All to select every returned work item.

3. Select OK. The worksheet list updates to show your changes.

Add or remove column fields

When you create a worksheet by using the New List action in the Team ribbon, the process generates a set of default field columns, such as Work Item Type, ID, and State. If you create your worksheet from an existing query, the column fields in the worksheet match the fields defined in the query.

In both scenarios, use the Choose Columns action in the Team ribbon to add and modify columns. Remember that if you create your worksheet from a query, your column changes don't affect the underlying query.

1. Select Column Options to add fields and assign values to other fields:

   

   • To filter the fields based on work item type, select the Work item type.

   • To add a field to the worksheet set, select the field in the Available columns list, and then select the right arrow.

   • To remove a field from the worksheet set, select the field in the Selected columns list, and then select the left arrow.

   • To change the position of a field in the column sequence, select the field and reposition it by using the up arrow and the down arrow. The up arrow moves the selected column toward the left in the set of table column headers. The down arrow moves the column toward the right in the set of table column headers.

   • You can add rich-text fields like Description. Keep in mind that some rich formatting might be lost in the published worksheet.

   Select OK to apply the column changes.

2. After the fields appear in the worksheet, assign values and publish your updates. When you work with identity fields that accept user accounts, follow the guidance in the next section, Select user accounts.

3. Save your worksheet.

Select user accounts

Use Select User to find accounts and assign values to person-named fields. This feature is especially useful for large teams and also shows your most recently used (MRU) values.

1. Confirm that Visual Studio 2015.1 or later is installed. If needed, download and install a newer version.

2. In your worksheet, select an identity or person-named field. This selection enables Select User in the Team ribbon:

   

   Identity and person-named fields map to user accounts, typically from Microsoft Entra ID, Windows Server Active Directory, or a Workgroup.

3. In the Assign User dialog, start typing a user name and select the matching account from the filtered results.

   

   • Enter a letter to jump to names that begin with that letter. Only user names are recognized, not aliases.

   • Excel stores recent selections so you can choose user accounts directly from the field next time.

   

Link work items

Use the Links tab in the Links and Attachments dialog to:

• Review existing links for a selected work item
• Add links to one or more work items
• Delete links.
• Open a linked work item in the web portal
• Edit the link type for an existing link
• Add columns and sort the list on the Links tab

For more information about linking work items, see Link user stories, issues, bugs, and other work items.

View and add links

The Links and Attachments dialog doesn't support bulk updates to work item links. For bulk updates to tree-topology link types, use a tree list.

Link one work item to other work items

1. Select the work item to update in the worksheet, and then select Links and Attachments in the Team ribbon.

2. In the Links and Attachments dialog, select the Links tab, and then select Link to.

3. In the Add link to <Work Item> dialog, identify the work items you want to link:

   1. Select the Link Type. The same link type is applied to all selected work items.

   2. Select the identifiers (Work item IDs) for all work items to link. For more information, see Find work items to link.

   3. Select OK.

   

4. Select Publish in the Team ribbon, and then close the dialog.

Link the same target work items to multiple work items

1. Select a range of work items in the worksheet by using the Shift + Select keyboard shortcut. Use Ctrl + Select to add or remove work items from the selected group.

2. Follow the procedure for the Links and Attachments and Add link to <Work Item> dialogs to identify the work items to link to.

3. Publish your changes. The identified work items are linked to all selected work items in the worksheet.

Find work items to link

In the Add link to <Work Item> dialog, open Choose Linked Work Items to select one or more target work items. If you want to use a saved query, first define the query.

1. In the Add link to <Work Item> dialog, select Browse (Visual Studio):

   

2. In the Choose Linked Work Items dialog, select the method to get the work items to link:

   

   Configure this dialog the same way as Get Work Items. For more information, see Add existing work items to your worksheet.

Add columns to the Links list

You can change the columns shown in the Links list:

1. Select a work item in the worksheet, and then select Links and Attachments in the Team ribbon.

2. In the Links and Attachments dialog, select the Links tab, and then select Column Options.

3. In the Column Options dialog, select the fields you want to display:

   • To add fields, select one or more fields in Available columns, and then select the Add selected column arrow.

   • To remove fields, select one or more fields in Selected columns, and then select the Remove selected columns arrow.

   Configure this dialog the same way as Get Work Items. For more information, see Add existing work items to your worksheet.

4. To reorder the list of links in the Links and Attachments dialog, select a column to sort the list on that field:

   

Open a linked work item

You can open a linked work item from Links and Attachments.

• In the Links tab, right-click a linked work item, and select Open Linked Item:

  

The linked work item opens in your web portal.

Edit the link and change the link type

You can edit any listed link, including the link type and target work item.

1. In the Links and Attachments dialog, on the Links tab, select the link to update, and then select Edit Link.

2. In the Edit Link dialog, change the link type as needed:

   

3. To change the linked work item, enter the work item ID, or select Browse to locate it.

   Configure fields in this dialog the same way as Get Work Items. For more information, see Add existing work items to your worksheet.

Add attachments

Follow these steps to add attachments to your work items:

1. Select the work item, and then select Links and Attachments.

2. In the Links and Attachments for <Work Item> dialog, select the Attachments tab.

3. Select Add, and then select the file you want to attach:

   

   Select OK.

4. Select Publish, and then close the dialog.

Use this same process to add the same attachments to several work items at the same time. In the worksheet, use the Shift + Select keyboard shortcut to select a range of work items, and Ctrl + Select to add or remove work items from the selected group. After you select the attachments, the Publish process applies the files to all selected work items.

Create a report

For flat-list queries, you can create charts and reports directly in the web portal. For more information, see Track progress by creating status and trend query-based charts.

To create an Excel report (on-premises Azure DevOps Server), select New Report:

For additional details, see Create a report.

Resolve publishing errors

Use the following references when publish or refresh operations fail in Excel:

• Resolve data conflicts: Another update changed the field value in Azure Boards after your last publish.

• Resolve data validation errors: A field value violates the rules for the work item type.

• Resolve invalid links in a tree hierarchy: Tree links were broken by a move or sort operation. Review the error and reposition items to restore the hierarchy.

• Address Error TF208104: Hierarchical Link Relationship Is Locked: Field changes publish, but hierarchy changes don't, because a process such as Project Server integration locks one or more link relationships.

Troubleshoot common issues

Use this section as a quick recovery guide while you work in Excel.

• Team ribbon isn't available: Verify that Azure DevOps Office Integration is installed. For more information, see Azure DevOps Office integration issues.
• Publish fails with validation errors: See Resolve data validation errors.
• Publish fails with data conflicts: Refresh the worksheet, reapply changes, and publish again. For more information, see Resolve data conflicts.
• Tree links are invalid after edits: See Resolve invalid links in a tree hierarchy.
• TF208104 hierarchical link is locked: See How do I resolve invalid links in a tree list.

Use AI to add or modify work items without Excel

If you configure the Azure DevOps MCP Server, you can describe bulk add and modify tasks in natural language instead of publishing and refreshing an Excel worksheet.

Use the following examples as templates. Replace project names, IDs, users, sprints, and area paths with your values.

Related content

• Integrate Azure Boards and GitHub
• Import or update work items in bulk with CSV files
• View and add work items