Saturday, May 25, 2024
HomeTutorialsHow To Remove Duplicates in Google Sheets

How To Remove Duplicates in Google Sheets

Let’s assume you have a bunch of email addresses to make any documents in your Google sheet. And you would like to know how many emails address you have in total right now, but there might be a possibility of getting the same address more than once. So, to get the actual number of data, you need to find and remove duplicates in Google sheets so that your workflow won’t hamper by any means.

However, it’s very obvious of having duplicates in time working with any large number of data. Either it can be human error or your system. But, to complete your work effectively without any error, you have to find those duplicates and remove them from your sheets accordingly.

To the entire article, you’ll walk through how to avoid these duplicates and complete your job easily. Well, Google Sheets now has a built-in feature that helps you remove¬†duplicate entries. If that doesn’t work in any case, I, therefore, will show you some more refined alternatives to removing duplicates from Google Sheets.

Remove Duplicates in Google Sheets

There are several ways by which you can easily find and remove duplicates in Google sheets. Here are the 2 easiest and most well-known ways (using a built-in feature, formulas) of getting rid of duplicate issues. So, let’s see one-by-one, don’t skip a word here, especially if you’re someone who has to use Google sheets daily.

Method 1: Find and Remove Duplicates in Google Sheets Using the Built-In Feature

Well, I suppose you’ve your Google sheets open with data. Getting rid of duplicates from Google sheets using a built-in feature, hit on the highlight Data section. Now, you’ll find Data cleanup and finally Remove duplicates after clicking on it.


After clicking on the Remove duplicates, you’ll find the below-resulting page. And from here, you’ve to select the range you work with. If the data has a header row, then select accordingly.


Once you select all the things according to your needs, hit the remove duplicates. By doing so, you’ll get to know how many rows were found as duplicated and already removed as well as how many unique rows remain which you’re searching for this long.


Method 2: Find and Remove Duplicates in Google Sheets Using a Formula

Using any formula is always preferable for doing these kinds of stuff. However, let’s assume you want to keep all your data and also find the duplicates. Yes, a formula can do that for you. A formula called Unique helps you to find the data that are unique and keep the old data as it is.

1. Remove Duplicates from within a Single Column

Well, let’s assume we want to find out only the unique email addresses from Sheet1. And for doing so, you need to follow some easy steps. Click into the cell at the top left of the sheet. (What if you like to have your data somewhere else? For doing so, you need to make sure enough space there below and to the right of the cell to avoid the formula overwrite.)

Step 1: At first, select the place where you would like to have your unique data – which is the data after removing the duplicates. Let’s create a new sheets to get this done. In my demo, I’ve created Sheet3.

Now go to the top left of the sheet and click into the cell. Please ensure you have enough space below and to the right of the cell which you select if you choose to put the data elsewhere. The formula will overwrite anything already in the cell.

Step 2: Let’s just type Type =UNIQUE( into the bar, once you type this, the exact formula will appear automatically for you.


Step 3: Now, return to your data sheet (Sheet 1). Simply click on the column heading above that you want to remove duplicates from (in this case, B) to select it. As you can see, it is automatically added to the range through the formula.


To complete the formula, enter a parenthesis at the end, ). You will end up with a formula looking like this:


Step 4: In the cell where you entered the formula, you will see the unique records from the selected column.

Step 5: Make sure one thing, if you copy/paste into another place in your google sheets, you need to follow instructions. And that is, choose Edit and then Paste special and finally Paste values only. If not, you will copy the formula rather than the results.

2. Remove Duplicate Rows from within a Sheets

It is similar to removing duplicate rows, except that you choose the range of cells you want to remove. To remove duplicate rows, repeat the steps above, but for Step 3, select the rows you wish to remove. And to delete duplicate entries, highlight rows 1-26 of Sheet1 in this example spreadsheet.


Note: If there are specific rows that aren’t next to each other, select each row with the command button for Mac or the ctrl key for Windows.

Over To You

Well, to conclude things up, it’s very clear that anyone will be able to remove duplicates in Google sheets very easily. Therefore, you just need to follow any one of the above-explained methods. However, it’s a no script-required process. So, find and delete duplicates from your Google sheet easily.

Well, it’s time to take a leave for today. Hope to see you again at any other tip of the google sheets. I hope this will help you a lot. So, if you find the article helpful, then please let me know your feedback in the comment box below. And don’t forget to share with your close ones.

Last but not least, if you’re familiar with any other process to get rid of duplicates from your Goggle sheets, please let me know as well. That would also be appreciative a lot. Be with UbuntuPit and keep yourself updated with the latest tips and tricks.

Mehedi Hasan
Mehedi Hasan
Mehedi Hasan is a passionate enthusiast for technology. He admires all things tech and loves to help others understand the fundamentals of Linux, servers, networking, and computer security in an understandable way without overwhelming beginners. His articles are carefully crafted with this goal in mind - making complex topics more accessible.


Please enter your comment!
Please enter your name here

You May Like It!

Trending Now