Configure Work Items in a Tree List in Office Excel

Article
02/04/2013

You can more easily and effectively add work items and modify the tree structure of work items that are related using parent-child or tree links by using an Office Excel tree list.

Work items in a tree list are displayed in multiple columns in Office Excel. By using a tree list, you can do any of the following actions:

Add to and remove levels from the tree hierarchy.
Promote and demote work items within the tree hierarchy
Move work items to become the parent or child of other work items
Update work item fields
Publish changes made to work item fields or the tree hierarchy

Caution

A title for a work item that is not in the correct column can establish incorrect linking or cause a validation error. A work item list is a table in an Office Excel worksheet, so when you move work items, you must make sure that you select a whole table row. An empty row or Title column, depending on where it is located, can also cause broken links or validation errors.

When you publish your updates, changes made to the tree hierarchy cause tree links to be created or deleted. These links are created and stored in the work item database as parent and child links between work items.

Work item tree's representation in Excel

Topic Contents:

Adding a Level to a Work Item Tree List
Adding a New Child Work Item
Removing Levels from the Tree
Moving a Work Item in the Tree
Demoting a Work Item to Child Status
Promoting Child Work Items from Child Status
Publishing Additions and Changes to Work Items

Important

You must not sort on any column when working with a tree list in Office Excel. Sorting on a column can cause link errors to occur that you must resolve before you can publish the work items.

Required Permissions

To perform these procedures, 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 Team Foundation Server Permissions.

Adding a Level to a Work Item Tree List

If you create a work item list that is tied to a tree query, and the work items already include at least two levels in the parent-child hierarchical structure, the work item list opens as a tree list with more than one Title column. Otherwise, you must add Title columns after you create the work item list. You also must add tree levels as you define more levels in the work breakdown structure.

To add a level to a work item tree list

Click a cell in the work item list.
On the Team tab, in the Tree group, click Add Tree Level.

Note

If the commands in the Tree group are not available, then the work item list is a query list that is tied to a flat-list query. For information about how to turn this kind of list into a tree list, see Refresh Work Items and Change the List Type in Office Excel.
If the Convert to Tree List dialog box appears, in the Choose the type of link to create the tree list, click Parent-Child.
Click Yes to confirm that you want to convert the list.

One or more new Title <number> columns appear in the Office Excel work item list. The number of columns that appear depends on how many levels of parent and child work items already exist in the work item list.

Adding a New Child Work Item

To create a child work item

Open or create a work item tree list.
In the tree list, click a cell in the work item to which you want to add a child work item.
On the Team tab, in the Tree group, click Add Child.

Office Excel inserts a blank row under the work item that you selected and selects a cell in the Title column where the title of the child work item should be.

Note

If the Tree group commands are not available, then the work item list is a query list that is tied to a flat-list query. For more information about how to configure a flat work item list into a tree list, see Refresh Work Items and Change the List Type in Office Excel.
Type a new title for the child work item.
In the Work Item Type field, select the work item type. If you cannot see the Work Item Type column, you must add it to the work item list.

For more information about how to add a column, see Add or Remove Columns in a Work Item List.
Type or select values for additional fields to define the work item.
Add child work items by using one of the following methods:
- Select the parent work item and, on the Team tab, click Add Child.
- Right-click a work item underneath where you want the new work item, point to Insert, and then click Table Row Above or Table Row Below, as required.
Note

The Table Row Below command appears on the shortcut menu only if you have selected the last work item in the list.
(Optional) When you have defined the tree to a level of detail that you want, on the Team tab, in the Work Items group, click Publish.
(Optional) Save the work item list.

Removing Levels from the Tree

The procedure listed below removes empty Title columns that are to the right of Title columns that contain data. An empty Title column that is to the left of a Title column that contains data is a structural error that you must correct manually by moving child work item titles.

To remove a tree level

Publish changes that you have made to work items before you remove a tree level.

Important

Removing tree levels requires a refresh, which overwrites data in the work item list. If you have not published the data, you will lose it.

Make sure that there are no values in the column. The column must be empty before you can remove it.
On the Team tab, in the Work Items group, click Refresh.

Moving a Work Item in the Tree

To drag a work item

Right-click the work item or work items that you want to move, point to Select, and click Table Row.
Point to the border of the selection. When the pointer becomes a move pointer , drag the row to another location.

To cut and paste a child work item

Right-click the work item that you want to cut, point to Select, and click Table Row.
Right-click the work item again and click Cut.

You can also press CTRL+X.
Select the row underneath where you want to move the work item. To select the whole row, point to the left end of the row, and click when you see the arrow .
Right-click the row that you have just selected, and click Insert Cut Cells.

Demoting a Work Item to Child Status

To demote a work item to child status

Verify that the column to the 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.
If necessary, you can move the work item that will be a child work item to a row under the parent work item.

If the work item is the only child of the parent work item, you must place it in the next row below the parent. Otherwise, you can place it in a row below existing child work items of the parent.
Select the cell that contains the title of the work item that will be the child.
On the Home tab, click Cut.

You can also press Ctrl+X.
In the same row, select the cell that is in the Title column to the right of the Title column that contains the parent work item title.
On the Home tab, click Paste.

You can also press Ctrl+V.

Promoting a Child Work Item from Child Status

To promote a work item from child status

Select the cell that contains the title of the work item that you want to promote from child status.
On the Home tab, click Cut.

You can also press CTRL+X.
In the same row, select the cell that is to the left of the work item title.
On the Home tab, click Paste.

You can also press CTRL+V.

Publishing Additions and Changes to Work Items

You must publish the work items for them to be stored in Team Foundation Server and accessible to team members. When you successfully publish results, an ID is assigned to each new work item. In addition to the work items being published, links are defined for each of the parent-child relationships that are represented in the tree list.

Note

Publishing a work item list can take several minutes to complete.

To publish additions and changes to work items in a tree list

Review the worksheet for any required fields that are blank. For tree lists, correct any of the following errors:
- A row between parent and child work items that is left blank.
- The title of a work item that is in the wrong column.
- More than one Title column for the same work item which contains data.
Click the Team tab.
In the Work Items group, click Publish.