To Highlight and Remove Duplicates in Excel/Gsheet

To highlight duplicates in a single column:

1. Select a column.
2. For instance, select column A > Format > Conditional formatting.
3. Under Format rules, open the drop-down list and select Custom formula is.
4. Enter the Value for the custom formula, =countif(A1:A,A1)>1.
5. Below Format rules, you can find Formatting styles, which lets you set a different colour for highlighted duplicates. To do that, hit the Fill colour icon and select your preferred shade.
6. Once you’re finished, hit Done to highlight the duplicates in a single column.
7. Similarly, if you have to do it for column C, the formula becomes, =countif(C1:C,C1)>1 and you do it so on for other columns as well.

To highlight duplicates across multiple columns

1. Select multiple columns.
2. For instance, select columns from B to E > click Format > click Conditional formatting.
3. Under Format rules, open the drop-down list and select Custom formula is.
4. Enter the Value for the custom formula, =countif(B1:E,B1)>1.
5. Set a different colour for the highlighted duplicates by following the earlier steps if you wish to. Once you’re finished, hit Done.
6. Similarly, if you want to select the duplicates for column M to P, then you replace B1 with M1 and E with P. The new formula becomes, =countif(M1:P,M1)>1.
7. Besides, if you want to highlight duplicates for all the columns from A to Z, simply repeat the earlier steps and enter the Value for the custom formula, =countif(A1:Z,A1)>1.

To remove duplicates from a spreadsheet

1. Select a column from where you want to remove the duplicates.
2. Click Data > Remove duplicates.
3. You will now see a pop-up. Tick the box next to Data has the header now > click Remove duplicates > click Done.
4. You can repeat the steps for other columns as well.