How to List All SharePoint Lists Across Your Tenant Using PnP PowerShell

Overview

SharePoint administrators are often tasked with producing reports like:

Which sites contain outdated or unused lists?
Where are document libraries being duplicated across teams?
How can we identify content sprawl in our Microsoft 365 tenant?

This is especially common when preparing for governance reviews, restructuring initiatives, or compliance audits. Manual collection is too slow and prone to error.

To solve this quickly, HarjTech built a production-ready PowerShell script using PnP PowerShell that loops through all sites, collects every list, and outputs a clean CSV.

This guide walks you through:

Installing PnP PowerShell
Connecting to your tenant securely
Running the script
Understanding what it does
Exporting a full Excel-ready list audit in minutes

Step 1 – Install PnP PowerShell

Open PowerShell in administrator mode and run the following command to install the module:

Install-Module -Name PnP.PowerShell -Force -AllowClobber

When prompted to trust the repository, type Y and press Enter.

Step 2 – Connect to SharePoint Online

Use the command below to log in securely. This supports multi-factor authentication.

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

Replace yourtenant with your Microsoft 365 tenant name.

Step 3 – Run the HarjTech PowerShell Script

This script connects to the SharePoint admin center, retrieves all non-personal site collections, and collects data on every list found.

# -------------------------------------------------------------
# Script by HarjTech | www.harjtech.com
# Purpose: List all SharePoint Lists across the tenant
# Outputs: Clean CSV with site title, list name, type, item count
# -------------------------------------------------------------

Import-Module PnP.PowerShell

# Connect to SharePoint Admin Center
$adminSiteUrl = "https://yourtenant-admin.sharepoint.com"
Connect-PnPOnline -Url $adminSiteUrl -Interactive

# Get all site collections, excluding OneDrive personal sites
$sites = Get-PnPTenantSite | Where-Object { $_.Template -ne "SPSPERS" }

# Prepare output array
$report = @()

# Loop through each site and retrieve lists
foreach ($site in $sites) {
    Write-Host "Processing site:" $site.Url -ForegroundColor Cyan

    try {
        Connect-PnPOnline -Url $site.Url -Interactive

        $lists = Get-PnPList

        foreach ($list in $lists) {
            $report += [PSCustomObject]@{
                SiteTitle     = $site.Title
                SiteUrl       = $site.Url
                ListTitle     = $list.Title
                ItemCount     = $list.ItemCount
                ListTemplate  = $list.BaseTemplate
                Created       = $list.Created
                LastModified  = $list.LastItemModifiedDate
            }
        }
    }
    catch {
        Write-Warning "Could not access site: $($site.Url)"
    }
}

# Export to CSV
$csvPath = "$env:USERPROFILE\Desktop\Tenant-List-Audit-HarjTech.csv"
$report | Export-Csv -Path $csvPath -NoTypeInformation -Encoding UTF8

Write-Host "`nReport saved to $csvPath" -ForegroundColor Green

What This Script Does

Connects to your tenant’s SharePoint Admin Center
Retrieves every non-personal site collection
Loops through each site and fetches:
- List title
- List type/template
- Item count
- Creation and last modified dates
Exports everything to a CSV on your desktop