How to Remove Duplicates in Google Sheets?

Remove Duplicates in Google Sheets - Intro

Duplicate Entries can make a Google Sheets spreadsheet difficult to manage, especially when you are working with customer lists, survey responses, product records, or imported data. The quickest way to remove duplicate rows is to select your data and use Data > Data cleanup > Remove duplicates. Google Sheets can also create a clean list with the UNIQUE function without changing your original data.

How to Remove Duplicates in Google Sheets (Quick Answer)

To remove duplicates in Google Sheets, select your data, click Data, choose Data cleanup, and then click Remove duplicates. Pick the columns to compare, tell Sheets whether your range has a header row, and confirm. Sheets deletes the repeated rows and shows how many it removed.

remove-duplicates-google-sheets-imamuddinpro

That built-in tool is the fastest option, but it is not always the safest. It permanently deletes data, and it can miss duplicates that look identical but contain hidden spaces. In this guide, you will learn how to delete duplicates, find them before deleting anything, keep one copy of each record, and fix the cases where the tool seems not to work.

Understand Duplicate Values and Duplicate Rows Before Cleaning Your Data

Before removing anything, determine whether you want to find repeated values in a single column or duplicate records across an entire dataset. These are two different situations, and choosing the wrong comparison method can remove information you intended to keep.

For example, consider a contact list containing names, email addresses, and phone numbers. Two rows might have the same email address but different names or phone numbers. You may want to treat those records as duplicates based on the email address alone, rather than requiring every column to match.

Here is a quick comparison of the available approaches:

Your Goal	Recommended Method
Delete duplicate rows from the original sheet	Data cleanup > Remove duplicates
Create a duplicate-free list without changing the original	UNIQUE function
Find and highlight repeated values	Conditional formatting with COUNTIF
Identify repeated entries before deleting them	COUNTIF helper column

Important: Before using a tool that permanently removes rows, make a copy of your original worksheet. This gives you a safe version to return to if the cleanup removes a record you actually needed.

What Counts as a Duplicate in Google Sheets?

Before you delete anything, it helps to know how Sheets decides that two values match. According to Google's help page on removing duplicates, cells with identical values but different letter cases, formatting, or formulas are considered duplicates. For example, "ANNA" and "anna" are treated as the same entry by the Remove duplicates tool.

Two other details matter in practice:

When you select several columns, Sheets compares the values in the checked columns of each row, so a duplicate is a row that matches another row in those columns.
If you select a single cell inside a range, Sheets removes duplicates from the whole range.

Method 1: Use the Remove Duplicates Tool in Google Sheets

The built-in Remove Duplicates feature is the easiest way to delete repeated records directly from a spreadsheet. You do not need to write a formula or install an add-on. Google Sheets checks the selected data and removes rows that match according to the columns you choose.

Step 1: Select Your Data Range

Open your Google Sheets spreadsheet and select the complete range containing your records. Include all relevant columns and the header row, if your table has one.

For example, suppose your contact list occupies cells A1:C15:

Column A: Name
Column B: Email Address
Column C: Phone Number

Select the entire range, A1:C15, rather than selecting only the email column. This helps ensure that the complete records are cleaned together instead of leaving related information behind.

Step 2: Open the Remove Duplicates Option

After selecting your data, follow this menu path:

Click Data in the top menu.
Choose Data cleanup.
Select Remove duplicates.

Step 3: Choose the Header and Comparison Columns

A dialog box will appear with options for identifying duplicate records.

Review these settings carefully:

Data has header row: Enable this option when your selected range contains column headings. This prevents the headings from being treated as ordinary records.
Columns to analyze: Choose which columns Google Sheets should compare when deciding whether records are duplicates.

For example, selecting Name, Email Address, and Phone Number means Google Sheets checks all three selected columns together. If you select only Email Address, repeated email addresses can be identified even when the other information differs.

Practical tip: When cleaning a customer list, you may want to compare only the Email Address column. When removing completely identical records, compare all relevant columns instead.

Step 4: Remove the Duplicate Rows

Once you have confirmed the header and comparison settings, click Remove duplicates.

Google Sheets will identify repeated records, remove the duplicate rows, and display a summary of the cleanup. Review the remaining data to make sure the correct records were retained.

Important: The first matching record in the selected data is retained, while later matching records are removed. If you need to keep a particular record, such as the most recent customer entry, sort the complete dataset appropriately before removing duplicates.

This is the best method when you want to clean your original spreadsheet quickly without manually searching for repeated entries.

Method 2: Remove Duplicates Using the UNIQUE Function

What if you want to remove duplicates in Google Sheets but keep your original data unchanged? The UNIQUE function is an excellent solution.

Instead of deleting records from your source range, this function returns a separate list containing unique values or rows. It is particularly useful when preparing reports, organizing lists, or creating a clean dataset for further analysis.

Remove Duplicate Values from a Single Column

Suppose your names are listed in cells A2:A15, and you want to display each name only once.

Click an empty cell, such as C2, and enter this formula:

=UNIQUE(A2:A15)

