How to Export SharePoint Site Users to an Excel Spreadsheet

Worth a direct correction before anything else: neither the classic on-premises COM-automation script nor the “PnP PowerShell” example commonly shown for this actually produces a real Excel file without a caveat — the COM approach genuinely needs Excel installed on the machine running it, and the PnP example usually exports to CSV, not a true `.xlsx`, despite the title of most versions of this guide promising Excel specifically. This post covers the real gap: a genuinely current way to write an actual `.xlsx` file with no Excel installation required, plus the connection and platform corrections both older approaches need.

In this post: Real .xlsx, with no Excel installed · Connecting for SharePoint Online · The COM approach, and its real constraints · Expanding group membership properly · Separating internal users from guests · When would you actually use this? · Related reading

Real .xlsx, with no Excel installed

The `ImportExcel` PowerShell module writes genuine `.xlsx` files directly — no COM automation, no Excel installation, and it works on Windows, Linux, and macOS alike, including in unattended contexts like a scheduled task or a CI pipeline where COM automation is notoriously unreliable:

Install-Module ImportExcel -Scope CurrentUser -Force

Connect-PnPOnline -Url "https://yourtenant.sharepoint.com/sites/yoursite" -Interactive

Get-PnPUser | Select-Object DisplayName, Email, LoginName |
    Export-Excel -Path "C:\SiteUsers.xlsx" -WorksheetName "Site Users" -AutoSize -BoldTopRow

`-AutoSize` and `-BoldTopRow` are genuinely cosmetic touches `Export-Csv` has no equivalent for at all — worth using them, since the whole point of exporting to Excel specifically (rather than CSV) is usually that someone’s going to open and read the file directly, not just import it into another tool.

Connecting for SharePoint Online

`-Interactive` replaces `-UseWebLogin` above — the latter has been removed from `Connect-PnPOnline` entirely, not merely deprecated, so a script still built around it fails to connect at all rather than showing a warning, with no partial functionality to fall back on. `Get-PnPUser` returns the site’s users directly; worth knowing it returns the flat list of individually-added users and groups, not automatically expanded group membership — covered separately below, since it’s a real, common gap in a “who has access” export.

Export-Excel writes real .xlsx files with no Excel installation required — a genuinely better fit than COM automation for anything unattended, and a genuinely better fit than CSV for anything meant to actually be read as a spreadsheet.

The COM approach, and its real constraints

The classic on-premises pattern — `New-Object -ComObject Excel.Application`, driving Excel programmatically — still works, but worth knowing its real, non-obvious constraints before relying on it: it genuinely requires a licensed copy of Excel installed on the exact machine running the script, it’s Windows-only (COM automation doesn’t exist on PowerShell 7 running cross-platform), and it’s a poor fit for anything unattended — Excel’s COM interface was built for interactive use, and running it from a scheduled task or service account context is a well-known source of silent hangs, since Excel occasionally throws modal dialogs (a corrupt-file warning, a permissions prompt) that block forever with nobody there to click them. `ImportExcel` above avoids all three constraints at once, which is the real reason it’s worth defaulting to now rather than treating COM automation as the standard approach.

Expanding group membership properly

A flat list of site users misses the real “who has access” picture if any of those users are actually SharePoint groups rather than individuals — expanding group membership is worth doing explicitly:

$results = foreach ($group in Get-PnPGroup) {
    Get-PnPGroupMember -Identity $group | Select-Object @{N="Group";E={$group.Title}}, DisplayName, Email, LoginName
}
$results | Export-Excel -Path "C:\SiteUsersExpanded.xlsx" -WorksheetName "Group Membership" -AutoSize -BoldTopRow

Worth exporting both the flat site-user list and this group-expanded version as separate sheets in the same workbook — `Export-Excel`’s `-WorksheetName` parameter, run twice against the same `-Path`, appends a second sheet rather than overwriting the first, giving an auditor both views in one file rather than two separate exports to reconcile by hand.

Separating internal users from guests

Worth filtering the export rather than dumping every entry `Get-PnPUser` returns — the raw list includes system accounts and, depending on the tenant’s guest-sharing configuration, external users mixed in alongside internal ones with no visual distinction unless the export specifically flags it:

Get-PnPUser | Where-Object { $_.PrincipalType -eq "User" -and $_.LoginName -notlike "*#ext#*" } |
    Select-Object DisplayName, Email, LoginName |
    Export-Excel -Path "C:\SiteUsersInternalOnly.xlsx" -WorksheetName "Internal Users" -AutoSize -BoldTopRow

`#ext#` in a login name is the real, reliable marker for a guest account added via external sharing — worth checking for it explicitly on any audit export where distinguishing internal staff from external guests is actually part of what’s being reviewed, rather than assuming the distinction is obvious from the display name alone. A guest account’s display name often looks identical in format to an internal employee’s, which is exactly why relying on it as the distinguishing signal is unreliable in practice.

When would you actually use this?

A periodic access audit or compliance review — the group-expanded export above is the version that actually answers “who can see this content,” not just “who’s listed on the site.”
Troubleshooting a specific user’s access complaint — a targeted `Get-PnPUser` lookup rather than a full site export is usually faster for a single-person question.
The export needs to run unattended on a schedule — `ImportExcel`, not COM automation, given the reliability problems covered above.
The destination system just needs the raw data, not a formatted spreadsheet — plain CSV is still simpler and lighter-weight than generating an actual `.xlsx` for something that’s immediately going to be re-imported elsewhere anyway.

SharePoint Permissions: A Comprehensive Guide — the broader access-control picture this export feeds into.

The underlying task hasn’t changed — pull the users, write them out for review. What’s genuinely worth updating is the export mechanism itself: a real `.xlsx` file that doesn’t depend on Excel being installed handles more scenarios than either the COM route or a CSV that only calls itself Excel by association, and it’s worth adopting as the default going forward rather than something reached for only once the older approach has already caused a problem.