Using VSTS with Excel for task tracking

I've had reason recently to use the task tracking features in Visual Studio Team System. We've been tracking our bugs for some time in VSTS, and wanted to move over to tracking tasks there as well - it's so convenient to have it all in one place. The blocker was that we had been using spreadsheets to generate glide path or burn down charts of task effort against time, with the tasks lists being maintained in the spreadsheets. This is not ideal as you can't query the lists very easily, it's yet another environment for team members to work in, and the lists get split over a number of spreadsheets (e.g. by feature crew) to make them manageable.

I had heard about the Excel integration that VSTS provides and thought I'd give it a go. An hour later, and I had an Excel workbook refreshing a task list in one worksheet by running a predefined query against work items in VSTS, and then, through the magic of Excel formulae (the SUMIF function was particularly useful) I was generating a glide path chart of actual against planned. I also had another spreadsheet giving me a breakdown of remaining work by person. Now as developers close tasks, or update them with completed work, the changes can be reflected in the chart and table at the press of a button. This gives us real-time data on the progress of the project, allowing us to be more agile.

So, congratulations to the VSTS team. This is good stuff.

Comments (1)

  1. Indeed, VSTS made me think of Excel and other (Office) applications as "views" of project contents. For example, requirements can still be read in a friendly manner in Word (possibly enabled by VSTO), but the project workflow will now be able to do much more interesting things with them since they have meta-information and can be queried from VSTF.

    The possibility to add such integration to your own tool is as impressive as well!


    — AFurtado

Skip to main content