Press Enter. Google Sheets will return a list containing the unique entries while keeping the first-appearance order of the original list.

Remove Duplicate Rows Across Multiple Columns

You can also use UNIQUE to remove repeated records from a complete dataset.

For example, when your table contains names, email addresses, and phone numbers in columns A through C, enter:

=UNIQUE(A2:C15)

This formula returns each distinct row once. A row is considered repeated when its values across the selected columns match another row.

Remove Duplicates Without Including Blank Cells

If your original list contains empty cells and you do not want a blank result included, combine UNIQUE with FILTER:

=UNIQUE(FILTER(A2:A15,A2:A15<>""))

This formula filters out empty cells before returning the unique values.

Important Things to Know About UNIQUE

The original data remains untouched.
The output expands into the cells below or beside the formula as needed.
Make sure the output area is empty so the results have room to appear.
The result can update when the source data changes.
You can copy the resulting list and use Paste special > Values only when you need a fixed, independent copy.

For additional information about the function's arguments and behavior, see Google's official UNIQUE function documentation.

Method 3: Find and Highlight Duplicates with Conditional Formatting

Sometimes deleting repeated entries immediately is not the best decision. You may first want to see which values appear more than once and inspect them before making changes.

Google Sheets provides conditional formatting that can highlight duplicate values automatically. You can use a custom COUNTIF formula to identify repeated entries.

Step 1: Select the Column You Want to Check

Suppose your email addresses are stored in cells A2:A100. Select this range.

Step 2: Open Conditional Formatting

Click Format in the top menu.
Select Conditional formatting.
In the formatting panel, open the Format cells if dropdown.
Choose Custom formula is.

Step 3: Enter the Duplicate Detection Formula

Enter the following formula in the custom formula field:

=AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1)

This formula checks whether the current email address appears more than once in the selected range. The AND condition prevents empty cells from being highlighted as duplicates.

Step 4: Choose a Highlighting Style

Select a background color or another formatting style that makes duplicate values easy to recognize. Click Done to apply the rule.

Google Sheets will now highlight every nonblank value that appears more than once in the selected range.

Highlight Only the Repeated Occurrences

What if you want to highlight only the second, third, and subsequent occurrences while leaving the first entry unchanged?

Use this alternative formula:

=AND(A2<>"",COUNTIF($A$2:A2,A2)>1)

Apply it to the range A2:A100. Because the counting range expands as the formula moves down the column, the first occurrence remains unhighlighted and later repetitions are marked.

This approach is useful when you want to review duplicate entries without automatically removing them.

Method 4: Identify and Remove Duplicates Using a COUNTIF Helper Column

A helper column is another practical way to find duplicate values before deleting them. Unlike direct removal, this approach lets you label repeated entries and review the results before changing the original dataset.

For this example, assume that the email addresses are in column A, beginning in cell A2.

Step 1: Add a Helper Column

In cell B1, enter a heading such as Duplicate Status.

Next, enter the following formula in B2:

=IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","Keep"))

Step 2: Apply the Formula to the Remaining Rows

Press Enter and copy the formula down through the rest of your records.

The formula compares each email address with the entries above it and identifies whether the value has appeared before.

For example, your results might look like this:

Email Address	Duplicate Status
alex@example.com	Keep
maria@example.com	Keep
alex@example.com	Duplicate
james@example.com	Keep
maria@example.com	Duplicate

The first occurrence of each email address is marked Keep, while later occurrences are labeled Duplicate.

Step 3: Filter and Review the Duplicate Entries

Use the filter feature to display rows labeled Duplicate in the helper column. Review the affected records to make sure that removing them will not discard any information you need.

Step 4: Remove the Unwanted Rows

After reviewing the repeated records, remove the corresponding complete rows from your original dataset. Do not delete only the email-address cells, because that can leave unrelated information in the wrong rows.

You can also use this approach when you want to compare multiple identifying fields rather than just one column.

For example, the following formula identifies repeated combinations of a name in column A and an email address in column B:

=IF(OR(A2="",B2=""),"",IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)>1,"Duplicate","Keep"))

This is useful when a duplicate record should be identified only when both selected fields match.

Remember: COUNTIF and COUNTIFS are not case-sensitive. If capitalization differences are important to your comparison, use a separate case-sensitive checking approach rather than relying on these formulas alone.

How to Remove Duplicates in Google Sheets but Keep Only One Record

One of the most common concerns when cleaning a spreadsheet is accidentally deleting the original record along with its repeated copies.

Fortunately, Google Sheets can retain one matching record while removing the others. The important step is choosing the correct column or columns for identifying duplicates.

For example, imagine that your contact list contains the same email address in three different rows. To keep only one record for each email address:

Select the complete contact table, including names, email addresses, phone numbers, and other relevant fields.
Open Data > Data cleanup > Remove duplicates.
Enable Data has header row if your range contains headings.
Select only the Email Address column as the comparison field.
Click Remove duplicates.

Google Sheets will keep the first matching record and remove later rows with the same email address.

However, what if you want to keep the most recent record instead of the first one?

