Google Sheets offer a couple of methods you can use to remove duplicates in a sheet.
solve this problem. You can either use the Remove duplicates command or the UNIQUE function.
- An example of a worksheet that contains unique records
2 Ways to Remove Duplicates in Google Sheets
- Removing duplicates using the Remove duplicates command
- Removing duplicates using the UNIQUE function
Let’s get started!
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.
Step 1: Open your Google Sheet file.
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.
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.
Google Sheets’ duplicate removal features help make your data more consistent and accurate.
We hope you found this article helpful!