Google Sheets is probably one of the best when it comes to organizing data in a spreadsheet. Apart from the multitude of features it has to offer, Google Sheets can also be accessed from any computer across the globe with your sign-in credentials.
One of the major deal-breakers when it comes to accessing information from sheets is ‘Duplicate Data’. It creates a barrier in understanding and making full use of the information available on the sheet. If you were to manually delete each duplicate entry, it would take forever in the case of large spreadsheets.
Google Sheet offers the option to highlight ‘Duplicate Data’ to avoid confusion, thus aiding the process of removing duplicate entries. You can highlight duplicate entries in a particular cell of a column or a complete row.
Highlighting Duplicate Cells in a Column
This is a fairly easy method and can be achieved through conditional formatting of the column.
Select the entries in a column you want to add conditional formatting to and then select ‘Format’ at the top.
Select ‘Conditional formatting’ from the drop-down menu.
If you already have a conditional formatting applied to the sheet, click on ‘Add another rule’ at the bottom.
Check if the ‘Apply to range’ mentions the correct set of cells that you have selected. Next, click on the box under ‘Format cells if’ to select an option.
Scroll to the bottom of the list and select ‘Custom formula is’, which is the last option.
To highlight duplicate cells, we will use the countif function. This checks if any entry is repeated more than once and then highlights them all.
=countif(Range,Criteria)>1A user has to make necessary adjustments to the formula as per the available data in the sheet. In the example that we are discussing, we will go with the following formula.
=countif($A$2:$A$9,A2)>1Paste this formula in the text box under ‘Custom formula is’, select a color for the highlighted cell using the ‘Fill color’ option and then click on ‘Done’.
The duplicate entries have been highlighted with the color selected while doing ‘Conditional Formatting’.
Highlighting Complete Row
When you are working on large spreadsheets with multiple rows and columns and inter-related data, highlighting individual cells will not do the job. In this case, you will have to highlight complete rows since the duplicate cell might not be visible on the screen. The formula for this is very similar to the one used above with some minor modifications.
This method will only look for duplicates cells in a single column but highlight the complete row instead of the individual cell.
Select all the cells(rows and columns) where you want to apply the conditional formatting.
Next, open ‘Conditional Formatting’ from the ‘Format’ menu as you did earlier. Delete the formatting that we did earlier and click on ‘Add another rule’ at the bottom. Now, check the ‘Apply to range’, if it displays all the cells that you have highlighted. Next, select ‘Custom formula is’ and then enter the following formula in the box underneath.
=countif($A$2:$A$9,$A2)>1The only modification made in the formula from the earlier case is adding ‘$’ in ‘A2’ since we require the absolute value for the column.
Once you are done, click on ‘Done’ at the bottom to apply the formatting.
Since we were looking for duplicate entries in column A, the others have not been taken into consideration. This method is just an extension of the one we discussed earlier where instead of the cell; the whole row is highlighted.
You can similarly check duplicate entries in other columns as well by modifying the formula accordingly.
Removing Duplicate Data in a Google Sheet
This method comes handy when you have to remove duplicate entries from a sheet. Manually removing each of them will consume a lot more time and effort which can easily be saved. Google Sheets has a built-in feature to identify and remove the duplicate data on a spreadsheet.
Using the ‘Remove Duplicates’ feature, you can easily remove complete identical rows or individual cells by selecting the appropriate options.
Select the cells that you want examined and duplicate entries removed, and then click on ‘Data’ menu at the top.
Next, select ‘Remove duplicates’ from the drop-down menu.
Tick/untick the check boxes before each of the options as per your preference and requirement and then click on ‘Remove duplicates’ at the bottom to delete them. If you want to check duplicate rows, tick the checkbox for ‘Select all’. In case you want to identify duplicate entries in individual cells, tick the checkbox for that particular column.
Google Sheets will inform you of the number of entries that were removed and the number that remains. Finally, click on ‘OK’ to close the box.
Highlighting and removing duplicate cells and rows wouldn’t be much of a task now. Moreover, it will ensure that you can focus on the part that requires attention rather than being engaged with duplicate entries. Duplicate data leads not only to confusion and error but can have serious repercussions when dealing with important data.
You should have a fair knowledge of the various functions, formulas and their modification to highlight duplicate entries. Once you know the stuff, you can modify the formula for other columns as well, thus saving considerable time.
Member discussion