License & Subscription Issues

How to Automate Microsoft 365 Licensed User Reports in Excel

Chanuka GeekiyanageChanuka Geekiyanage Oct 28, 2026 869 views

Question details

The user wants to automate the process of refreshing a Microsoft 365 licensed-user report in Excel to avoid manually exporting and copying data from the admin center.

How to Automate Microsoft 365 Licensed User Reports in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Automating the retrieval and scheduled refreshing of Microsoft 365 user license data directly into an Excel spreadsheet.
Observed behavior
Using Excel's 'Data > From Web' feature fails because the built-in web view does not support signing into the Microsoft 365 admin center, requiring users to rely on APIs or scripts.
Before you start

Before attempting to automate your reports, ensure you have Microsoft 365 Global Admin or Report Reader permissions, as these are mandatory for accessing user license data via APIs or PowerShell.

Solution 1Recommended

Automate User Reports using Microsoft Graph API

Extract licensed-user data automatically by querying the Microsoft Graph API, bypassing manual web login limitations.

Since Excel's native 'From Web' connector cannot authenticate modern admin center logins, you must use Microsoft Graph. This method creates a direct, secure data feed that Excel can refresh at any time.

1
Register an Application in Azure AD

Log into the Azure portal, navigate to Azure Active Directory > App registrations, and register a new application. Grant it the necessary Microsoft Graph API permissions, such as 'Reports.Read.All' or 'User.Read.All'.

2
Generate Client Credentials

In your newly registered app, go to 'Certificates & secrets' and generate a new client secret. Note down this secret and your Application (client) ID to authenticate API requests.

3
Connect Excel to the API

Open Excel, go to the 'Data' tab, select 'Get Data', then click 'From Other Sources' followed by 'From OData Feed'.

4
Input the Endpoint URL

Enter the Microsoft Graph API endpoint URL for licensed users. When prompted for credentials, use the Basic authentication method with your App ID and Client Secret to load the data.

Automate User Reports using Microsoft Graph API
Developer Knowledge Required: Using Graph APIs requires initial setup in the Azure portal. If you are unfamiliar with APIs, consider asking your IT administrator for assistance.
Free Microsoft Office alternative

Edit and Analyze Your Exported Reports in WPS Office

While extracting backend data from the Microsoft 365 Admin Center requires specific administration tools or scripts, you don't need expensive software to analyze the final results. WPS Office is a free, lightweight suite that seamlessly opens your exported CSV and Excel reports.

Fully compatible with Microsoft Excel (.xlsx) and CSV formats.Advanced data filtering, pivot tables, and charting tools for report analysis.Lightweight design ensures fast loading times even with massive user data sets.Familiar user interface requires no steep learning curve.
QA img-9

Frequently Asked Questions

Why doesn't the 'Data > From Web' feature work for the Microsoft 365 Admin Center?

The built-in Web View in Excel's 'Get Data' feature does not fully support the modern authentication, multi-factor authentication, and conditional access policies required to log into the Microsoft 365 Admin Center, preventing successful sign-ins.

Can I schedule automatic data refreshes within my Excel workbook?

Yes. Once you connect Excel to a supported data connection like an OData feed (Microsoft Graph API) or a local CSV file generated by PowerShell, you can navigate to 'Data' > 'Queries & Connections' > 'Properties' and set the data to refresh automatically when opening the file or at specific time intervals.

Is there a built-in automated license report link in Microsoft 365?

Microsoft 365 provides usage and license reports in the Admin Center, but there is no native feature to stream these live directly into an Excel workbook. Automation requires bypassing the admin center UI using APIs, PowerShell scripts, or third-party extraction tools.

Do I need coding skills to automate Microsoft 365 reports?

If you use specialized third-party software, no coding is required. However, utilizing the highly recommended Microsoft Graph API or PowerShell methods requires a basic understanding of scripting, API configuration, and Azure Active Directory setup.