How to Remove Duplicates in Excel Files on SharePoint

How-to basics · Updated November 18, 2026

To remove duplicates from an Excel workbook stored in SharePoint, open the workbook in Excel, select the full data range, then choose Data > Remove Duplicates. Choose the columns that define a duplicate and confirm. Excel keeps the first matching row and removes later matches, so make a copy or confirm version history is available before changing shared data.

What You’ll Need

  • Permission to edit the Excel workbook in its SharePoint document library. If you only have read access, ask the owner for permission or a working copy.
  • A clear rule for what counts as a duplicate. For example, duplicate email addresses may be the rule even when the person’s other details differ.
  • A backup or a way to restore the previous version. Download a copy before editing, or check that SharePoint version history is available for the file.
  • A reliable internet connection if you are editing in Excel for the web. For a large workbook, Excel for Microsoft 365 on Windows may be more convenient.

Step-by-Step

  1. Open the workbook from SharePoint. In the SharePoint document library, select the Excel file and open it in Excel. You can use Excel for the web or, if available, choose Open in Desktop App to use Excel for Microsoft 365 on Windows. Check the file name and location so you do not clean a downloaded copy by mistake. If Excel says the workbook is read-only, resolve the permission or checkout issue before proceeding.
  2. Protect the original data. Before removing anything, make a duplicate of the workbook or download a copy to a safe location. If your organization relies on SharePoint version history, confirm that the file has previous versions and that you are allowed to restore one. Removing duplicate rows changes the workbook; a backup is safer than relying only on Undo, especially if other people are editing the file.
  3. Identify the data and its headers. Locate the list you want to clean and check that it has one header row, such as Customer ID, Email, or Order Date. Remove blank title rows or notes from inside the data range if practical. If the records are already formatted as an Excel table, click a cell in that table. Tables make it easier to keep the header and data together. Avoid including totals or unrelated notes in the range.
  4. Decide which columns define a duplicate. Write down the rule before opening the tool. If two records count as duplicates only when both their email address and order number match, select both of those columns in the dialog. If the email alone identifies a duplicate, select just the email column. This choice matters: selecting too few columns can remove records that are different in other important fields, while selecting every column will only remove rows whose values match across all those columns.
  5. Select the complete data range. Click inside the table, or select the full rectangular range containing the header and all records. Include every column whose values you need to keep together, not just the column being checked. For example, if a row contains a customer name, email, and account status, select all three columns so that Excel removes a whole duplicate record rather than leaving related fields out of alignment. In desktop Excel, if a selection prompt appears, choose to expand the selection when you intend to remove complete rows.
  6. Open Remove Duplicates. In Excel for Microsoft 365 on Windows, select Data > Remove Duplicates on the ribbon. In Excel for the web, open the Data tab and choose Remove Duplicates; the command may be grouped under Data Tools or shown directly depending on the available ribbon layout. On a Mac, use the Data tab and the Remove Duplicates command as well. If the ribbon is collapsed, expand it or use the command search box to find the feature.
  7. Set the header and column options. In the Remove Duplicates dialog, confirm whether the selected range has headers. Make sure the header row is treated as labels, not as a record. Review the column checklist and leave selected only the fields that make a record count as a duplicate. For a duplicate email rule, select Email and clear the other columns. For a duplicate combination rule, select every field in that combination. Read the column names carefully before confirming.
  8. Run the check and review Excel’s result. Select OK to remove later matching rows. Excel reports how many duplicate values were removed and how many unique values remain. Treat that message as a useful check, not proof that the rule was correct. Scan the surviving records, especially the first few rows and any cases where the same key has different names, dates, or statuses. Excel normally retains the first occurrence in the selected range, so row order determines which copy stays.
  9. Save and verify the SharePoint file. In Excel for the web, changes normally save automatically; wait for the save status to finish before closing the tab. In desktop Excel, save the workbook and allow it to sync to SharePoint. Reopen the file from the document library or refresh the browser view to confirm the cleaned data is present. If others use the workbook, tell them what rule you applied and when, so they do not mistake the change for missing data.

The same steps work whether the records are in a normal cell range or an Excel table. The key decision is not the storage location: it is which columns define a duplicate and whether you have selected the complete rows you intend to keep or remove.

