It’s common to find duplicate data when working in Google Sheets. It can be names, email addresses, product IDs, or survey answers. Your data can become disorganized and difficult to handle due to these duplications. Google Sheets has a built-in feature that allows you to remove duplicates with a few clicks.
Removing Duplicates in Google Sheets Using a Formula
You can remove duplicates without removing the original data by using the UNIQUE function. This function checks the list and displays only the first instance of each value. When applied to a column or range, it identifies duplicates and removes them. To explore how you can easily remove duplicates using a formula, check out this guide on removing duplicates with a formula. Or you can follow the process in short here with simple steps:
➤ Click on cell B2 and type this formula:
➤ Press Enter and see the result.
Removing Duplicate Rows Based on One Column in Google Sheets
It is quite a simple task to remove duplicates based on a single column in Google Sheets by using a combined formula. In Google Sheets, you can remove duplicates from a column by combining FILTER, MATCH, and ROW functions. MATCH identifies the first occurrence of each value, ROW helps track the position of each row, and FILTER uses this information to display only the unique entries based on the single column.
➤ Click on cell C2 and type this formula:
➤ Press Enter and see the result.
Removing Duplicates Without Shifting Cells in Google Sheets
Google Sheets does not move cells, only the duplicate values are removed. Let’s look at the dataset below, where you have two columns.
➤ Select the cell range A1:B6
➤ Click on the menu:
Data > Data cleanup > Remove duplicates
➤ A pop-up window will appear.
➤ Check “Data has header row” if row 1 has headers
➤ Click Remove duplicates.
➤ A pop-up message will appear. Click OK.
➤ See the result. There is no cell shifting.
Frequently Asked Questions
Does Google Sheets eliminate the initial or final duplicate?
The first duplicate value is always kept by Google Sheets, while the last duplicate values are removed.
How can I undo the duplicate removal process?
You can undo the duplicate removal process by pressing Ctrl + Z (Windows) or Cmd + Z (Mac).
Concluding Words
In Google Sheets, removing duplicates is an easy but effective method to maintain accurate, well-organized, and clean data. Removing duplicate entries helps prevent confusion and error while working with names, emails, product listings, or sales data.