How to Export Azure DevOps Data to Excel

Exporting data from Azure DevOps to Excel empowers engineering teams to create custom reports, perform deeper analysis, and share insights with stakeholders in familiar formats. In this guide, we’ll explain:

• Why you might export Azure DevOps work items and reports to Excel

• Step-by-step methods for exporting work items, boards, and reports

• How to use Excel for advanced analytics

• Common errors and their solutions

By the end, you’ll know the most efficient ways to get Azure DevOps data into Excel, and how to turn it into actionable insights.

Why Export Azure DevOps Data to Excel?

While Azure DevOps includes dashboards and analytics, many teams turn to Excel for greater flexibility in reporting. Here’s why:

1. Streamlining Reporting & Analysis

Manually compiling status reports from Azure DevOps can be tedious and time-consuming. Exporting data to Excel automates this, letting you refresh work item data with one click and avoid repetitive manual updates. It saves time and effort for engineering managers who need regular progress reports.

2. Custom Charts and Pivot Analysis

Excel enables you to create custom charts (e.g. burndown trends, defect age histograms) and perform pivot-table analysis on work items. In fact, one of the quickest ways to generate a custom work-tracking report is to export a flat list query to Excel – you can then build status or trend charts and slice the data with PivotTables (you can refer them below). This level of customization is often beyond what Azure DevOps’s default analytics provide.

Figure 1: Azure DevOps Sprint Progress Chart

3. Combining Data Sources

Excel serves as a common platform to merge Azure DevOps data with other datasets. For example, you could:

• Combine work item data with cost data

• Correlate code quality metrics (from a tool like CodeAnt.ai) with Azure Boards 

• Build KPI dashboards that blend technical and business performance.

Excel’s flexibility allows engineering directors to integrate multiple sources and perform custom calculations for KPIs relevant to the business.

4. Sharing with Non-Technical Stakeholders

Not all stakeholders have access to or familiarity with Azure DevOps. Exporting to Excel allows you to package project data (e.g. a list of open issues or a sprint plan) into a format that can be easily emailed or presented. Excel spreadsheets can be viewed by anyone and are often the “lingua franca” for business reports, ensuring broader accessibility of the information.

In short, exporting to Excel gives engineering teams greater control over their Azure DevOps data, enabling ad-hoc analysis, custom reporting, and easier distribution of information. 

Next, we’ll cover how to actually perform these exports for work items and other Azure DevOps data.

How to Export Work Items to Excel

Azure DevOps allows exporting any work item query to a CSV file (as shown above), which you can open in Excel for further analysis.

The method you choose depends on whether you need a quick one-time export or a dynamic link that can refresh and even sync changes back to Azure DevOps.

1. Export Query Results to CSV (Static Export)

The simplest approach is exporting a work item query as a CSV file:

1. In the Azure DevOps web portal, go to Boards > Queries.

2. Run or define a query (adding columns/filters as needed).

3. Open the […] menu and select Export to CSV.

This downloads a CSV containing your query results (up to 20,000 items) that you can open in Excel. Once there, you can sort, filter, or analyze the snapshot. This method works well for quick reports or when sharing data with others, but keep in mind, it’s static. Any updates in Azure DevOps won’t reflect unless you re-export.

2. Use the Excel Integration (Azure DevOps Office Add-in)

For more flexibility, Microsoft provides an Office integration plugin for Excel:

1. Install the add-in: Download Azure DevOps Office Integration 2019 (aka Team Foundation Office Integration). After installation, Excel will show a new Team tab on the ribbon.

2. Connect to Azure DevOps: In the Team ribbon, click New List, enter your organization’s URL/project, and authenticate. You can import results from a saved query (e.g., Active Bugs, Sprint Backlog) or create a new input list.

3. View & Edit Work Items: Work items appear as rows in Excel, with support for parent/child hierarchies. This is especially useful for Epic → Feature → Story relationships. You can refresh anytime to pull updated data, or even bulk edit tasks in Excel and publish changes back into Azure DevOps.

Tip: The Excel integration is ideal if you:

• Need to preserve hierarchy in exports.

