Working with spreadsheets, especially with large sets of data, can sometimes be confusing.
When you already spent a lot of screen time encoding on your worksheets, there is a possibility that you have encoded duplicate entries. Of course, you can always go over your worksheets and check all your entries to correct them, but this can be a bothersome task.
Google Sheets offers some methods you can use to solve this problem. You can either use the Remove duplicates command or the UNIQUE function.
Familiarize yourself about these methods by finishing this article.
- An example of a worksheet that contains unique records
Now, there are two methods you can use to remove duplicates in Google Sheets.
2 Ways to Remove Duplicates in Google Sheets
- Removing duplicates using the Remove duplicates command
- Removing duplicates using the UNIQUE function
Before you proceed with the steps, it is important to understand when to use each method. You can use each of these methods in certain situations. Use the Remove duplicates command if you intend to remove all duplicate entries in a cell range. Suppose you need to identify the unique records in a cell range without removing the original records, you can use the UNIQUE function. As you read this article, you will learn some of the practical applications of these methods.
Let’s get started!
Why Should You Remove the Duplicates in Google Sheets?
One of the prime reasons why there is a need to remove duplicates is to ensure data integrity. Having a lot of duplicate data in your records can cause negative impacts to your work.
For instance, you are a business owner and you kept some of your transaction records in Google Sheets. If you fail to check duplicate entries in your records, it might later on cost your business to lose a significant amount of money or other resources.
Preventing duplicate entries is a step towards consistency and accuracy of data.
Below are some good rules of thumb that guide you when to use the duplicate removal features of Google Sheets.
- You want to identify the unique records in your worksheet.
- You need to create a new data set having only the unique records of an existing data set.
These are the most common instances wherein you can consider using either of the methods stated earlier. In the next section, you will learn the steps needed in each method. As you learn these steps, you will eventually realize other situations that require each method.
It’s time to learn the methods!
Method 1: Removing the Duplicates Using the Remove Duplicates Command
You can use this method to remove the duplicate entries within a cell range. Keep in mind that if you use this method, the changes will replace your selection. This is best applied when you do not need to keep the old set of data.
Here are the steps on how to use this.
Step 1: Open your Google Sheet file.
Start by opening the spreadsheet you want to modify. Locate the file in your drive and open it using Google Sheets. Below is an example of a spreadsheet opened in Google Sheets. Follow along with this tutorial by opening any of your spreadsheets that contain duplicate records.
Step 2: Highlight the cell range that you need to format.
From there, you’ll want to highlight or select the cells that you want to format. To do that, Google Sheets needs to know the cell range that you want duplicates removed from. Highlight the cells that you want to format by clicking and dragging over them. In the example below, A1:C14 is the selected cell range.
Step 3: Open the Remove duplicates dialog box.
Once you’ve done that, you’ll need to go over some settings and windows. Go ahead and locate the settings under the Remove duplicates dialog box to finish the operation. To display the dialog box, click the Data tab located on the menu. This will display a drop-down list of options. From the list, move your mouse down and click the Remove duplicates option. The dialog box will appear upon doing this.
Step 4: Configure the Remove duplicates dialog box.
Notice the settings under the Remove duplicates dialog box. Set the columns that you will include by ticking the respective checkboxes. You can select all columns or include only some of them. This will depend on how you would like Google Sheets to analyze your selection. If you included the column names in your selection, make sure that you tick the Data has header row checkbox.
Step 5: Finalize your settings and remove the duplicates.
Finish the process by clicking the Remove duplicates button. A message box will appear informing you about the number of duplicate rows found and removed. You will also get informed on the number of unique rows that remain after the operation.
Method 2: Removing Duplicates Using the UNIQUE Function
Google Sheets also offers a function that allows you to create a new set of data containing only the unique values from an existing dataset. This is possible by using the UNIQUE function. This is best applied if you want to keep the old set of data while creating a new one.
Here are the steps on how you can do it.
Step 1: Locate and open your Google Sheet file.
Like the first method, you will need to open your file to edit it. Search for your spreadsheet and open it in Google Sheets. A sample spreadsheet is being shown below to guide you with this method. You can follow along by using any of your spreadsheets that contain duplicates.
Step 2: Determine where to create a new dataset in your spreadsheet.
As mentioned earlier, this method will create a new set of data. In your spreadsheet, identify where you would like to add the new dataset. It can be on the same worksheet with your reference dataset or in another worksheet. This will depend on your preference. Always bear in mind that the number of columns that will contain the new dataset will be equivalent to the number of columns you will select later on. Once identified, click on the cell which will contain the first column. In the example below, cell E1 is selected.
Step 3: Use the UNIQUE function.
Now that you have identified and selected where to add the new data, it is time to use the UNIQUE function. With the cell selected, type in “=UNIQUE()”. As you type in, you may notice that a tooltip appears below it. You may opt to click the suggested function or finish typing it. Complete the function by specifying the range of the reference dataset inside the parentheses. To do so, first, you need to place your mouse cursor inside the parentheses. Specify the reference either by clicking and dragging over the reference dataset or simply typing the cell range. In the example below, the selected cell range is A1:C14.
Step 4: Finalize the function.
With the syntax of the function already complete, it is time to apply it. Press the Enter key on your keyboard. A new dataset will appear on where you used the function. This contains only the unique values of your reference dataset.
Now that you learned the different methods on how you can remove duplicates in Google Sheets, it is time to apply them to your own spreadsheets.
Google Sheets’ duplicate removal features help make your data more consistent and accurate.
You no longer need to worry about having duplicate entries in your spreadsheets using the methods discussed in this article. Use these features as often as possible, especially when you are working on large sets of data.
This is one of the most effective solutions to problems brought by duplicate entries.
We hope you found this article helpful!