PowerShell Export Active Directory to Excel Automatically
─□✕

PowerShell Export Active Directory to Excel Automatically

PowerShell Tips Editor 3 min read
PowerShell Export Active Directory to Excel Automatically

Management asks for an Active Directory report and the answer is always “we’ll get that to you by end of week.” With PowerShell and the ImportExcel module, you can PowerShell export Active Directory to Excel as a professionally formatted multi-sheet workbook automatically — generated in minutes, not days, and scheduled to run without human intervention.

Quick Answer / TL;DR

Install the ImportExcel module, query AD with Get-ADUser and related cmdlets, then pipe to Export-Excel with -WorksheetName, -TableStyle, and -AutoSize for a polished workbook.

Collect AD Users, Groups, and Computers

Gather all three AD object types at the start of the script. Request all the properties you need in a single query with -Properties to avoid repeated AD calls later. Filter and shape each dataset before exporting.

# Collect users with required properties
$users = Get-ADUser -Filter * -Properties DisplayName, Department, Title,
    EmailAddress, Enabled, LastLogonDate, PasswordLastSet, PasswordNeverExpires |
    Select-Object DisplayName, SamAccountName, Department, Title,
        EmailAddress, Enabled, LastLogonDate, PasswordLastSet, PasswordNeverExpires

# Collect security groups
$groups = Get-ADGroup -Filter * -Properties Description, Members |
    Select-Object Name, GroupScope, GroupCategory,
        Description,
        @{N='MemberCount'; E={($_.Members).Count}}

# Collect computers
$computers = Get-ADComputer -Filter * -Properties OperatingSystem, LastLogonDate |
    Select-Object Name, OperatingSystem, Enabled, LastLogonDate

Write-Host "Collected: $($users.Count) users, $($groups.Count) groups, $($computers.Count) computers"

Install and Use ImportExcel

The ImportExcel module by Doug Finke generates Excel files without Excel being installed. It uses the EPPlus library under the hood. Install from the PowerShell Gallery and import before use.

# Install ImportExcel module
Install-Module ImportExcel -Scope CurrentUser -Force

# Verify installation
Get-Module ImportExcel -ListAvailable | Select-Object Name, Version

# Import for use in script
Import-Module ImportExcel

Create Multi-Sheet Workbook

Export each dataset to a separate worksheet in the same workbook file. Use the same -Path value for all Export-Excel calls and specify different -WorksheetName values. The -ClearSheet flag ensures previous data is removed when regenerating the report.

# Export to a multi-sheet workbook
$reportPath = "C:\Reports\AD_Report_$(Get-Date -Format 'yyyyMMdd').xlsx"

$users     | Export-Excel -Path $reportPath -WorksheetName 'Users'     -ClearSheet -AutoSize
$groups    | Export-Excel -Path $reportPath -WorksheetName 'Groups'    -ClearSheet -AutoSize
$computers | Export-Excel -Path $reportPath -WorksheetName 'Computers' -ClearSheet -AutoSize

Write-Host "Report saved: $reportPath"

Apply Table Formatting

Export-Excel supports Excel Table styles that add banded rows, filter dropdowns, and automatic styling. The -TableName and -TableStyle parameters enable this. Use one of the built-in Excel table styles: Light1–Light21, Medium1–Medium28, or Dark1–Dark11.

# Apply table formatting with blue-themed style
$users | Export-Excel -Path $reportPath `
    -WorksheetName 'Users' `
    -TableName 'ADUsers' `
    -TableStyle Medium2 `
    -AutoSize `
    -FreezeTopRow `
    -BoldTopRow `
    -ClearSheet

$computers | Export-Excel -Path $reportPath `
    -WorksheetName 'Computers' `
    -TableName 'ADComputers' `
    -TableStyle Medium6 `
    -AutoSize `
    -FreezeTopRow `
    -ClearSheet

Add Summary Statistics

A summary sheet gives management a quick overview without scrolling through thousands of rows. Use -WorksheetName 'Summary' with custom statistics objects.

# Build summary statistics
$summary = @(
    [PSCustomObject]@{ Category = 'Total Users';         Count = $users.Count }
    [PSCustomObject]@{ Category = 'Enabled Users';       Count = ($users | Where-Object Enabled -eq $true).Count }
    [PSCustomObject]@{ Category = 'Disabled Users';      Count = ($users | Where-Object Enabled -eq $false).Count }
    [PSCustomObject]@{ Category = 'Password Never Expires'; Count = ($users | Where-Object PasswordNeverExpires -eq $true).Count }
    [PSCustomObject]@{ Category = 'Total Groups';        Count = $groups.Count }
    [PSCustomObject]@{ Category = 'Total Computers';     Count = $computers.Count }
    [PSCustomObject]@{ Category = 'Report Generated';    Count = (Get-Date -Format 'yyyy-MM-dd HH:mm') }
)

$summary | Export-Excel -Path $reportPath `
    -WorksheetName 'Summary' `
    -TableStyle Light9 `
    -AutoSize `
    -ClearSheet

Write-Host 'Summary sheet added'

Schedule Monthly Report

Wrap the entire script in a function and schedule it via Windows Task Scheduler. The script saves the report to a shared network path where stakeholders can access it without any manual intervention.

# Scheduled task PowerShell action (full path to script)
$action  = New-ScheduledTaskAction -Execute 'powershell.exe' `
    -Argument '-NonInteractive -File C:\Scripts\Generate-ADReport.ps1'
$trigger = New-ScheduledTaskTrigger -Monthly -DaysOfMonth 1 -At '06:00'
$settings = New-ScheduledTaskSettingsSet -RunOnlyIfNetworkAvailable

Register-ScheduledTask -TaskName 'Monthly AD Report' `
    -Action $action -Trigger $trigger -Settings $settings `
    -RunLevel Highest -User 'CONTOSO\svc_reports'

Write-Host 'Monthly AD report scheduled for 1st of each month at 06:00'

Common Errors and Fixes

  • ImportExcel requires .NET Framework on older PowerShell. ImportExcel 7.x requires .NET 5 or later and works best with PowerShell 7. On Windows PowerShell 5.1, use ImportExcel 5.x or 6.x which targets .NET Framework 4.x. Check compatibility with (Get-Module ImportExcel).Version after installation.
  • Large AD queries may time out — filter with SearchScope. Querying all users in a large domain can time out or return too many objects. Use -SearchBase 'OU=Employees,DC=contoso,DC=com' to limit the search scope, or -ResultSetSize 5000 to cap results. Add -SearchScope Subtree explicitly for predictable behavior.

Related Cmdlets / See Also

Wrapping Up

Combining Get-ADUser, Get-ADGroup, Get-ADComputer, and Export-Excel turns a day-long reporting task into a scheduled five-minute script. Use table styles for professional appearance, add a summary sheet for executive consumption, and run it monthly via Task Scheduler so the report is always current without any manual effort.

#active-directory#automation#excel#report
Send-Item -To