How to Compare Microsoft Lists with Excel to Find Duplicate Names
Question details
The user wants to compare applicant names entered in a Microsoft List with a separate Excel worksheet to identify duplicate entries across the two data sources.

- Product
- Microsoft Lists and Microsoft Excel
- Device & OS
- not provided
- Scenario
- Managing applicant names across both Microsoft Lists and Excel spreadsheets and needing automated cross-platform duplicate detection.
- Observed behavior
- Microsoft Lists can enforce unique values internally, but it does not natively check external Excel files for existing names without a custom automated workflow.
Ensure your Excel file is saved in OneDrive or SharePoint, and that the data range containing the applicant names is formatted as an official Excel Table.
Create a Power Automate Cloud Flow to Cross-Reference Names
A custom Power Automate cloud flow is the most effective way to trigger a duplicate check against an Excel file whenever a new item is added to a Microsoft List.
Since standard SharePoint workflows cannot natively cross-reference external Excel files on the fly, Power Automate serves as the bridge between Microsoft Lists and Excel.
The automated flow will trigger upon item creation, search your designated Excel Table for matching text, and update the List item if a duplicate is found.
Open your Excel worksheet, select your data range, and press Ctrl+T to format it as a Table. Save this file to your connected OneDrive or SharePoint account.
Log in to Power Automate, click 'Create', and select 'Automated cloud flow'. Name your flow and choose 'When an item is created or modified' as your SharePoint/Microsoft Lists trigger.
Add a new step and search for the 'List rows present in a table' action for Excel Online. Point it to the location and Table name of your saved Excel file.
Add a 'Condition' control. Set it to check if the applicant name from your Microsoft List trigger is equal to the applicant name from the Excel table output.
In the 'If yes' branch of the condition, add an 'Update item' action for SharePoint. Configure it to update a specific column (e.g., 'Duplicate Status') in your Microsoft List to flag the entry as a duplicate.

How to Find Duplicates Instantly Using WPS Spreadsheet
If you want to avoid building complex cloud flows between Microsoft Lists and Excel, you can simply export your list and manage all your applicant names within WPS Spreadsheet. WPS offers built-in, easy-to-use tools to instantly highlight duplicates across massive datasets.
- 1. Open your data in WPS Office: Launch WPS Spreadsheet and open your combined applicant tracking file.
- 2. Select the target columns: Click and drag to highlight the columns or rows containing the applicant names you want to compare.
- 3. Access Conditional Formatting: Navigate to the 'Home' tab on the top ribbon and click on 'Conditional Formatting'.
- 4. Apply duplicate rules: Hover over 'Highlight Cells Rules' in the dropdown menu and select 'Duplicate Values'.
- 5. Highlight matching names: Choose your preferred highlight color from the prompt and click 'OK'. All duplicate names between your lists will be instantly highlighted.

Frequently Asked Questions
Can Microsoft Lists automatically prevent duplicates from external sources?
No, the 'Enforce Unique Values' feature in Microsoft Lists only checks for duplicates within that specific list's column. It cannot natively scan external Excel files or other SharePoint lists without a third-party automation tool or a Power Automate flow.
Why must the Excel file be formatted as a Table for Power Automate?
Power Automate requires data to be organized in an official Excel Table (created via Insert > Table) so its API can accurately identify the exact boundaries, rows, and headers through the 'List rows present in a table' action.
Is there a simpler way to compare two lists without Power Automate?
Yes, if you do not require real-time cloud syncing, you can export your Microsoft List data to an Excel or WPS Spreadsheet file, paste it next to your existing data, and use the 'Highlight Duplicates' conditional formatting feature to visually identify matches in seconds.




