Google Sheets: How to Highlight and Remove Duplicates

While working in Google Sheets, it’s possible you’ll come throughout giant spreadsheets the place you’ve got to cope with many duplicate entries. We perceive the ache of coping with duplicates and how tough it might get in case you have been to spotlight and take away entries one after the other. However, with the assistance of conditional formatting, highlighting and eradicating duplicates turns into fairly straightforward. Follow this information as we let you know how to discover and take away duplicate entries in Google Sheets.

Google Sheets: How to spotlight duplicates in a single column

Before figuring out how to take away duplicate entries from Google Sheets, let’s find out how to spotlight duplicates in a single column. Follow these steps.

1. Open your spreadsheet in Google Sheets and choose a column.
2. For occasion, choose column A > Format > Conditional formatting.
3. Under Format guidelines, open the drop-down listing and choose Custom formulation is.
4. Enter the Value for the customized formulation, =countif(A1:A,A1)>1.
5. Below Format guidelines, yow will discover Formatting kinds, which helps you to set a distinct color for highlighted duplicates. To try this, hit the Fill color icon and choose your most well-liked shade.
6. Once you are completed, hit Done to spotlight the duplicates in a single column.
7. Similarly, when you have to do it for column C, the formulation turns into, =countif(C1:C,C1)>1 and you do it so on for different columns as nicely.

Besides, there is a method to discover duplicates in the course of the columns too. To study, comply with these steps.

1. Let’s assume you would like to spotlight duplicates between cells C5 to C14.
2. In that case, go to Format and choose Conditional Formatting.
3. Under Apply to vary, enter the info vary, C5:C14.
4. Next, beneath Format guidelines, open the drop-down listing and choose Custom formulation is.
5. Enter the Value for the customized formulation, =countif(C5:C,C5)>1.
6. Set a distinct color for the highlighted duplicates by following the sooner steps if you want to. Once you are completed, hit Done.
7. Set a distinct color for the highlighted duplicates by following the sooner steps if you want to. Once you are completed, hit Done.

Google Sheets: How to spotlight duplicates throughout a number of columns

Just in case if you’d like to spotlight duplicates throughout a number of columns and rows, comply with these steps.

1. Open your spreadsheet in Google Sheets and choose a number of columns.
2. For occasion, choose columns from B to E > click on Format > click on Conditional formatting.
3. Under Format guidelines, open the drop-down listing and choose Custom formulation is.
4. Enter the Value for the customized formulation, =countif(B1:E,B1)>1.
5. Set a distinct color for the highlighted duplicates by following the sooner steps if you want to. Once you are completed, hit Done.
6. Similarly, if you’d like to choose the duplicates for column M to P, you then exchange B1 with M1 and E with P. The new formulation turns into, =countif(M1:P,M1)>1.
7. Besides, if you’d like to spotlight duplicates for all of the columns from A to Z, merely repeat the sooner steps and enter the Value for the customized formulation, =countif(A1:Z,A1)>1.

Google Sheets: Remove duplicates from a spreadsheet

After you are executed highlighting duplicate entries in your spreadsheet, the following step is to delete them. Follow these steps.

1. Select a column from the place you need to take away the duplicates.
2. Click Data > Remove duplicates.
3. You will now see a pop-up. Tick the field subsequent to Data has header now > click on Remove duplicates > click on Done.
4. You can repeat the steps for different columns as nicely.

This is how one can spotlight and take away duplicates in Google Sheets.

For extra tutorials, go to our How to part.

Are iPhone 12 mini, HomePod mini the Perfect Apple Devices for India? We mentioned this on Orbital, our weekly expertise podcast, which you’ll be able to subscribe to by way of Apple Podcasts, Google Podcasts, or RSS, obtain the episode, or simply hit the play button under.