Common Mistakes

  • Selecting only the key column when you mean to delete whole records. Removing duplicates from a narrow selection can cause Excel to warn you about expanding the selection. If you proceed with only a single column, you risk separating values from the rest of their row. Select the whole dataset when the goal is to remove complete repeated records.
  • Checking every column when duplicates should be based on one field. If two rows have the same email but different capitalization, notes, or status values, selecting all columns may keep both. Choose only the columns that define identity for your task.
  • Checking too few columns. If you select Customer Name alone, two different customers with the same name may be treated as duplicates. A customer ID or a combination of fields can provide a more dependable rule. Decide whether the value is genuinely unique before deleting records.
  • Forgetting that Excel keeps the first matching row. If the older record appears first and the newer one later, the older record may remain. Sort or review the data first if you need a particular version to survive. Do not assume Excel automatically keeps the newest or most complete row.
  • Including a title row, totals, or unrelated data. A heading or summary row can confuse the selection or produce an unexpected result. Set the header option correctly and limit the selection to the intended table or range.
  • Expecting the cleanup to change a SharePoint list. A workbook saved in a SharePoint document library is an Excel file. Editing an exported or connected copy does not necessarily remove items from the original SharePoint list. Check where the authoritative data is stored before making changes.

Tips

  • Use a stable identifier where possible. A customer number, product code, or transaction ID is usually safer than a name, which may be shared by different people or typed inconsistently.
  • Review spacing and formatting before comparing. Values that look alike can differ because one has a leading or trailing space, a different date representation, or a numeric value stored as text. Clean or standardize data first if records that appear identical are not being recognized. Make those changes on a copy if the original formatting matters.
  • Keep a record of the rule. Add a note in a separate worksheet or in your team’s change log stating which columns were used. This makes the cleanup easier to explain and repeat next time.
  • Use Conditional Formatting for a preview. If you want to inspect suspected repeats before deleting rows, select the relevant cells and use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This highlights values in the selected area; it does not remove rows, and the result depends on what you selected.
  • Consider a separate unique-results view for recurring work. In Microsoft 365 or Excel 2021 and later, the UNIQUE function can return distinct values in another area without deleting source rows. It is useful for a review list, but it is not a replacement for removing records from the original table. Keep the source data intact until you have verified the output.
  • Coordinate with collaborators. If several people are editing the workbook, agree on a time to clean it and ask them to avoid changing the same range during the operation. Collaboration and syncing can make it harder to tell which copy contains the latest changes.

Troubleshooting

  • Remove Duplicates is missing or unavailable. Confirm that the workbook is open in an editable Excel session, not in a preview or read-only view. In Excel for the web, check the Data tab and expand the ribbon if it is collapsed. If your organization limits editing or the file is protected, ask the owner to grant access or unlock the relevant sheet.
  • Excel says no duplicates were found. Check the selected columns and header setting. Look for extra spaces, inconsistent punctuation, dates stored in different formats, or text-versus-number differences. Try highlighting duplicates in the key column first, then standardize the values on a copy and run the removal again.
  • Excel removed more rows than expected. Undo immediately with Ctrl+Z in Windows or Command+Z on Mac, if the action is still available in the current editing session. Then restore the full data if needed and rerun the tool with a more specific combination of columns. If Undo is no longer available, use a backup or restore a prior SharePoint version; in the document library, open the file’s menu and choose Version history, if permitted.
  • The workbook does not show the change in SharePoint. Wait for Excel to finish saving or syncing, then refresh the document library and reopen the file. Check that you edited the workbook stored in the library rather than a local download or a second copy with a similar name. If sync reports an error, resolve it before closing or replacing files.
  • The source is a SharePoint list, not an Excel workbook. Removing rows in an Excel export or workbook does not automatically delete list items. Make the change in the list itself using an approved list-management process, or ask the list owner for help. Be cautious with imports or overwrites, as they can change or add many records.

Frequently Asked Questions

Does Remove Duplicates delete the entire row?

It removes later duplicate records from the selected range, keeping the first occurrence. Select the full set of columns for those records so the remaining cells stay together. If you select only one column, you may not get the whole-row result you intended.

Can I remove duplicates based on just one column?

Yes. In the dialog, select only that column as the duplicate rule, while selecting the full range if you want entire records removed. For example, select Email as the matching field and include the other record columns in your selected range.

Will this change the original SharePoint list?

Only if you are editing data in the list through a process that updates the list. Cleaning an Excel workbook or exported copy changes that workbook, not necessarily the source list. Confirm which location is authoritative before deleting records.

Can I undo duplicate removal?

Try Ctrl+Z on Windows or Command+Z on Mac soon after the change, while the same editing session is open. If undo is unavailable, use your backup or restore a previous file version from SharePoint version history, if you have permission.

Does Excel treat uppercase and lowercase as different?

Remove Duplicates is generally not case-sensitive, so values such as North and NORTH can be treated as duplicates when compared in the same selected field. If capitalization carries meaning in your data, verify the result on a copy before removing anything.

Can I find duplicates without deleting them?

Yes. Use Conditional Formatting to highlight duplicate values, or make a separate review copy of the data. You can inspect the highlighted rows and decide what to keep before running Remove Duplicates on the original workbook.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.