Working with TFS work items in Excel

Visual Studio ships with a pretty good query engine that you can use to retrieve a list of work items, but if you need to change the filter criteria frequently or calculate totals for estimates or remaining work, Visual Studio can’t cut it on its own. Fortunately, the integration between TFS and the Microsoft Office suite is fantastic and we can export our data into Excel with a few simple clicks.

Hey, if you’re reading this because you’re a project manager at my office and I sent you this link, you’ll need a few things on your machine for this to work:

1. Download a copy of Visual Studio Team Explorer.  It’s basically a slimmed down shell of Visual Studio without all the code editing features and it includes all the goodies for you to query work items and export to Excel.  If you have Visual Studio installed, you can skip this step.
   • Visual Studio Team Explorer for Visual Studio 2012
   • Visual Studio Team Explorer for Visual Studio 2010
2. You’ll need read permissions to the TFS Team Project. Reach out to your friendly IT support for this.
3. If you’ve never connected to the TFS server before, you’ll also need the TFS Server name, Team Project Collection name, and Team Project name.

There are two ways to get TFS work items into Excel:

• Import TFS Work Items into Excel
• Export TFS Work Items from Visual Studio or Team Explorer into Excel

Importing TFS Work Items into Excel

Let’s assume that you’ve never connected to TFS before. Managing this from Excel is actually pretty easy.

1. Open Excel and create a blank workbook
2. Select the “Team” option from the Ribbon. If “Team” isn’t available in the Ribbon, you haven’t installed Team Explorer. (see above)
3. Select the “New List”.
   
4. The Connect to Team Foundation Server dialog will appear.
   
5. Click the Servers button. The Add/Remove Team Foundation Server dialog will appear.
   
6. Click the Add button to open the Add Team Foundation Server dialog.
   
7. Fill in the server name and details provided to you by IT. You’ll know you’ve entered the right information when the Preview matches the information provided.
   
8. After all the connection information is provided, select the appropriate Team Project Collection, and the Team Project that you want to use.
   
9. Finally a dialog appears that lets you select an existing TFS query.
   
10. Once you’ve selected, the query, click OK. Voila!
    

Exporting TFS work items to Excel from Visual Studio

Exporting your favorite query from Visual Studio to Excel couldn’t be easier. Simply run the query and then select “Open in Microsoft Office –> Open Query in Microsoft Excel”

Famous Last Words

Here’s a few tips with working with work items Excel:

• At any time you can get the latest by clicking the “Refresh” button
• If you make changes, you can push them back to the server by clicking the “Publish” button. (You’ll need write permissions for this)
• This worksheet is tied directly to the server, so don’t forward the spreadsheet to individuals that don’t have access to the data. For this, copy the entire worksheet to another worksheet.