How to Find and Remove Duplicates in Excel and Google Sheets

The short answer: the order of steps

1. Back up the original data: copy the sheet or the whole file.
2. Highlight duplicates to see the scale of the problem and make sure they really are duplicates.
3. Remove them with the built-in tool or build a unique list with a formula on a separate sheet.

The built-in removal is fast but changes your data. Formulas delete nothing and recalculate when the source changes.

How to keep the original data safe

• Copy the sheet: right-click the sheet tab → Move or Copy (Excel) or Duplicate (Google Sheets).
• A row number column. Add an ID column with 1, 2, 3… so you can restore the original order after cleaning and sorting.
• Version history. Google Sheets always has it: File → Version history. In Excel it is available for files stored in OneDrive or SharePoint.
• Undo (Ctrl+Z) only works until you close the file, so don't rely on it.

Highlighting duplicates

Excel: select the column → Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Every repeat gets coloured, including the first occurrence.

Google Sheets: select the range → Format → Conditional formatting → choose "Custom formula is" and enter:

=COUNTIF($A:$A, $A1)>1

The rule's range must start at the cell the formula refers to (A1 here). If your data starts at A2, use $A2.

The built-in removal tool

Excel: select the table → Data → Remove Duplicates. Tell it whether your data has headers and choose the columns used to compare rows.

Google Sheets: Data → Data cleanup → Remove duplicates. Same settings: header row and the columns to compare.

What you need to know:

• The first occurrence from the top stays, and other rows are deleted entirely. If you want to keep the most recent record, sort by date from newest to oldest first.
• Only the selected columns are compared. Tick just the "Phone" column and rows with the same phone will be deleted even if the other fields differ.

The UNIQUE formula: a clean list without deleting

=UNIQUE(A2:A500)

The function returns a list without repeats and updates itself when the data changes. Give it several columns, =UNIQUE(A2:C500), and it returns unique rows. It exists in Google Sheets and in current Excel versions, but not in older ones.

COUNTIF: flag repeats in a helper column

This works in any version and shows exactly which rows repeat. In an empty column, enter and fill down:

=COUNTIF($A$2:A2, A2)

The range grows with the formula, so the first occurrence gets 1, the second gets 2, and so on. Filter for values greater than 1: those are the repeats you can delete while keeping the first ones. To count every value across the whole list, use a fixed range: =COUNTIF($A$2:$A$500, A2).

Duplicates across several columns

Sometimes a duplicate means several fields match at once: name and date, product and warehouse.

• COUNTIFS checks several conditions:

=COUNTIFS($A$2:A2, A2, $B$2:B2, B2)

• A key column: join the fields and look for repeats in it, =A2&"|"&B2. The separator stops "12" + "3" from matching "1" + "23".
• The built-in tool handles this too: just tick the columns you need.

Hidden duplicates

The software treats values as different even when they look identical to a person:

• Extra spaces at the start, end or middle: remove them with TRIM.
• Letter case: highlighting and removal usually ignore it, but when you build key columns it is safer to normalise text with LOWER.
• Different formats of phones and dates: "+998 90…" and "99890…" are different strings for a formula. Bring data to one format before looking for duplicates.