Rosario: Managing hierarchies in Microsoft Excel

  • Article

As a follow up to Gregg's post on querying on links and creating tree queries, I'd like to introduce you to managing work item hierarchies in Microsoft Excel.

Creating work item hierarchy 

There are a few ways you can get started with creating hierarchies in Excel:

  1. Create a tree query in Team explorer and export it to Excel by right clicking on the query and choosing "Open in Microsoft Excel".
  2. Alternately, create a query bound list in Excel and bind it to a tree query. The dropdown that lists the queries now has icons to easily distinguish between tree queries and non-tree queries.
  3. Export one or more items from Team explorer query results view to Excel and then click on either "Add tree level" or "Add child" button in the toolbar. This will automatically bring in all the child work items for items in the list.
  4. Alternately, create an empty ID bound list in Excel and then click on either the "Add tree level" or "Add child" button in the toolbar. Then import one or more work items into the list using the Get work items dialog.This will automatically bring in the selected work items and all their child work items into the list.

You will notice multiple Title columns in the list, one for each level in the hierarchy of work items.

Extending the hierarchy 

  • "Add tree level" extends the tree by adding a new Title column. For ex. you have a tree of features and tasks, and want to break down the tasks further into sub-tasks.
  • "Add child" inserts a new row in the list. For ex. you want to add a new task to a feature in the list. You can also choose to manually insert additional rows into the list. The benefit of using the Add child button is that it will also add a new Title column if necessary. 

Updating hierarchy links 

To add, remove or update hierarchy links, you do not have to use the Links and Attachments dialog for each work item. This can be easily accomplished by moving the title values from one Title column to another. More over, it allows you to create and edit links in bulk.

  • To make a work item a child of its sibling (demote), cut and paste its title into the Title column to the right.
  • To make a child work item a sibling of its parent (promote), cut and paste its title into the Title column to the left.
  • To delete a link between 2 work items, either cut and paste the child work item's title to the very first Title column or delete the entire row from the list and publish.

Let us know what you think about this functionality and how you plan to use it.