• Frequently refresh or update work item data.

• Want to perform bulk edits (e.g., closing tasks or reassigning bugs).

Note on Query Limits: Azure DevOps imposes a limit of 20,000 work items on query results for export. If your query returns more than 20k items (which can happen on large projects), the export will fail or truncate. In such cases, refine your query with additional filters (for example, export work items by smaller date ranges or specific areas) to get under the limit. Also be mindful that extremely large exports (thousands of rows) might be slow to open or manipulate in Excel.

3. Alternate Method: Marketplace Extension

If you prefer a one-click approach, install the free Azure DevOps Open in Excel extension from the Marketplace. It adds an Open in Excel button directly to the query toolbar. With the Office plugin installed, clicking it launches Excel pre-connected to your query results.

With either method, once your work item data is in Excel, you can leverage Excel’s capabilities (charts, formulas, pivot tables) to analyze progress and trends. Next, we’ll look at exporting other Azure DevOps data like boards and reports.

How to Export Reports & Boards to Excel

Beyond individual work item lists, many teams want to export board data (Kanban or sprint taskboards) or analytics reports (burndown, velocity, cumulative flow, etc.) for deeper analysis in Excel. While Azure DevOps doesn’t offer a direct “Export Board to Excel” button, there are reliable ways to access the underlying data.

Export Board Work Items via Query

Boards in Azure DevOps (Kanban or sprint boards) are essentially filtered views of work items. For example:

• A sprint taskboard = all active items in Sprint X

• A Kanban column = user stories in “New” and “Active” states

To export this data:

1. Create a query that mirrors the board’s filters (e.g., Iteration Path = Project\Sprint X and exclude “Removed/Done”).

2. Export the query results to CSV or Excel (as explained in the previous section).

This produces a flat list of board items with fields like Assigned To, State, Work Item Type, etc. While Excel won’t replicate the visual layout (columns, swimlanes), you’ll still get all the data and can regroup it with PivotTables or filters.

Exporting Analytics Reports

Azure DevOps includes built-in analytics widgets such as velocity charts, cumulative flow diagrams (CFD), and burndown reports. Export options vary by widget:

• Manual Export or Copy/Paste: Some report lists (e.g., test case results) include an Export to Excel button. Others may allow copying table data directly. However, many charts (velocity, CFD) don’t have a direct export option.

• Leverage OData Analytics (Advanced): The most flexible approach is using the Azure DevOps Analytics OData feed. This allows you to pull raw trend data, such as daily counts, sprint velocity, or work item history, directly into Excel. With OData queries, you can recreate reports like:

  • Velocity: Story points completed per sprint

  • Cumulative Flow: Items in each state by day

  • Burndown: Remaining work over time

This requires some familiarity with query syntax but unlocks custom reports beyond what Azure DevOps provides out of the box.

Figure 2: How Azure DevOps board/report data flows into Excel for custom reporting and analysis

How to Use Excel for Advanced Analysis

If exporting queries and reports isn’t enough, Excel can also act as a live reporting tool by connecting directly to Azure DevOps Analytics via the OData feed. This method is ideal for advanced analysis across large datasets or historical trends, since you can refresh data without re-exporting CSV files.

Step 1: Understand the OData Feed

Azure DevOps Services provides an OData Analytics API endpoint that exposes entities like WorkItems, TestResults, Pipelines, Projects, and WorkItemSnapshots (daily history). With this, you can query both current and historical data.

Make sure Analytics is enabled for your Azure DevOps organization (it’s on by default in Azure DevOps Services).

Step 2: Connect Excel to the Feed

1. In Excel (2016 or later), go to Data > Get Data > From Other Sources > From OData Feed.

In older versions (2010/2013), install the Power Query add-in first.

2. Enter the OData URL in this format:

https://analytics.dev.azure.com/{OrganizationName}/{ProjectName}/_odata/{version}/

If your organization is Fabrikam and you want the v2.0 Analytics API, the URL would be https://analytics.dev.azure.com/Fabrikam/_odata/v2.0/. 

3. You can include {ProjectName} to scope to a single project.

