How to remove repeated data in google sheets- Spreadsheet users are increasingly choosing Google Sheets. In comparison to other spreadsheet apps, Google Sheets has the easiest collaboration features.
The simplicity of use of Google Sheets is another factor contributing to its popularity. Its developers are always introducing new features and capabilities to make tasks simple and quick.
I’ll go through a few techniques in this guide for eliminating duplicates in Google Sheets.
Imagine the following: you have imported data from your database to Google Sheets to create a report. While scanning through the entries, you spotted that some of them are duplicated like this:
Would you manually delete the redundant rows? This could be a possibility, however if your data collection is huge enough, automating the elimination is a preferable alternative. Using native capabilities, formulae, or complex workarounds, we’ll show you every method for deleting duplicates in this tutorial.
The simplest method on How to remove repeated data in google sheets
It’s not necessary to manually remove each duplicate row because Google Sheets can take care of this for you in just two simple steps:
- Choose Remove duplicates from the Data menu.
- Tap Remove Duplication after selecting the columns to be checked for duplicates.
- For example, you wish to get rid of entries that contain the same name or date twice. You must only choose these columns in this scenario. We wish to completely remove the duplicate entries in our case. Therefore, we click Remove duplicates after selecting all columns.
- The duplicate rows have been effectively removed the following instant.
- Additionally, this capability will perform with bigger data sets. The fact that you must manually repeat it each time you add a new entry to the data collection is its biggest downside. What happens if you add more than 50 entries and are unsure whether any of them are duplicates? The automated methods for locating and deleting duplicate entries are listed below.
Reasons for Remove duplicates in Google Sheets
Users often need to achieve one of the following objectives:
- Get rid of duplicates in a single column.
- Get rid of duplicates in several columns.
How to delete Duplicates Using the ‘Remove Duplicates’ Tool
In Google Sheets, removing duplicate records has become such a standard practise that a dedicated option has been created.
Let’s say you want to get rid of all the duplicate records in the dataset that is displayed below.
The steps to delete duplicates from a dataset in Google Sheets are as follows:
- Decide the dataset you wish to purge the duplicate records from.
- Select Data from the drop-down menu.
- Select “Remove Duplicates” from the menu.
- Make sure ‘Data contains header row’ is selected in the Remove Duplicates dialogue box (in case your data has the header row).
- Ensure that “Select All” is chosen under “Columns to Analyze.”
- The “Remove Duplicates” button should be clicked.
The aforementioned actions would immediately eliminate any duplicate records from the dataset, giving you the outcome displayed below.
The data surrounding the duplicate records won’t be affected when you utilise the ‘Remove Duplicates’ option to remove them. This indicates that utilising it doesn’t erase any cells or remove any rows. It only purges the cells’ duplicate records (without disturbing cells around the dataset)
Utilize the UNIQUE function to remove duplicates.
Additionally, Google Sheets provides a tool that allows you to maintain only the unique data and eliminate duplicates.
That function is called UNIQUE.
Let’s say you have the dataset depicted below and want to get rid of all the duplicate records in it:
You may retrieve all the unique records by using the formula below, which will eliminate all the duplicate records:
The outcome of the aforementioned formula will be shown beginning in the place where the formula was typed.
You cannot delete or alter any of the unique records that make up the result, which is an array. If you attempt to erase even one cell from the result, nothing will happen. Additionally, if any cell is overwritten, the entire result will disappear and a #REF! error will appear.
Using the UNIQUE function has certain drawbacks, including the fact that it only flags records as duplicates when the full row content is repeated. If you only want to maintain one instance of a country name and remove the others, using UNIQUE will only work if all of the other column values for that record are identical.
If your data contains additional, leading, or following spaces, the unique function will treat the records differently. In this situation, you may apply the following formula:
Remove Duplicate using an Add-on
In general, Google Sheets has a vast library of several add-ons to address every issue or problem that can arise.
All of the add-ons for duplicate elimination may be used for the same thing. One of the greatest add-ons for removing duplicate entries from your dataset is Remove Duplicates by AbleBits.
You must first add an add-in to your Google Sheets document before you can utilise it.
The procedures for adding an add-on to your Google Sheets document are as follows:
- Open the Google Sheets file where the duplicates need to be eliminated.
- Go to the “Add-ons” tab.
- Select “Get add-ons” Select “Get Add-ons”
- Search for “Remove Duplicate” in the top-right section of the Add-ons dialogue box that appears.
- Select the “Remove Duplicate” add-on from the list of available add-ons.
- Select the blue Install button on the screen for the Remove Duplicates add-on.
- You might be prompted to sign into your Gmail account in the dialogue box that follows in order to authenticate your account. After entering your credentials, press the blue “Allow” button.
- The Remove Duplicate add-on has now been added to your Google Sheets document, and you may use it.
The following steps will show you how to use this add-on to remove duplicate records from Google Sheets:
- Choose the dataset from which you wish to eliminate the duplicates.
- Select Add-ons from the menu by clicking it.
- The “Remove Duplicates” option should be highlighted.
- To find duplicate or unique rows, click the link. This may take a few seconds to launch the “Find duplicate or unique rows” dialogue box.
- Ensure sure the appropriate range is chosen. The option to make a backup copy of the Google Sheets document may also be selected.
- Make sure Duplicates is chosen.
- Indicate whether or not your data contains a header and if you wish to skip empty cells.
- ‘Delete rows inside selection’ should be chosen. This will remove the duplicate records.
- Select Finish.
- How to Remove a excel sheet Password
- How to do Conditional Formatting in Google Sheets
- What is google chat and is it safe
The aforementioned processes would immediately eliminate the duplicate records, leaving you solely with the unique records.
Use Google Sheets’ built-in feature whenever feasible since it offers a convenient way to eliminate duplicates. The add-on is only useful when you wish to accomplish much more than merely deleting duplicates.
Additionally, this add-on may do the following:
- Duplicate records should be colored.
- Create a column and indicate which records are duplicates.
- Comparing sheets or columns