In this article we’ll break down how you can find and remove the duplicates in a couple of different ways.
How to highlight duplicates in Google Sheets
Google Sheets does offer a way to identify and remove duplicate data contained in your spreadsheets, but when working through large data it is a good idea to start by highlighting the data you want to review to easily identify where duplicates are. This step should be used to highlight the specific column(s) that you want to review. Before you start, be sure you have the necessary Editor permissions to make any changes. That will always be the case for files you’ve created yourself. Also, ensure that the spreadsheet has all the data organised by columns with headings above each column.
Launch the Google Sheets file you want to reviewHighlight the cell or column you want to analyse the data of
If working through a large file, sort the text in ascending or descending alphabetical order.In the ‘Format’ tab, select ‘Conditional formatting.’A drop-down menu will appear on the right. Confirm it is the correct cell range (if not, you can customise it yourself)Select ‘Custom formula is’ under the ‘Format cells if…’ section.Enter (=countif(A:A,A1)>1) in the value formula, changing the 2nd and 3rd letters to match your chosen column. In this case it is, (A:C,C1).In the ‘formatting style’ section, select your chosen custom highlight colour and click ‘Done.’
The selected column should now appear with all duplicate text highlighted in your custom colour.
How to find duplicates using formulas
One way to find duplicates in Google Sheets is by using formulas. A ‘duplicate’ is a repeated word or phrase found in the selected column.
Optionally highlight the column you want to review (you can do this by using the above steps or by clicking the ‘fill colour’ tab)Click on an empty cell, preferably to the right near your data
Type the formula (=UNIQUE) in the cell, then add the cell coordinates where you noticed duplicates. In this case it is =UNIQUE (B11:B13).Click ‘Enter’ on your keyboard. All duplicate data should now appear in that cell/ column.
A duplicate found in the highlighted column will appear in the chosen cell in a fill colour, duplicates found from other columns will appear below. In this case ‘Before’ is the additional duplicate.
How to remove duplicates in Google Sheets
Once you’ve found duplicate data, you may want to remove it. Before completing the next steps, it’s advisable to save the original file and then save the edited file as a copy using a different name after duplicates have been removed, so that you can refer back to the original if you need to.
To remove duplicates, select the range of cells to checkIn the ‘Data’ tab, select ‘Remove duplicates’
Google Sheets will automatically delete the duplicates and show how many rows and columns were selected.
Once completed, save the file. Once you have repeated this for each ould now appear without any duplicates. See more information about tips and tricks when using Google Docs.
