Define a duplicate before you remove anything
A duplicate row is defined by the columns that represent the same real record, not by two cells that merely look alike. Write that rule down before opening Excel, preserve the received workbook unchanged, and perform every review and removal on a clearly named copy.
For example, consider a registration export with Registration ID, Surname, Email, and Session. Two people can share a surname, so surname alone is unsafe. Registration ID may be enough when each person can register once; if one person can attend several sessions, the practical key might be Registration ID plus Session. Selecting too few columns can remove valid registrations, while selecting every column can leave a duplicate in place because a timestamp or note differs.
Save an untouched file such as registrations-received-2026-08-09.xlsx, then create registrations-dedup-working-copy.xlsx. Inside the working file, also copy the source worksheet and label the tabs Source copy and Dedup working. Record the row count, filters, visible columns, and key rule in a small notes sheet. This gives a reviewer enough context to reproduce the decision instead of seeing only a smaller final table.
Highlight candidates before using Remove Duplicates
Review potential duplicates first with Excel's conditional formatting, then run removal only on the working sheet. Microsoft currently documents this workflow for Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016.
Select the key column or columns on Dedup working, then use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Highlighting does not define a duplicate row across several columns for you; it helps expose repeated cell values so you can inspect their surrounding records. Microsoft notes that Excel cannot highlight duplicates in the Values area of a PivotTable report, so review the underlying source range or table instead.
Inspect several highlighted cases. Check whether repeated IDs have the same session, whether blank IDs are present, and whether leading or trailing spaces make values appear different. For the registration example, a repeated surname with two distinct IDs is expected, while the same ID and session twice needs investigation against the registration system.
When the key rule is confirmed, select the complete data range on the working sheet and choose Data > Remove Duplicates. Under Columns, check only the fields that make up the agreed key, then confirm. Microsoft warns that this feature permanently deletes duplicate data and recommends moving or copying the original data to another worksheet first. Treat that copy as the recovery path; do not rely on an open-session Undo command as your audit plan.
Reconcile Excel's message with your own counts
The result message is a checkpoint, not a complete audit. Write down Excel's reported duplicate and unique counts, then reconcile the working sheet's before and after row totals while accounting for the header row.
If the working sheet had 1,001 used rows including one header and ends with 981, the observed reduction is 20 data rows. Compare that reduction with the message and investigate any mismatch before saving. Microsoft cautions that the reported duplicate and unique counts can include empty cells and spaces, so blank keys, space-only cells, formulas returning empty text, and filtered ranges deserve specific attention.
Do not assume that a lower row count proves the remaining row is the authoritative one. Open several removed-key cases in the untouched source copy and in the source system. Confirm which record owns the current email, status, or session, and check whether the duplicate resulted from an export retry rather than a real duplicate in the system. If the workbook controls access, billing, attendance, or compliance, the data owner should approve the exceptions before the cleaned copy replaces any downstream input.
Compare a small before-and-after key extract
A small, consistently sorted text extract can show which lines disappeared without exposing the whole workbook. Copy the agreed key plus one harmless context field from the untouched sheet and the cleaned sheet into two separate plain-text lists, sort both with the same rule in Excel, and paste them into the Text Diff tool: before on the left, after on the right.
Line mode is appropriate when one line represents one record, such as REG-0042 | morning. A removed duplicate should appear as a deleted line while unrelated keys remain aligned. If the diff marks most lines as changed, stop and check sort order, whitespace, headers, and column order. Do not “fix” the extract until you understand why the comparison is noisy.
The tool compares pasted text by line, word, or character. It does not open an XLSX file, count unique values, choose a business key, delete rows, restore a workbook, or prove that the retained record is correct. Use synthetic or redacted values when a browser comparison is not approved for registration, customer, employee, or production data.
If a reviewer only needs to inspect a derived workbook or CSV, the CSV and Excel Preview tool can display selected CSV, TSV, XLSX, and XLS files, switch sheets, search values, and export the current sheet as a separate CSV. It is preview-only: it does not edit the workbook, write changes back, or preserve formulas, formatting, macros, and multiple sheets in a CSV export.
Verify the cleaned copy before it leaves the review folder
The cleaned copy is ready only after key uniqueness, representative records, formulas, and the destination import are checked. Keep the original, working workbook, notes, and small diff extract together until the responsible owner accepts the result.
- Recount blank keys and repeated keys with an independent Excel formula, PivotTable, or approved data process.
- Spot-check one expected removal, one legitimate repeated surname with distinct IDs, one blank-key row, and several unchanged records.
- Confirm formulas, structured references, filters, totals, and named ranges still cover the intended rows.
- Save under a new filename, close the workbook, reopen it, and repeat a small key check.
- If the file will be imported elsewhere, test a representative subset in that system and reconcile accepted and rejected row counts.
Excel for the web and Excel for Mac are not listed in the current Microsoft Support page used for these menu instructions. If those interfaces differ, search the product's built-in Help for Remove Duplicates and make the same untouched copy first; do not assume a web or Mac button has identical scope or recovery behavior.
Frequently asked questions
Which columns should I select in Remove Duplicates?
Select the columns that jointly identify one real record. Use a documented ID where possible, and add a second field such as Session when the same ID can legitimately appear more than once. Names alone are rarely safe keys.
Does highlighting duplicates remove any rows?
No. Conditional formatting marks repeated values for review. Removal happens through Data > Remove Duplicates, and the selected columns in that dialog define which rows Excel treats as duplicates.
Can I recover rows after Remove Duplicates?
Treat removal as permanent. Microsoft recommends moving or copying the original data first, so restore from the untouched workbook or source worksheet rather than depending on Undo or an overwritten file.
Can Text Diff compare two Excel workbooks directly?
No. It compares text pasted into two fields. Prepare a small, consistently sorted key extract and return to Excel or the source system for structured validation and recovery.
Why does Excel's count differ from the rows I expected to remove?
Blank cells, spaces, the selected range, headers, filters, and the chosen key columns can change the result. Microsoft specifically notes that reported counts can include empty cells and spaces, so reconcile totals and inspect those cases before accepting the file.
Do these menu paths apply to Excel for the web or Mac?
The Microsoft page checked on 2026-08-09 lists Excel for Microsoft 365, 2024, 2021, 2019, and 2016, not the web or Mac editions. Use version-specific Help when the interface differs and preserve a source copy before any removal.