Fatema Rain
When you’re using Google Sheets to keep track of a contact list or sign-ups, sometimes the same name or email can show up more than once. It happens a lot and you can get rid of this situation easily. Imagine someone enters their email three times by accident. You definitely don’t want that messing up your list.
In this article, we will show you three easy ways to get rid of duplicates based on just one column, like email. And don’t worry, the rest of your data stays safe.
Steps to Remove Duplicate Rows Based on One Column Using the Remove Duplicates Tool:
➤ Select the dataset including the header row.
➤ Go to Data >> Data cleanup >> Remove duplicates.
➤ In the popup, check “Data has header row.”
➤ Select only the column you want to check for duplicates (e.g., “Email”).
➤ Click Remove duplicates to delete repeated rows based on that column.
Remove Duplicates in Google Sheet with the Built-In Tool
This is the fastest method and is ideal when you want to remove repeated rows directly from your original dataset.
We’re using a simple list of form submissions that shows each person’s name, email, company, and the date they filled it out. If you look at the email column, you’ll see that some addresses appear more than once. That’s what we’ll focus on as we go through how to clean up the list. Make sure your dataset has a specific column, like “Email” that you want to check for duplicates.
Steps:
➤ Select the entire dataset, including headers.
➤ Go to Data >> Data cleanup >> Remove duplicates.
➤ In the popup, check “Data has header row.”
➤ Check only the column you want to check duplicates for (e.g., “Email”).
➤ Click Remove duplicates.
Google Sheets will show a summary of how many rows were removed and how many unique rows remain.
Note:
This action is permanent, so consider making a copy of your data first if you want to keep the original.
Use the UNIQUE Function to Filter Duplicates in Google Sheet
If you want a dynamic list without permanently deleting anything, using UNIQUE function is a better solution here.
Steps:
➤ Choose an empty column where you’d like to place your cleaned list.
➤ Enter this formula (assuming column B has emails):
=UNIQUE(B2:B)
This will give you a list of unique values based on only one column.
To Show Full Rows Based on a Unique Value in One Column
If your full dataset is in the cell range of A2:D, and the column to filter duplicates by is column B (the first one), use:
=UNIQUE(A2:D)
Google Sheets will return one row per unique value in column A.
Use QUERY Function to Keep Only the First Instance in Google Sheets
The QUERY function gives you more control. It’s great for larger or more complex datasets.
Steps:
➤ Add a helper column by entering this formula in cell E2:
=IF(COUNTIF($B$2:B2, B2)=1, 1, 0)
➤ Drag down the cell from E2 to the bottom of the table to apply to all cells.
This marks the first occurrence of each name with a 1.
➤ Click on an empty cell and type this formula:
=QUERY(A1:E, “SELECT A, B, C, D WHERE E = 1”, 1)
This formula looks at column A to find repeats. That could be names, emails, or anything else you want to check. It shows only the rows where the value in column A is unique.
Note:
This method may not retain the exact first instance of the row unless the grouped rows are identical in other columns. Use with caution when non-key columns vary.
What If Duplicates Aren’t Being Removed in Google Sheets?
Sometimes, duplicates won’t go away even if they look identical. This is usually due to:
➤ Extra spaces (try =TRIM(cell)).
➤ Different capitalization (UPPER or LOWER can normalize).
➤ Hidden characters or formatting issues.
Fix these by cleaning your data using the TRIM and ARRAYFORMULA functions together:
=ARRAYFORMULA(TRIM(A2:A))
➤ The CLEAN function helps get rid of weird hidden characters. Just use =ARRAYFORMULA(CLEAN(B2:B)) to clean up the values in that column.
Frequently Asked Questions
How do I remove rows with duplicate values in just one column?
Just go to Data >> Remove Duplicates, then pick the one column you want to check. Or if you don’t want to delete anything, use the UNIQUE() formula to pull out only the different values.
Will UNIQUE remove entire duplicate rows or just values?
By default, UNIQUE() looks at whole rows—so if everything matches, it keeps only one. But if you only want it to check one column, just give it that column like =UNIQUE(A2:A). Easy and clean!
Can I keep the first occurrence only when removing duplicates?
Yep! Google Sheets is smart like that. When you use Remove Duplicates, it automatically keeps the first time it sees something and deletes the rest. The UNIQUE() function works the same; it saves the first and skips the copies.
Can I highlight duplicates without removing them?
Yes! Use conditional formatting:
➤ Select the column.
➤ Go to Format >> Conditional formatting.
Use this custom formula: =COUNTIF(A:A, A1) >> 1
➤ Choose a color to highlight duplicates.
Wrapping Up
Removing duplicates based on one column in Google Sheets is quick and easy once you know your options. Whether you prefer to use built-in tools or formulas like UNIQUE or QUERY, there’s a method that suits every workflow. Try them out on your own data and keep your sheets clean and accurate!
