Connecting Azure DevOps to Excel

One of the tools I use often on projects is the DevOps integration with Excel in order to gain a better visual of To Do, In Progress and Done Tasks associated with each Product Backlog Item. The Azure Integration Tool for Office 2019 allows you to link your Excel Spreadsheet with your Azure DevOps Queries.

How do you set this up? Download Azure DevOps Office Integration 2019 from here: https://visualstudio.microsoft.com/downloads/

Once downloaded, install the solution by running the .exe file:

Once installed you should have the following screen appear below:

Make sure to close any open instances of Excel you may have open and restart Microsoft Excel. Once Excel is opened you should now see the Team tab in your header Ribbon in Excel:

Under the Team tab, click on New List. (Hint: always keep your selected cell on the first cell(A1) as any new list you create will use the selected cell as the starting point for your list)

If you have already connected to an Azure DevOps Organization before, you will be able to see a list of Azure DevOps servers under Team Project Collections.

Click on Servers.. then click on Add… and enter the URL for the Azure DevOps Organization where TheOrganisationName is the name of the Organisation where your Azure DevOps project lives.

The format should look like this: https://dev.azure.com/TheOrganisationName/

Once you click OK you will then be prompted to log in to your Microsoft account that has access to the DevOps project under the Organisation.

Your Server will then be viewable from the DevOps server list as per the below. Click Close

A new window will pop up displaying YourOrganisationName under Team Project Collections sub window and the projects that you have permissions to view under the Team Projects window. Select the Team Project and click Connect.

A new window will then appear prompting you to Select a Query. Click the drop-down menu and select one of your own queries created on DevOps or click on a shared query

You should now see your below data from your query pulled directly from DevOps??

In another post I will show you some more cool things you can do with Excel and DevOps, like publishing data from Excel to DevOps to update tasks, creating custom queries on DevOps and much more!