Keep the Latest Record Instead of the First One

Suppose your dataset contains customer records with dates. If the latest record should be retained, sort the entire dataset by the date column in descending order before using Remove Duplicates.

This places the newest record first. You can then remove duplicates using the relevant identifying column, such as the email address.

Important: Always sort the complete table rather than sorting an individual column by itself. Sorting only one column can separate names, dates, and other information from their correct records.

Why Are Some Duplicates Not Being Removed in Google Sheets?

Sometimes two entries appear identical but Google Sheets does not identify them as duplicates. This can happen when the underlying values contain hidden spaces, inconsistent formatting, or other differences.

Here are some common causes and practical ways to address them.

1. Extra Spaces in Text Values

Consider these two entries:

Alex Johnson
Alex Johnson with an extra space at the end

Although they may look identical on the screen, the underlying text can be different. Select the relevant range and use Data > Data cleanup > Trim whitespace before running duplicate detection again.

Be aware that Google's Trim whitespace feature does not remove non-breaking spaces. Those may require additional cleanup if imported data contains unusual spacing characters.

2. Inconsistent Data Formatting

Imported records may contain numbers, dates, or other values stored in different formats. For example, a number stored as text may not behave like the same number stored as a numeric value.

Review the affected columns and standardize their data types and formats before removing duplicates.

3. Incorrect Comparison Columns

If you select the wrong comparison columns, Google Sheets may remove records that are not actually duplicates according to your requirements, or retain repeated records that you intended to remove.

For example, if you want to identify duplicate email addresses, make sure the Email Address column is selected as the matching field. You do not need to require the names and phone numbers to match as well unless that is part of your intended comparison.

4. Blank Cells and Incomplete Records

Empty cells can complicate duplicate checks, particularly when a formula compares entire columns. Consider excluding blank entries where appropriate or reviewing incomplete records separately before performing cleanup.

Which Method Should You Choose to Remove Duplicates?

The best method depends on whether you want to delete data, generate a separate clean list, or inspect repeated records first.

Use Remove Duplicates: When you want to delete repeated rows directly from your original dataset.
Use UNIQUE: When you want a clean list while preserving the original data.
Use Conditional Formatting: When you want to see duplicate values visually before making changes.
Use COUNTIF: When you want to label and review repeated entries before removing their rows.

For ordinary spreadsheet cleanup, the built-in Remove Duplicates tool is usually the quickest solution. However, using UNIQUE or a helper column is often a safer choice when the original records must remain available for comparison.

Remove Duplicates in Google Sheets – [FAQs]

Even after learning the main methods, a few practical questions tend to come up once you work with real data, such as how to keep one record, what to do when nothing seems to change, and whether a mistake can be undone. The answers below cover those situations in a few sentences each.

Select your data range, click Data > Data cleanup > Remove duplicates, choose the columns to compare, and indicate whether the first row is a header. Then click Remove duplicates. Sheets will tell you how many duplicate rows it deleted.

How do I quickly delete duplicates in Google Sheets?

The quickest route is the Data cleanup menu. Click any cell inside your data, since Google says selecting a single cell removes duplicates from the whole range, then open Data > Data cleanup > Remove duplicates and confirm. Take a copy of the sheet first if the data is important.

Is there a way to find duplicates in Google Sheets?

Yes. Use Format > Conditional formatting with the custom formula =COUNTIF($A$2:$A,$A2)>1 to highlight repeated values. You can also open Data > Data cleanup > Cleanup suggestions to see whether Sheets flags duplicate rows.

How to delete duplicates in Google Sheets but keep one?

The Remove duplicates tool keeps the first matching row and deletes later ones, so you already keep one copy by default. To control which row stays, sort the data first, for example by newest date, or check only the column that defines a duplicate, such as email address.

Can I undo Remove duplicates in Google Sheets?

Yes, right after running it you can press Ctrl+Z (Cmd+Z on a Mac). If you have already made more edits, open File > Version history > See version history and restore an earlier version.

Why does Google Sheets say no duplicates were found?

The values probably differ in a hidden way, such as trailing spaces, non-breaking spaces, or numbers stored as text. Run Data > Data cleanup > Trim whitespace, standardize the values, confirm that the right columns are checked, and try again.

Working with a messy sheet that does not fit any of these scenarios? Add your question or describe your setup in the comments below, and I will do my best to point you in the right direction. If you would prefer to write to me directly, you can reach me through the Contact Us page.

Final Thoughts on Removing Duplicates in Google Sheets

Removing duplicates in Google Sheets does not have to be complicated. The built-in Remove Duplicates tool is ideal for quickly cleaning an existing dataset, while UNIQUE creates a separate list without changing your source information. Conditional formatting and COUNTIF provide additional control when you need to identify and review repeated entries before deleting anything.

Before choosing a method, decide which columns should define a duplicate and which record you want to retain. Most importantly, make a backup before removing rows from important spreadsheets. With the right approach, you can keep your data organized, accurate, and easier to work with.

How to Remove Duplicates in Google Sheets? - [4 Smart Ways]