In Scrum, the taskboard is a visual display of the progress of the Scrum team during a sprint. It presents a snapshot of the current sprint backlog, allowing everyone to stay synchronized and focused on the work to be done. Most of the time, smaller teams are distributed across multiple locations, and in these situations, tracking work with a digital tool helps distributed teams synchronize more effectively. Some of us are lucky enough to land on green field projects, which gives us the opportunity to start tracking the requirements of work items from inception. Other times, projects are planned in tools that don't natively support integration with Azure DevOps Server. Luckily, most planning tools allow you to extract the data to Excel. Azure DevOps Server natively supports importing work items through Excel, but the challenge is mostly working out which fields in the spreadsheet should map out to work items in Azure DevOps Server. In this recipe, we'll learn how to import requirements from Excel into work items and refresh updates from work items back into Excel.
If you don't have Office Excel, install it. For Azure DevOps Server 2019, you'll need Office 2013 or a later version. The Excel plugin for Azure DevOps Server is installed by installing one of the latest editions of Visual Studio or the Azure DevOps Server Standalone Office Integration installer. Azure DevOps Server Standalone Office Integration supports connecting to Azure DevOps Server from Excel, Microsoft Project, and the PowerPoint-based storyboard tool.
If you don't intend to install Visual Studio but need Office integration, download and install Azure DevOps Server Standalone Office Integration (free) from https://www.visualstudio.com/downloads/. Once the installation is complete, the Excel Plugin will show up under the Team ribbon in Excel, as shown in the following screenshot:
If you don't see the Team ribbon, perform the following steps to enable it:
- Click the
Filetab in Excel and chooseOptions. - In the
Categoriespane, clickAdd-ins, and verify thatTeam Foundation Add-inshows up in theDisabled Application Add-inssection. - In the manage box, select disabled items and click
Go. - Select the
Azure DevOps Server Add-inand clickEnable. Finally, exit the dialog by clickingClose.
Note
If you are continuing to run into issues with Add-in not showing up in Excel, you may be able to resolve the issue with the procedures provided at the following link: https://docs.microsoft.com/en-us/vsts/work/backlogs/office/tfs-office-integration-issues.
Now that we have the Azure DevOps Sever excel plugin installed, in this section we'll learn how to use it.
Start by performing the following steps:
- Launch Excel and start with a blank sheet. Navigate to the
Teamribbon. - Click on
New Listto connect to your project in TFS.
- If you are connecting to Azure DevOps Server from Excel for the first time, you will have to add your server details to the list of recognized servers. The steps for this are shown in the following screenshot:
- Select the
PartsUnlimitedteam project and clickConnect:
- When asked which type of work item list you want to create, choose
Input list. An Input list gives you a blank template that is linked to your team project:
- Your worksheet will now be bound to your team project as a flat list. What this means is that you can add work items to the team project from the worksheet or add work items to the worksheet from the team project. Fill out the details of the work items you want to add and their work item type. The Excel plugin defaults the list type to flat, but you can change it to a tree list if you wish. A tree list allows you to create and view hierarchically linked work items, like so:
- Publish the changes by clicking the
Publishbutton from the Team ribbon.
To validate whether the changes have been synchronized to Azure DevOps Server, launch the web portal in a browser, and navigate to the work hub in the PartsUnlimited team project. The newly added work item should show up under the features backlog, as shown in the following screenshot:
Follow these tips to keep your work in sync:
- When you first open a saved worksheet, use the
Refreshbutton in Excel on the Team ribbon to download the latest data from the data store - Enter data for additional fields by adding columns to the worksheet using the
Choose Columnicon in Excel on theTeamribbon - To avoid data conflicts, publish your additions and modifications often
- To prevent loss of data before you publish or refresh, save your workbook periodically
The Azure DevOps Server Excel plugin uses the Azure DevOps Server REST APIs, which are wrapped into an SDK. This allows for safe and secure bulk editing of work items. The plugin supports two-way updates, and changes that are made to work items in Azure DevOps Server web portal can be refreshed back into Excel by clicking the Refresh button. Refreshing the data does not overwrite any calculations or formatting that you may have applied to the worksheet. If you spend a lot of time using Microsoft Project, you'll be excited to know that the Azure DevOps Server plugin can also be used from Microsoft Project.
The marketplace features the Azure DevOps Open in Excel extension (https://marketplace.visualstudio.com/items?itemName=blueprint.vsts-open-work-items-in-excel). This is a free extension that was created by Microsoft DevLabs, and adds the option of opening work items in Excel from various access points, such as work item queries, backlogs, and selective work items:
Another noticeable extension in the marketplace is the Requirements Integrator (https://marketplace.visualstudio.com/items?itemName=jgarverick.RequirementsIntegrator). This is an open source extension that was created by Microsoft MVP Josh Garverick, which introduces the capability of mapping external requirements into Azure DevOps Server to create a traceability matrix with work items. This extension introduces a new sub-tab called Requirements in the work hub, which allows you to import external requirements into TFS using a predefined Excel template:
The extension allows you to do the following:
- Manage requirements to work item mapping
- Display a sprint view that shows the requirements covered by a sprint
- Display a traceability matrix, including gaps, for requirements that are imported and mapped to WIs
- Restrict import usage to non-CMMI process templates
- Requirement visualization (visual traceability)
- Export requirement information to Excel
I encourage you to look at the marketplace (https://marketplace.visualstudio.com/) as it has a range of extensions that enhance the experience of planning, tracking, and managing work items. While this extension isn't necessarily a replacement for the Excel add-in, you'll find that it enhances the work planning, tracking, and management experience.