How to Automate Microsoft 365 Licensed User Reports in Excel
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.

- 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 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.
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.
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'.
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.
Open Excel, go to the 'Data' tab, select 'Get Data', then click 'From Other Sources' followed by 'From OData Feed'.
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.

Export License Reports Automatically with PowerShell
Use a PowerShell script to automatically pull Office 365 license data and output it as a CSV file that Excel can read.
Utilize Third-Party Reporting Tools
Use pre-built scripts or third-party administration tools to handle the data extraction for Excel without coding.
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.

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.




