Sishir Roy
When using Google Sheets, you could come across large spreadsheets with several duplicate entries. We understand how annoying it is to deal with duplicate entries and how difficult it would be to highlight and eliminate each entry individually. This article will demonstrate 5 effective ways to remove duplicates in a column in Google Sheets.
5 Fast Ways to Remove Duplicates in a Column in Google Sheets
The dataset below will be used to demonstrate examples of how to get rid of duplicates in a column in Google Sheets.
The dataset contains winners of the FIFA World Cup of the previous 40 years. Here, some countries won several times in these periods, and we want to remove those duplicates in Google Sheets.
1. Using Remove Duplicates Tool
In Google Sheets, removing duplicate records is so often done that a specific option has been created to do so. It is the easiest way to remove duplicates in a column in Google Sheets.
Steps:
- Select the column or a column range from where you want to delete the duplicates. We chose Cell C5:C14.
- Go to the Data from the top menu bar, select Data Cleanup and then select Remove Duplicates.
- The Remove duplicates dialog box will open, now click on the Remove Duplicates option.
- The Remove duplicates dialog box will show you how many duplicate rows have been found and removed. Click OK.
- All the duplicates have been removed from the column.
Read More: Remove Duplicates in Column on Different Sheets in Google Sheets
2. Applying UNIQUE Function
In Google Sheets, you can also use a function to remove duplicates in a column. The UNIQUE function can extract unique values from a range in Google Sheets.
Steps:
- Select a cell where you want the non-duplicate values. We selected Cell D5.
- Type the formula and put the range inside the function-
=UNIQUE(C5:C14)
- Now press Enter and you will get the desired result.
Read More: How to Remove Duplicates in Google Sheets Using Formula
3. Using Conditional Formatting
Another way to highlight and remove duplicates in a column in Google Sheets is to use conditional formatting. You can highlight and remove the duplicates in a column or multiple columns with this method.
Steps:
- Select the entire range from where you want to remove the duplicates. We selected the range C5:C14.
- Go to the Format tab from the top menu bar and select Conditional formatting.
- The Conditional format rules dialog box will open. Now click on the drop-down icon of the Format cells if.
- Now select the Custom formula is option from the appeared list.
- Now type the formula in the Formula bar and click Done. You may also choose the formatting style if you do not want the default formatting style.
=COUNTIF(C$5:C5,C5)>1
- All of the duplicates in the column are highlighted.
- Now you can easily remove the duplicates. You can simply select all the duplicate rows and press the Delete button.
- All of the duplicate rows are now removed from the dataset.
Read More: How to Remove Duplicates in Google Sheets Without Shifting Cells
4. Inserting Pivot Table
A pivot table can identify and remove duplicates by itself. First, you need to create a pivot table. Then the pivot table editor will help you to remove the duplicates in a column in Google Sheets.
Steps:
- First, select the dataset to create a pivot table.
- Now go to the Insert option from the top menu bar and select Pivot table.
- Now specify where to create the pivot table and click Create.
- The Pivot table editor dialog box will open. Now you should add rows in the pivot table. Click Add.
- Select the column from which you want to remove the duplicates.
- You can unselect the Show totals Now you should add values to the pivot table. Select Add and choose the Winner column.
- Choose COUNTA from the Summarize by option.
- It will count the number of each entity present in the column. Now you can remove the duplicates manually as discussed before.
5. Assigning Google Apps Script
To eliminate duplicates based on specified criteria, you may write a custom function in Google Apps Script. It is quite helpful when a sheet is constantly adding new values. Here, we used a custom function to delete entire duplicate rows starting from Cell A1.
Steps:
- Go to the Extensions tab from the top menu bar and select Apps Script.
- To remove the entire duplicate rows from the dataset, enter the following code-
function Remove_Duplicates_in_a_Column() {
var sheet = SpreadsheetApp.getActiveSheet();
var info = sheet.getDataRange().getValues();
var new_info = [];
for (var i in info) {
var row = info[i];
var duplicate = false;
for (var j in new_info) {
if (row.join() == new_info[j].join()) {
duplicate = true;
}
}
if (!duplicate) {
new_info.push(row);
}
}
sheet.clearContents();
sheet.getRange(1, 1, new_info.length, new_info[0].length).setValues(new_info);
}
- Click Save to save the project and then click Run to run the project.
- An Authorization required dialog box may open. Click on the Review permissions.
- Choose an account for authorization.
- Click on the Advanced.
- Click on the Go to Remove Duplicates in a Column (unsafe).
- Now, click on Allow.
- The project will successfully run and you will get your desired result in the current worksheet.
Conclusion
In this article, we have tried to demonstrate all possible techniques to remove duplicates from a column in Google Sheets. You can simply follow any method and get rid of duplicate entities. Please feel free to leave any questions or suggestions in the comments section below. Visit our site Officewheel.com to explore more.
