How to see which rows Remove Duplicates deletes, and keep a copy

Excel's Data › Remove Duplicates reports how many duplicate values it removed and how many unique values remain. It does not say which rows went. If you need to show a colleague or client what changed, or check the result later, flag the duplicates yourself first and remove exactly the rows you flagged. The formulas below use standard COUNTIFS behaviour, so they should work the same way in Google Sheets; we checked the logic with the Python script in section 3 and have not run the formulas in Excel or Sheets ourselves.

1. Add a helper column that marks repeats

Say the email address is in column C and the data starts in row 2 (row 1 is the header). In the first empty column, type a heading such as Repeat, then in row 2 enter:

=COUNTIFS($C$2:C2,C2)>1

Fill it down to the last row. The range grows as it goes down, so each row counts how often its email has appeared so far. The first copy shows FALSE and every later copy shows TRUE. That keeps the first occurrence, as Remove Duplicates does.

2. Match on two columns

To treat rows as duplicates only when, say, the first name (column A) and the email (column C) both match, add one range/value pair per column:

=COUNTIFS($A$2:A2,A2,$C$2:C2,C2)>1

3. What the helper column computes, in Python

We checked the logic of the formula with this equivalent script on the sample contact list (email in the second column). It prints the value the Repeat column would show for each row:

import csv

seen = {}
with open("contacts.csv", newline="", encoding="utf-8-sig") as f:
    reader = csv.reader(f)
    next(reader)
    for n, row in enumerate(reader, start=2):
        email = row[1].strip().lower() if len(row) > 1 else ""
        seen[email] = seen.get(email, 0) + 1
        print(f"row {n}: Repeat = {seen[email] > 1}")

row 2: Repeat = False
row 3: Repeat = False
row 4: Repeat = True
row 5: Repeat = False
row 6: Repeat = True
row 7: Repeat = False
row 8: Repeat = False

Rows 4 and 6 are the repeats (the script trims spaces first; the COUNTIFS formula alone would miss row 4 because of its trailing space, which is why the TRIM step below matters). Row 7 has an empty email: decide by hand what to do with rows like that, since the formula treats all empty cells as the same value.

4. Deal with stray spaces

COUNTIFS already ignores upper and lower case, but "ann@example.com " with a trailing space does not match "ann@example.com". Make a cleaned copy of the key first: in another helper column put =TRIM(C2), fill it down, and point COUNTIFS at that column instead of C.

5. Keep a copy, then remove

Turn on a filter (Data › Filter) and show only the rows where Repeat is TRUE.
Select those rows, copy them, and paste them into a new sheet called Removed. That sheet is your record.
Back on the data sheet, with the filter still showing only TRUE rows, delete those rows (select the row numbers, right-click, Delete Row).
Clear the filter. What is left is your de-duplicated data. Delete the helper columns before you save it as CSV.

Because you delete exactly the rows you copied, the Removed sheet and the result always agree.

Limits of this method

COUNTIFS treats * and ? in a value as wildcards, so values that contain them can match more than they should. Put ~ in front of those characters, or use a different method for such data.
It slows down on large sheets, because each row looks back over every row above it. Tens of thousands of rows can take a while.
Excel may change long numbers, leading zeros and dates when it opens a CSV, before any of this happens. Import the file with Data › From Text/CSV and set those columns to Text if that matters.

Offline CSV Cleaner produces this record automatically. Next to the clean CSV it writes a "removed rows" CSV with each removed row's original row number, the reason (duplicate, wrong number of columns, empty match column) and, for duplicates, the row it duplicated. It matches on the columns you choose, ignores case and stray spaces, and runs in your browser without uploading the file. Try the free demo (full results for files up to 200 rows), or get the full version for USD 15. Disclosure: this guide is published by the maker of Offline CSV Cleaner.

Last updated 2026-10-27. Code on this page was run on that date and the output shown is the real output. Spreadsheet menu steps are written from Microsoft's and Google's documented behaviour; where we have not run something ourselves, the page says so.