Managing Work Items with the Excel Add-In Primer

Microsoft provides a comprehensive set of online documentation and guides for using TFS/VSTS, including the Office Add-in. However, I wanted to provide a blog post with some additional tips we’ve developed based on real-world experiences. Hopefully one or two these will be beneficial to others attempting to use the Office Add-In for TFS/VSTS.

Team Foundation Server/Visual Studio Team Services (TFS/VSTS) has long been a cornerstone development tool for software development teams. In the past couple of years, Microsoft has been enhancing this tool to provide additional features for work management using Agile project approaches. TFS/VSTS now has a wide range of features to manage the work of a team in terms of lists, e.g., product backlog/user stories, bugs, tasks, features, etc. Although TFS/VSTS has plenty of built-in features for managing these lists, there are times when you really just want to be able to manipulate these lists in a spreadsheet. Thankfully, Microsoft has also provided an Office Add-in that provides additional capabilities for managing TFS/VSTS lists using familiar tools like Microsoft Project and Excel (this post focuses on Excel use with VSTS).

When you need to add/update list items in bulk or generate quick reports on list content, this Add-in can be invaluable. A common use case for our team is to build product backlogs during an initial envisioning/sales effort with a customer to create cost estimates. Due to portability and flexibility considerations, we typically use a spreadsheet for this purpose. We then need to import this backlog (with lots of supporting information) into a project in VSTS so that the project team can align their work with initial expectations set with our customer during the sales process. The TFS/VSTS Office Add-In is built for this type of need.

The Nickel Tour

Before describing some tips, let me summarize the how the Office Add-In works for any newbies out there.

The main idea is to create a list on a tab of an Excel workbook to either “add/update” or “read/update” work items in VSTS. You can have multiple tabs for this purpose within a single workbook as long as they all point to the same VSTS project. Step 1 of creating a list for this purpose in Excel is to click the “New List” button on the “Team” ribbon tab as a first step. If you don’t see the “Team” tab in the Excel ribbon, you need to download and install the Add-in (see links below).

When you click “New List” you will be prompted to select the “type of work item list you want to create”:

  • Add/Update – Pick the “Input List” work item list type. This is the ONLY way to add new items. You can also selectively pull items down from VSTS, update locally then publish back to VSTS using this list type.
  • Read/Update – Pick the “Query list” work item list type. This allows you to download a filtered list of work items (based on a query you define in VSTS) for reading/reporting purposes or you can update the items locally and then publish back to VSTS using this list type.

