This article explains how to find and remove duplicate entries in Google Sheets using built-in functions or third-party add-ons.
What to Know
- Remove Duplicates: Select cell range/columns to search > Data > Data Cleanup > Remove Duplicates.UNIQUE function: Select first cell in column for unique returns > Insert > Function > Filter > Unique.Next: Select search range > press Enter > Sheets will create new column/table of unique entries.
Find Duplicates in Google Sheets Using the Remove Duplicates Tool
Google Sheets includes a built-in tool that will search the sheet based on the criteria you provide.
- Open the spreadsheet in which you want to find and remove duplicate data.
- Select the data range in which you’d like to remove duplicates.
- Cells with identical values, but different formatting, formulas, or letter cases are considered duplicates.
- Select Data > Data Cleanup > Remove Duplicates. The Remove Duplicates dialog box will open showing the number of columns and rows selected.
- Select the Data has header row checkbox if your spreadsheet includes a header row to prevent this row from being affected.
- Choose the columns you want the tool to analyze by selecting the corresponding checkboxes. To analyze all columns, select the Select All checkbox.
- If you select a single cell within a range, duplicates will be removed for the whole range.
- Select Remove Duplicates. The tool will analyze the data, and a box will appear showing how many duplicate rows Google Sheets found and removed.
Find Duplicates in Google Sheets Using the UNIQUE Function
The UNIQUE function in Google Sheets returns unique rows from the provided source range, discarding duplicates. The UNIQUE function returns rows in the order they first appear in the source range. Using this method, you are able to view and compare both sets of data before deleting the original duplicates.
When you use the Remove Duplicates tool, it’ll automatically remove the duplicates it finds. You won’t have the opportunity to view them first. If you prefer to review them before Google Sheets removes them, you may want to try highlighting duplicates in Google Sheets instead.
Open the spreadsheet in which you want to find and remove duplicate data.
Select the data range in which you’d like to remove duplicates.
Cells with identical values, but different formatting, formulas, or letter cases are considered duplicates.
Select Data > Data Cleanup > Remove Duplicates. The Remove Duplicates dialog box will open showing the number of columns and rows selected.
Select the Data has header row checkbox if your spreadsheet includes a header row to prevent this row from being affected.
Choose the columns you want the tool to analyze by selecting the corresponding checkboxes. To analyze all columns, select the Select All checkbox.
If you select a single cell within a range, duplicates will be removed for the whole range.
Select Remove Duplicates. The tool will analyze the data, and a box will appear showing how many duplicate rows Google Sheets found and removed.
- Select the first cell in the column in which you want to unique rows returned.
- Select Insert > Function.
- Select Filter > Unique.
- Enter or select the range you want to use as the argument for the function and press Enter.
- Google Sheets will create a new column or table with only unique rows included.
- If returned rows appear to include duplicates, look for cells that have hidden text or other differences, such as trailing spaces.
Find and Remove Duplicates in Google Sheets Using an Add-on
Add-ons are tools created by third-party developers that improve or simplify your experience using Google Sheets. Add-ons are available that will find and remove duplicates from your spreadsheet in Google Sheets.
Ensure numeric values are formatted in the same way, such as percentages or currency values.
Select the first cell in the column in which you want to unique rows returned.
Select Insert > Function.
Select Filter > Unique.
Enter or select the range you want to use as the argument for the function and press Enter.
Google Sheets will create a new column or table with only unique rows included.
If returned rows appear to include duplicates, look for cells that have hidden text or other differences, such as trailing spaces.
While no two add-ons are identical, many include similar features. Learn how to locate add-ons that find and remove duplicates in Google Sheets and how to decide which one best suits your needs.
- Go to Google Sheets.
- Select Add-ons > Get Add-ons. The Add-ons window will open with several suggested tools.
- Type “remove duplicates” into the Search add-ons field and press Enter. A list of potential add-ons matching your search will appear.
- Select an add-on you want to learn more about to open the description page. Scroll down to read reviews or scroll right and left to view screenshots and videos displaying the add-on in action.
- Select Add.
- The Add button might have a price or the word “Free” on it.
- Select or sign in to your Google account if prompted.
- Select Allow to provide the requested permissions to the add-on.
- Wait while the add-on is installed.
- Select Add-ons to access and use the tool.
- Select Help in the add-on menu to get help using the tool you have downloaded.
Go to Google Sheets.
Select Add-ons > Get Add-ons. The Add-ons window will open with several suggested tools.
Type “remove duplicates” into the Search add-ons field and press Enter. A list of potential add-ons matching your search will appear.
Select an add-on you want to learn more about to open the description page. Scroll down to read reviews or scroll right and left to view screenshots and videos displaying the add-on in action.
Select Add.
The Add button might have a price or the word “Free” on it.
Select or sign in to your Google account if prompted.
Select Allow to provide the requested permissions to the add-on.
Wait while the add-on is installed.
Select Add-ons to access and use the tool.
Select Help in the add-on menu to get help using the tool you have downloaded.
Get the Latest Tech News Delivered Every Day
