SharePoint Document Issues

How to Find Duplicate Files in a Large SharePoint Library

Maira Mehtab Sep 21, 2026 868 views

Question details

The user needs to identify and export duplicate files within a SharePoint document library containing approximately 28,000 items.

Product: SharePoint
Device & OS: not provided
Scenario: Attempting to clean up and deduplicate a massive document library where standard views fail to load all items.
Observed behavior: The 5,000-item list view threshold in the modern SharePoint interface prevents users from viewing all files simultaneously or exporting the complete list to find duplicates.

Before you start

Before proceeding, clearly define your criteria for a duplicate file (e.g., exact same file name, identical file size, or matching content hash) to ensure accurate filtering.

Solution 1Recommended

Use Indexed Columns and Export to Excel

Bypass the 5,000-item threshold by indexing your columns, filtering the view into smaller batches, and analyzing the exported data in a spreadsheet.

SharePoint imposes a List View Threshold of 5,000 items to protect server performance. To work around this, you must rely on indexed columns to filter the library into smaller, exportable segments.

Index the Name column

Navigate to Library Settings, select 'Indexed Columns', and create a new index for the file 'Name' (or other criteria like 'Modified Date').

Create a Filtered View

Create a new view that filters items based on your indexed column. Use parameters (such as names starting with A-M) to ensure the view returns fewer than 5,000 items.

Export to Excel

From the modern SharePoint interface, click the 'Export' button in the top ribbon and select 'Export to Excel'.

Find Duplicates in Spreadsheet

Open the downloaded file in your spreadsheet software. Select the file name column, navigate to Conditional Formatting, and choose the option to highlight duplicate values.

Data Export: Exporting to Excel only downloads the file metadata, making it entirely safe and fast even for very large libraries.

Solution 2

Sync Library to OneDrive for Local Sorting

Use the OneDrive desktop client to sync the SharePoint library and leverage Windows File Explorer to spot identical names.

Solution 3

Use PowerShell to Retrieve File Properties

Utilize PowerShell scripts to programmatically bypass view limits and pull exhaustive item properties for advanced comparison.

Free Microsoft Office alternative

Analyze Exported SharePoint Lists with WPS Office

While SharePoint requires specific workarounds to bypass the 5,000-item limit, finding the actual duplicates in the exported lists is incredibly simple with WPS Office. As a fast, lightweight Microsoft Office alternative, WPS Spreadsheet effortlessly handles massive exported CSVs and features one-click conditional formatting to highlight duplicate file names.

1. Download and Install: Download WPS Office for free and install it on your PC or Mac.
2. Open the Exported List: Launch WPS Spreadsheet and open the .iqy, .xlsx, or .csv file you exported from the SharePoint modern view.
3. Highlight Duplicate Files: Highlight the 'Name' column, navigate to the Home tab, click Conditional Formatting, and select 'Highlight Duplicate Values'.

Fully compatible with Microsoft Excel (.xlsx) and CSV files exported directly from SharePoint.Lightweight architecture handles lists with tens of thousands of rows smoothly.Built-in Highlight Duplicate Values functionality for instant data cleanup.Familiar spreadsheet interface ensuring seamless migration with zero learning curve.

Frequently Asked Questions

What is the 5,000-item List View Threshold in SharePoint?

The List View Threshold is a limit set by Microsoft that prevents a single database query in SharePoint from returning more than 5,000 items at once. This prevents excessive locking of SQL server databases and ensures performance remains stable across the environment.

Can I delete duplicate files directly from the exported Excel spreadsheet?

No, exporting a SharePoint list to Excel only downloads the metadata (like file names and paths). To delete the duplicates, you must use the spreadsheet to identify which files are redundant, and then manually delete them within SharePoint or use an automated script.

Why does syncing to OneDrive help bypass the SharePoint view limit?

When you sync a SharePoint library to your local machine via OneDrive, you view the files using Windows File Explorer. File Explorer does not rely on SharePoint's web database queries, meaning it is not bound by the 5,000-item threshold and can seamlessly display and sort all 28,000 items.

Are there automated tools to automatically delete duplicates in SharePoint?

Yes, there are third-party administrative tools and specialized PowerShell modules designed to find and delete duplicates automatically. However, always ensure these tools are reputable, as improper deduplication can lead to accidental data loss.

Document & File Issues