Remove Duplicates in Excel and Google Sheets

First, make a copy

Removing duplicates deletes rows. Before you start, duplicate the sheet (right-click the tab › Move or Copy › Create a copy) or save a copy of the file. If anything goes wrong, you can return to the original without relying on Undo.

Step 1: Check for duplicates before removing them

In Excel, select the column and go to Home › Conditional Formatting › Highlight Cells Rules › Duplicate Values. Repeated values are highlighted. This helps you see the extent of the problem and spot unusual cases, like the same customer with different email addresses.

In Google Sheets, the equivalent is Format › Conditional formatting with the rule "Custom formula is" and a formula such as =CONT.SE(A:A;A1)>1.

Step 2: Remove duplicates in Excel

1. Click any cell in the table.
2. Go to Data › Remove Duplicates.
3. Check whether your table has headers.
4. Choose the columns that determine what counts as a "duplicate." For example, if you select only the Email column, rows with the same email address count as duplicates even if the names are different. If you select all columns, only completely identical rows are removed.
5. Click OK. Excel tells you how many duplicates were removed and how many unique values remain.

The first instance of each value is kept, and later ones are deleted. If you want to keep the most recent entry, sort the table by date, newest to oldest, before removing duplicates.

Step 2 (alternative): Remove duplicates in Google Sheets

1. Select the range.
2. Go to Data › Data cleanup › Remove duplicates.
3. Indicate whether there are headers and which columns to check.
4. Click Remove duplicates.

Keep the original data: Use the UNIQUE function

If you'd rather keep the original data and create a list without duplicates alongside it, use a formula:

• Excel (recent versions, such as Microsoft 365): =ÚNICO(A2:A500).
• Google Sheets: =UNIQUE(A2:A500).

The unique list updates automatically when you change the source data.

Why some duplicates aren't removed

• Extra spaces: "Maria " and "Maria" are different to a spreadsheet. Clean them up with the ARRUMAR (Excel) or TRIM (Sheets) function in a helper column.
• Uppercase and lowercase letters: Excel's Remove Duplicates feature isn't case-sensitive, but other comparisons may be. Standardize the text first.
• Accents: "Joao" and "João" are different values.
• Numbers stored as text: "123" (text) and 123 (a number) aren't treated as the same value.
• Invisible characters copied from the internet. The TIRAR function (Excel) removes some of them.

For simple lists outside a spreadsheet

If you just have a list (emails, names, codes) copied from somewhere, paste it into Remove duplicate lines. It keeps the original order, can ignore capitalization and spaces at the beginning or end, and gives you a clean list to copy. To sort it alphabetically afterward, use Sort lines.

Example: an event registration list

An online form received 480 registrations, and many people submitted it two or three times. The goal is to keep one registration per person—the most recent one.

1. Duplicate the response sheet.
2. Create a helper column with the email address in lowercase and without extra spaces: =MINÚSCULA(ARRUMAR(C2)).
3. Sort the table by submission date, newest to oldest.
4. Use Remove Duplicates and select only the helper column. This keeps the most recent registration for each email address.
5. Check the total, then sort by name again if you like.

The same approach works for customer, student, and product lists: choose the column that uniquely identifies each person or item (email, ID number, product code) and standardize it before removing duplicates.

Frequently asked questions