When working with Google Sheets you can come across some large spreadsheets where you have to go through various duplicate entries. It is always very annoying experience to deal with the duplicate ones. It is very difficult to highlight and remove the entries one by one. But with the help of conditional formatting, highlighting as well as removing the duplicates can become very easy. In order to know how you can find and remove duplicate entries in Google Sheets you need to follow this tutorial.
How to highlight duplicates in a single column
In order to highlight the duplicates in a single column you need to follow these steps.
- First of all, open your spreadsheet in Google Sheets and then select a column.
- For example, you can select column A>Format>Conditional formatting.
- Now under the Format rules, open the drop-down list and then select Custom formula is.
- Now enter the value for the custom formula, =countif(A1:A,A1)>1.
- You will find the Formatting styles below Format rules which allows you to set a different colour for highlighted duplicates. In order to do that hit Fill color icon and then select your preferred shade.
- Once you are done, hit Done to highlight the duplicates in single column.
- If you need to do it for column C, the formula becomes, =countif(C1:C, C1)>1 and you can do it so on for other columns as well.
You can also find the duplicates in the middle of the columns as well. In order to learn how to do it you need to follow these steps.
- Well let us assume that you need to highlight duplicate between cells C5 to C14.
- In this case you need to go to Format and then select Conditional Formatting.
- Now under Apply to range you need to enter the data range, C5:C14.
- Under Format rules, open the drop down list and then select Custom formula is.
- Now enter the value for the custom formula, =countif(C5:C, C5)>1.
- Now set a different colour for highlighted duplicates by following earlier steps if you wish to. Once you are done, hit Done.
How to highlights duplicates across multiple columns
If you need to highlight duplicates across multiple columns and rows you are required to follow these steps.
- First of all, you need to open your spreadsheet in Google Sheets and then select multiple columns.
- For example, select columns from B to E > click Format >click Conditional formatting.
- Under the Format rules, open the drop-down list and select Custom formula is.
- Now enter the value for the custom formula, =countif(B1:E, B1)>1.
- Now set a different colour for the highlighted duplicates by following the mentioned earlier. Once you are done, hit Done.
How to Remove duplicated from a spreadsheet
Once you are done highlighting the duplicate entries in your spreadsheet then you are required to delete them. In order to do that follow the steps mentioned below.
- First of all, select a column from where you need to remove the duplicates.
- Now click Data > Remove duplicates.
- After this you will see a pop-up. Tick the box next to Data has header now > click Remove duplicates > click Done.
- You can repeat these steps for other columns as well.
By following the above mentioned steps you can highlight as well as remove the duplicates in Google Sheets.