Authenticate using Microsoft Entra ID (Azure AD) if available. Alternatively, generate a Personal Access Token (PAT) with Analytics read permissions.

Step 3: Select & Filter Data

• Excel will display available entities in the Navigator (WorkItems, WorkItemSnapshots, TestRuns, etc.).

• Use Transform Data (Power Query Editor) before loading to filter down records (e.g., only Active work items, or a 6-month window).

• This prevents massive datasets (like millions of snapshot rows) from overloading Excel.

Step 4: Analyze & Refresh

Once imported, your data lives inside Excel tables or the Data Model. From here you can:

• Build PivotTables to track progress by State, Assigned To, or Iteration.

• Create charts for weekly throughput, cycle times, or deployment frequency.

• Join DevOps data with other datasets (e.g., financial KPIs, security scans, or CodeAnt.ai quality metrics).

Because the connection is live, clicking Refresh All re-queries Azure DevOps, keeping reports up to date without re-exporting CSVs.

Example Use Cases

• Lead Time Trends: Pull CreatedDate and ClosedDate fields, calculate cycle time in Excel, and chart trends over months.

• Throughput vs Deployments: Join pipeline run data with work item data to analyze if higher deployment frequency correlates with backlog throughput.

• Custom Governance Metrics: Use OData + Power Query to enforce KPIs your org cares about (e.g., defect age, reopen rate, time in “Blocked” state).

Permissions & Requirements

• You’ll need at least Basic access in Azure DevOps.

• Analytics permissions are usually enabled for project members by default.

• If you run into issues, check your account settings or create a PAT with Analytics (Read) scope.

Using the OData feed turns Excel into a custom Azure DevOps reporting engine, letting you build trend analysis, cross-dataset joins, and live dashboards far beyond the default Analytics widgets.

Next, we’ll cover common errors you might hit while exporting or connecting to Excel, and how to fix them quickly.

Common Export Errors & Fixes

Exporting Azure DevOps data to Excel is powerful, but not always seamless. Here are common issues teams encounter and how to resolve them:

1. Excel Add-in Missing or Disabled

• Symptom: Team ribbon doesn’t appear in Excel.

• Fix: Go to File > Options > Add-ins > COM Add-ins and enable Team Foundation Add-in. If it’s missing, reinstall the Azure DevOps Office Integration 2019. In rare cases, set LoadBehavior=3 in the registry to force-load the add-in.

2. Authentication Errors (TF30063, TF31003)

• Symptom: “Not authorized” or “Unable to connect.”

• Fix:

  • Sign out and back in using the correct Microsoft/Azure AD credentials.

  • If needed, use a Personal Access Token (PAT) with work item read/write scope.

  • Ensure Excel is updated for MFA prompts.

  • Verify you have at least Stakeholder access (read) or Basic access (write).

3. OData Feed Connection Fails

• Symptom: “Excel cannot connect to OData feed.”

• Fix:

  • Select Azure AD authentication (not Windows).

  • If blocked, use PAT with Basic auth.

  • Check firewall rules – ensure analytics.dev.azure.com is reachable.

4. CSV Export Formatting Issues

• Line breaks/HTML tags: Fields like Descriptions may show <p> tags or  . Use Find/Replace or rely on Excel integration for cleaner exports.

• Auto-formatting errors: Large IDs may appear in scientific notation. Pre-format columns as Text or import via Data > From Text/CSV to control data types.

• Unsupported work item types: Some types (e.g., Test Cases) don’t export via CSV, use the Excel add-in or REST API instead.

5. Hierarchy Not Preserved

• Symptom: CSV exports flatten parent/child links.

• Fix: Use a Tree of Work Items query + Excel add-in. Direct Links queries import flat; for true hierarchy, stick to Tree queries.

6. Large Data or Performance Problems

• Symptom: Queries capped at 20,000 items; very large exports can hang Excel.

• Fix:

  • Filter queries (date ranges, states).

  • Remove unnecessary columns.

  • Use OData with filters instead of pulling entire datasets.

  • Prefer 64-bit Excel for large data.

7. Publish/Update Conflicts in Excel

• Symptom: Work item changed in Azure DevOps after you loaded it.