The general process flow for using the TFS/VSTS Office Add-in is…

  1. Create a new workbook in Excel.
  2. Click “Team” ribbon menu.
  3. Select a VSTS project to connect with in the “Connect to Team Foundation Server” dialog box.
  4. You will the see the “New List” dialog box pop-up where you will select the type of list you wish to use for the data connection. This is the same pop-up you will see if you create a new worksheet in the workbook and press the “New List” button on the Team ribbon.
  5. Data from the query (if you selected a Query List type of work item list) will be populated in the current worksheet within the workbook. Additional data column can be added to the worksheet list using the “Choose Columns” button.
  6. Use Refresh/Publish buttons on Team ribbon toolbar for download/upload functions. (CAUTION: See tip #3 below before pressing the “Publish” button.

If you are brand new to using the Office Add-In, you can start your learning journey at the following links:

Add-In Download: (look for the download link in the “Team Foundation Server Office® Integration 2017” section of the page)

Basic User Guide:

Tip #1 – Use a “Staging” worksheet to fix errors before they occur

The publish feature has a pretty good UI for reporting/identifying errors during “Publish” operations. (Remember that this is the only function that allows you to make changes in the data stored in VSTS). However, the Publish function will skip rows in your data where it encounters upload errors. This can create a confusing situation as you sift through rows in your spreadsheet to make corrections with some rows having already been uploaded and others not. Sometimes this will also cause item numbers for newly uploaded items to be out of sequence in VSTS (if that is something you are concerned with).

Bottom line…I really try to correct errors before they occur in the Publish function by using standard Excel data validation features and functions BEFORE any attempts to Publish new work items. To make this process cleaner/easier, I typically use a separate “Staging” worksheet where I perform all data validation and manipulation. Then I copy data columns from the “Staging” worksheet to the worksheet I am using for Publishing, typically a list type of “Input List”.

The above screen snap shows a sample staging worksheet with a standard type of validation, i.e., ensuring that a field width is less than the maximum allowed by VSTS. In this case, the column with the red highlighted title is used to perform a validation on the title field to ensure that it is less than 255 characters. Standard Excel conditional validation is used to highlight specific rows in red where the title is greater than 255 characters (note that wrapping characters are not shown in the figure above). In these situations, I would manually edit affected titles to trim characters either by truncation or editing/abbreviating before copying to an Input List worksheet.

The screen snap below shows an “Input List” worksheet (before executing the publish function) that could be used to publish information to VSTS. The green highlighted column headers indicate columns that have been populated by manually copying from the staging worksheet above.

The screen snap below shows the same data after pressing the “Publish” button.

Tip #2 – Overload fields if you want to stick with standard work items

When creating a product backlog spreadsheet in our envisioning/sales process, we often collect data in columns that aid in planning discussions with customers but don’t necessarily correspond to data fields in VSTS. In this situation, we will typically “overload” fields before uploading to VSTS by combining multiple staging worksheet columns into a single column.

The screen snap below shows overloading of the “Description” field that will be uploaded to VSTS. Overloading is accomplished by combining multiple fields into a single field using standard Excel string concatenation functions. Of course, VSTS has a capability for defining new work item processes and data templates with new data fields. However, if you don’t have time for that or want to stick to standard templates this tip can help.

Tip #3 – Measure twice – cut once when using the “Publish” button

This is a small tip…but by far the most important! The old carpenters saying of “measure twice cut once” (referring to ruining a piece of wood if cut too short) is appropriate for the Publish function. You can do massive damage to your lists in VSTS if you inadvertently change rows in an input list and then publish to VSTS. Remember – all rows in the input list are uploaded and overwrite existing rows with the same IDs in VSTS.

Tip #4 – Start slow using flat lists

Newbies just starting out with the Publish function should use “flat” lists as opposed to “tree” lists. Tree Lists allow you to upload multiple records and the hierarchical relationships between records. In the screen snap below, a backlog item and related tasks have been uploaded to VSTS.

Tree Lists can be powerful ways to enter and manipulate data. However, for beginners, they can also create massive confusion if erroneous data is entered. By default, the “New List” button will create a Flat List. You can use the “Add Tree Level” button to convert this into a Tree List. Again, Flat Lists are a good/simple starting point for beginners.

Tip #5 – Keep your data in sync – refresh early and often

OK – I’ll admit this tip is already available on the VSTS website at the “Basic User Guide” link mentioned above. However, it is important enough to mention here again. This tip relates to the following best practices to ensure your spreadsheet data is in sync with the online VSTS data store:

  1. When you first open a saved worksheet, use Refresh icon in Excel on Team ribbon (Refresh) to download the latest data from the data store.
  2. To avoid data conflicts, publish your additions and modifications often.
  3. To prevent loss of data before you publish or refresh, save your workbook periodically.

If you ignore the above, you may notice strange behavior when attempting to publish information or worse, you may overwrite information others are entering in VSTS while you were editing your spreadsheet!

Tip #6 – Name your tabs according to list type and purpose

This simple tip is a good way to help others understand the structure of spreadsheets you’ve created for use with the TFS/VSTS Office Add-In. In general, tab names should provide clues as to the type/use of the worksheet as indicated in the screen snap below.

Tip #7 – Use values in cells, not formulas

The Publish function does not automatically calculate values for formulas when uploading to VSTS. Instead, it will simply upload a stripped version of the formula itself. This can be simply overcome by copying and pasting in place as values in your input list worksheet prior to publishing.

Tip #8 – Leverage pivot tables for simple reporting

If you are an Excel power user, you are most likely familiar with the power of pivot tables for visualizing data. These are also handy for providing quick roll-up summaries of list items. We commonly use this to generate on-the-fly roll ups for total story points in various columns of our Kanban board as shown in the screen snap below.

Hopefully one or more of the above tips will help you have a more enjoyable experience with this great tool!  Please feel free to share any of your tips in the comments below.

Bob MorrisManaging Work Items with the Excel Add-In Primer


Join the conversation

Join the conversation

This site uses Akismet to reduce spam. Learn how your comment data is processed.