Excel to Azure Setup and Tutorials
Step 1: Set up your Azure/Excel VSTS integration
Step 2: Create an Azure Query with the following Fields:
- Work Item Type = [Any]
- State = [Any]
- Completed Work > 0
- Iteration Path = (Whatever the current sprint is)
Step 3: Create an Excel file, with the Azure VSTS Integration, that is utilizing this Query
Step 4: Create a Pivot Table from the Table created by the Azure/VSTS Query Table on a separate worksheet from where the worksheet that is displaying the Query
Step 5: Select the following fields in the Pivot Table
- Assigned To:
- Area Path
- Remaining Work
- Completed Work
Step 6: Organize the fields in the Pivot Table in the order below:
- Filters: (Blank)
- Columns: Values
- Rows: Area Path, Assigned To
- Values: Sum of Completed Work, Sum of Remaining Work
Step 7: Refresh the Azure table, then refresh the Pivot Table
Terms and Conditions:
- Developers are often slow to update their tickets, the biggest call for updating tickets is roughly 7 pm CT. This is called the "Sprint Snapshot."
- Ticket migration happens around 8 pm CT, when this happens, some completed work can be removed/moved to the next sprint, it's crucial that the "Sprint Snapshot" is communicated to the development team, and that the PM team does not move tickets until the snapshot happens.
- If the Query/Pivot Table was updated at 7 pm and is not refreshed, that data will be static until they are updated.