• Fix: Refresh data before publishing. Correct rule violations (e.g., invalid states, read-only fields). For Assigned To fields, ensure exact match or use Name <email>.

8. macOS Compatibility Issues

• Symptom: The Azure DevOps Excel add-in is Windows-only.

• Fix:

  • Use CSV export.

  • Run Excel in a Windows VM.

  • Use Power BI for richer reporting on Mac.

Microsoft considers the Excel add-in “feature complete” (no further updates). For long-term reliability, prefer CSV exports or OData connections over the Office plugin.

Conclusion

Exporting Azure DevOps data to Excel unlocks flexibility and deeper insights. With the right method, you can:

• Generate quick CSV snapshots for one-off reporting.

• Leverage the Excel add-in for live, refreshable queries.

• Use the Analytics OData feed for advanced, large-scale reporting.

These approaches turn raw work item and pipeline data into meaningful insights that engineering managers, product owners, and DevOps leads can use to:

• Track velocity and delivery trends.

• Spot bottlenecks early.

• Communicate progress in the universal language of Excel.

However, building and maintaining these reports manually can be time-consuming. This is where AI-driven platforms like CodeAnt AI go further:

• Continuous analytics: Automatically surfaces metrics like code quality, PR review times, coverage trends, and DORA metrics.

• Seamless integration: Works directly with Azure DevOps (and other VCSs).

• Real-time feedback: Developers get instant AI-powered code reviews in pull requests.

• Manager-ready dashboards: Leaders gain a 360° view of engineering performance without wrestling with spreadsheets.

In short:

• Excel = visibility

• CodeAnt.ai = continuous intelligence

Ready to move beyond manual exports? Try CodeAnt AI to automate engineering analytics, accelerate code reviews, and improve software delivery with AI-powered insights. Visit our website to learn more and see how it complements your Excel reporting.

FAQs

Q1. Can I automatically refresh Excel with the latest Azure DevOps data?

Yes. If you use the Excel add-in or connect via the OData feed, you can refresh data in place. For example, in the Team ribbon of Excel, click Refresh to pull updated work items. With OData + Power Query, Excel’s Refresh All will re-query Azure DevOps Analytics. CSV exports are static snapshots, so you’d need to re-export them manually.

Q2. How can I export a hierarchical list of work items (epics → features → stories)?

A CSV export always flattens the list. To preserve hierarchy:

• Excel add-in: Use a Tree of Work Items query and load it in Excel. You can add tree levels (parent/child) to display nested relationships.

• REST API/PowerShell: For advanced users, the Azure DevOps REST API can return parent/child links, which you can structure in Excel.

The add-in is the easiest method for most teams.

Q3. Can I export test plans or test results to Excel?

Yes. Options include:

• Direct Export: In the Test Plans hub, many views (Runs, Results) have an Export to Excel/CSV option.

• Work Item Queries: Test cases can be exported like any other work item type.

• Analytics OData feed: Use entities like TestRuns or TestPoints for detailed reporting in Excel or Power BI.

Keep in mind that importing test cases back from CSV isn’t supported, but export works reliably.

Q4. How can I export the pipeline or build data to Excel?

Pipeline/build data isn’t in Boards queries, but you can access it via:

• Analytics OData feed (entities like PipelineRuns or Builds include status, result, timestamps).

• REST API + scripts (PowerShell, Azure CLI) to dump pipeline run data to CSV.

• Quick Copy: In the Pipelines UI, you can copy/paste build history tables into Excel.

For recurring needs, OData or Power BI templates are recommended.

Q5. Are there security concerns when exporting Azure DevOps data to Excel?

Yes, treat exported files as sensitive:

• Data privacy: Work items often include confidential details. Secure or encrypt files when sharing.

• Permissions: Exports respect Azure DevOps permissions—users can only export what they’re allowed to view.

• Credentials: Use secure methods (Azure AD or PATs) for OData/Excel integration; avoid deprecated alternate credentials.

• Compliance: Follow your org’s retention and governance policies (e.g., GDPR).

Handled correctly, exports are safe and extremely useful for reporting.