Tag Archives: Excel

Quick Tips: TFS Demand Management via Pivot Table


One of the meetings I commonly lead is a demand management meeting. In this meeting we look at the work currently in the pipeline for delivery and make decisions about what needs to happen with it. When working with TFS I run this meeting via an excel worksheet which I load a backlog query into. 

The default view for this backlog is a table, which is great for the basics of view/edit but suffers the problem of not easily being able to see the forest for the trees. What I really want is a summary view that enables me to roll up the work dynamically to answer questions raised in the meeting.

How To

To achieve this simply: 

  • Select a cell somewhere within your backlog query
  • Select the design tab of your ‘Table Tools’ ribbon category
  • Click ‘Summarize with PivotTable

The options should already have your work item table selected. Either have the table dropped into a new sheet, or your current and click OK. 

Once the table is up I generally roll up size as a sum, and Work Item Id as a count by a field such as Area to get the overview I need. Additionally allowing filtering by blocked items is handy. 

Next Steps

If you need to drill down to the work items, you can simply use the pivot table tools ‘Expand Field’ function to either have the individual work item id or title values listed as a sub field. 




TFS and Excel – The Basics

Flicking through my blog stats on a Friday shows an interesting trend. On Friday my most common search term is generally “connecting to TFS in Excel”. This tells me 2 things – lots of reports are being written on a Friday, and there’s some demand for a starter guide for TFS and Excel integration!

First things first – licensing. The licensing requirements to connect to TFS from Excel and generate reports is fairly simple. You need a CAL. The people you distribute your reports to as either an xls(x) or hard copy won’t require a CAL, with the restriction that they can’t access the server to refresh/update/create data. For more info on licensing you can get the appropriate white papers for 2005 from here, and for 2008 from here.

Right, now we’ve made sure we’re covered by the right license we need a way to connect to TFS.

1. Install Team Explorer

If you’re using 2005, and have the TFS install media you can find it in the TFC directory of the media. Don’t have access to the install media? It’s also available for download from here.

If you’re looking for the 2008 version, you can get it from here.

The downloads both come in the form of an ISO, so you’ll need some kind of ISO reader. WinRAR and 7-zip will both let you extract what you need to do the install. Once you’ve got the files out, it’s a matter of following the bouncing ball through the setup wizard.

2. Open Excel

Now we’ve got a way to connect to TFS, the next step is to open your flavour of Excel. If you’re working with 2003 you should see a toolbar similar to the image below:

Excel 2003 Team Toolbar

Excel 2003 Team Toolbar

If you’re using 2007, you’ll get a ribbon that looks like this:

Excel 2007 Team Ribbon

Excel 2007 Team Ribbon

If you don’t see one of the above, check your install to make sure it completed successfully. You should see an item in your add/remove programs that looks a bit like this

Team Explorer

Team Explorer


If you’re running Office 2007 you can also check the status of the add-in in Excel. Instructions on the WIT Tools blog here.

3. Get a list of work items

To get the list of work items you’d like to work with you’ll need to click the ‘New List’ button in the toolbar installed with Team Explorer. Depending on your configuration, you might get a credential prompt at this point. If you do, enter the credentials that you usually use to connect to TFS (if you’re unsure, best to contact your TFS admin to find out which set you should be using).

You should then see a dialog asking you to pick a server, and team project to connect to. You’ll need to specify a server if you haven’t already. You can do this by clicking the servers button, then the Add… button.

Connect to TFS Server

Connect to TFS Server

Finally enter the URI and the configuration used to connect to your TFS instance. Once again, if you’re unsure – best to contact your TFS admin for these values.

Once you’ve set up a server, pick the team project you’re interested in reporting on and click OK. Double clicking the team project also works.

You’ll now be prompted to pick a query that will form the basis of your work item list. If you like, you can also pick the ‘Input List’ option to enter work items. The queries listed are those specified against the team project you selected. This means if none of those presented suit your needs and you’re not that keen on re-filtering every time you open excel you can go define a new work item query that you can use later to generate lists.

Once you’ve picked a query and clicked OK, Excel will head off to TFS and start building your list. From here on in you’re in Excel land and can generate pivot tables, charts and filtered lists all based off the data you’ve extracted. You can also add work items by entering data into the blank row at the bottom of your list that is denoted with an asterisk.

If you are adding new data – or updating items via the Excel list, you’ll need to hit the publish button to push this data back to TFS before it’s in the system though. An important thing to note here is that if you refresh your data set before publishing any changes you’ve made will be lost. This also applies to any custom formatting you’ve applied to the work item list (though this scenario is addressed in the 2010 integration model).

Well that’s about that. A basic guide to getting a work item list from TFS in Excel that will hopefully simplify the reporting story that most of us go through at some point.

TFS and Excel – Did you know?

One of my favorite integration points for TFS is Office. Seriously! I find excel the most intuitive and natural way to work with my iteration plans, bug lists and perform general task management. Having talked to a few people about this recently it seems that a lot of the TFS and Office integration is going unused, so I decided to start a living post to catalog some of the Q & A I come across to hopefully increase the use of some of my favorite features….

1. Excel as a task entry system

Excel works really well as a list management tool. What people don’t realise is that it can also be used to create lists for publishing to TFS, and it’s really quite simple.

a. Connect to your team project in Excel and get a list of work items from a query (n.b. you can also pick the ‘Input List’ option here which will open an empty list for entry).
b. Note the empty line at the end of the list marked with a *
c. Start entering your data into the empty line, and be sure to fill out all mandatory fields (you’ll get a prompt later if you don’t, so it’s not a show stopper)
d. Once you’re done entering your new tasks, hit publish to push these back to TFS.
e. If you get a prompt regarding required fields, update your data and try publishing again.

That’s it! You’ve just added a list of work items to your Team Project!

2. Excel, TFS and Concurrency

I was doing a pre-presentation review last week, and a discussion started about the Excel and TFS concurrency model. During the discussion I was asked if 2010 addresses the issue of ‘last-in-wins’ concurrency that exists in 2008. This seemed a little strange to me as I wasn’t aware of the issue but I answered that it did – as I had seen the resolution dialogue in 2010.

Having answered the immediate question, I decided that I’d find out exactly what the case was in 2008 as looking at the work item tracking tables I didn’t see a reason that work item version changes couldn’t be identified between fetch and publish in Excel. It turns out that the concurrency model for 2008 behaves the same (on the surface at least) as that in 2010.

The behavior is that when you publish back to TFS a conflict check occurs. If a non-blocking conflict – users have edited different fields in the same work item, is detected an auto-resolution will occur and the publish will complete. If a blocking conflict – users have edited the same fields in a work item, is detected then a conflict resolution dialog will be shown. This dialog allows you to select the change you want to keep, and then to continue with the publish operation.


So, there’s a couple of features around Excel and TFS integration that I’ve been asked about recently. If you have any other questions on any version of the integration (including any for any of the other Office integration points) leave them in the comments and I’ll do my best to get an answer!