Data Import & Export

How to Export Microsoft 365 Users and Shared Mailboxes to Excel

Kushani Nimanthika Oct 1, 2026 869 views

Question details

The user needs to combine licensed Microsoft 365 users and shared mailboxes into a single PowerShell export and save the results into one Excel workbook.

How to Export Microsoft 365 Users and Shared Mailboxes to Excel

Product: Microsoft 365 / Excel
Device & OS: not provided
Scenario: An administrator is attempting to run a PowerShell script to audit Microsoft 365 accounts, aiming to consolidate different account types into one reporting file.
Observed behavior: To successfully generate a single Excel workbook that contains dedicated, separate worksheets for licensed users and shared mailboxes.

Before you start

Ensure you have PowerShell running as an Administrator with the necessary modules installed (AzureAD, ExchangeOnlineManagement, and ImportExcel), and that you hold the required administrative privileges in your Microsoft 365 tenant.

Solution 1Recommended

Export Data to Separate Worksheets Using PowerShell

Because users and shared mailboxes are distinct object types with different properties, they must be exported to separate worksheets within the same Excel workbook to maintain data integrity.

By utilizing the ImportExcel module, administrators can write data directly to an .xlsx file without needing Microsoft Excel installed on the machine.

The process involves connecting to the respective Microsoft 365 services, pulling the data into variables, and piping those variables to the Export-Excel command while specifying distinct Worksheet names.

Connect to Microsoft 365 Services

Open PowerShell as an Administrator and authenticate to Azure AD, MSOnline, and Exchange Online using the appropriate connection cmdlets (e.g., Connect-ExchangeOnline and Connect-AzureAD).

Query Licensed Users

Run the appropriate Get-MsolUser or Get-AzureADUser command, filtering for users with active licenses, and store the output in a PowerShell variable (e.g., $licensedUsers).

Query Shared Mailboxes

Use the Get-Mailbox command with the -RecipientTypeDetails SharedMailbox parameter to retrieve all shared mailboxes, storing this data in another variable (e.g., $sharedMailboxes).

Export Users to Excel

Pipe the user variable to the Export-Excel cmdlet, specifying your desired file path (e.g., 'C:\Reports\M365Report.xlsx') and adding the parameter -WorksheetName 'Users'.

Append Mailboxes to the Same File

Pipe the shared mailbox variable to the Export-Excel cmdlet using the exact same file path. Include the parameters -WorksheetName 'Mailboxes' and -Append so it adds a new sheet rather than overwriting the file.

Export Data to Separate Worksheets Using PowerShell

Required PowerShell Module: The Export-Excel command requires the ImportExcel module. You can install it from the PowerShell Gallery by running: Install-Module -Name ImportExcel.

Free Microsoft Office alternative

View and Analyze Your Exported Microsoft 365 Data with WPS Office

While extracting data from Microsoft 365 requires specific administrative tools, you don't need a pricey Microsoft Office subscription to view, format, and analyze the resulting reports. WPS Office is a powerful, free alternative that perfectly handles Excel (.xlsx) files generated by PowerShell exports.

Fully compatible with Microsoft Excel (.xlsx) formats generated by PowerShell scripts.Free to use, lightweight, and consumes minimal system resources compared to heavy alternatives.Features a familiar user interface, meaning zero learning curve for Excel users.Includes robust sorting, filtering, and PivotTable tools to easily analyze your M365 user and mailbox data.

microsoft office alternative - wps office

Frequently Asked Questions

Why shouldn't I export users and shared mailboxes into the exact same worksheet?

Because licensed users and shared mailboxes are completely different object types in Microsoft 365. They possess different attributes (columns), and combining them in a single worksheet would result in misaligned, hard-to-read data. Separating them into different tabs keeps your report structured.

Do I need Microsoft Excel installed on my server to run the Export-Excel command?

No. The Export-Excel cmdlet from the ImportExcel module utilizes a standalone library to construct .xlsx files directly. You can safely generate these administrative reports on servers without any Office desktop applications installed.

How can I easily analyze the data after it is exported?

Once the PowerShell script completes and your .xlsx file is generated, you can open it in WPS Spreadsheet or Microsoft Excel. From there, you can format the data as a table, apply advanced column filters, or utilize PivotTables to audit licenses and shared mailbox allocations efficiently.

Excel & Data Problems