Find out how to Spotlight Duplicates in Google Sheets [Step-by-Step]

Duplicate information is the bane of spreadsheet options, particularly at scale. Given the quantity and number of information now entered by groups, it’s doable that duplicate information in instruments like Google Sheets could also be related and essential, or it may very well be a irritating distraction from the first objective of spreadsheet efforts.

The potential drawback raises a great query: How do you spotlight duplicates in Google Sheets?

We’ve acquired you coated with a step-by-step have a look at tips on how to spotlight duplicates in Google Sheets, full with photographs to be sure you’re heading in the right direction on the subject of de-duplicating your information.

Highlighting Duplicate Knowledge in Google Sheets

Google Sheets is a free, cloud-based different to proprietary spreadsheet packages and — no shock, because it’s Google we’re coping with — gives a bunch of nice options to assist streamline information entry, formatting, and calculations.

Google Sheets has all of the acquainted capabilities: File, Edit, View, Format, Knowledge, Instruments, and many others. and makes it simple to shortly enter your information, add formulation for calculations, and uncover key relationships. What Sheets doesn’t have, nevertheless, is a simple technique to discover and spotlight duplicates.

Whereas different spreadsheet instruments, comparable to Excel, have built-in conditional formatting instruments that may pinpoint duplicate information in your sheet, Google’s resolution requires slightly extra guide effort.

Step-by-Step: Find out how to Spotlight Duplicates in Google Sheets (With Photos)

So how do you routinely spotlight duplicates in Google Sheets? Whereas there’s no built-in instrument for this objective, you’ll be able to leverage some built-in capabilities to spotlight duplicate information.

Right here’s a step-by-step information:

Step 1: Open your spreadsheet.

Step 2: Spotlight the info you need to examine.

Step 3: Beneath “Format”, choose “Conditional Formatting.”

Step 4: Choose “Customized components is.”

Step 5: Enter the customized duplicate checking components.

Step 6: Click on “Carried out” to see the outcomes.

Step 1: Open your spreadsheet.

First, head to Google Sheets and open the spreadsheet you need to examine for duplicate information.

Step 2: Spotlight the info you need to examine.

Subsequent, left-click and drag your cursor over the info you need to examine to spotlight it.

Step 3: Beneath “Format”, choose “Conditional Formatting.”

Now, head to “Format” within the high menu row and choose “Conditional Formatting”. You could get a notification that claims “cell will not be empty” — in that case, click on on it, and it’s best to see this:

Step 4: Choose “Customized components is.”

Subsequent, we have to create a customized components. Beneath “Format cells if”, choose the drop-down menu and scroll all the way down to “Customized components is”.

Step 5: Enter the customized duplicate checking components.

To seek for duplicate information, we have to enter the customized duplicate checking components, which for our column of information seems to be like this:

=COUNTIF(A:A,A1)>1

This components searches for any textual content string that seems greater than as soon as in our chosen information set, and by default will spotlight it in inexperienced. If you happen to choose a distinct shade, click on on the small paint pot icon within the formatting type bar and choose the colour you favor.

Step 6: Click on “Carried out” to see the outcomes.

And voilà — we’ve highlighted the duplicate information in Google Sheets.

Find out how to Spotlight Duplicates in A number of Rows and Columns

If you happen to’ve acquired a bigger information set to examine, it’s additionally doable to spotlight information duplicates in a number of columns or rows.

This begins the identical means because the duplicate checking course of above — the one distinction is that you simply change the info vary to incorporate all of the cells you need to evaluate.

In follow, this implies getting into an expanded information vary within the Conditional format guidelines menu and the customized format field. Let’s use the instance above as a place to begin, however as an alternative of simply looking out column A for duplicates, we’re going to look throughout three columns: A, B, and C, and likewise throughout rows 1-10.

Once we enter our conditional format guidelines, Apply to Vary turns into A1:C10 and our customized components turns into:

=COUNTIF($A$2:G,Oblique(Tackle(Row(),Column(),)))>1

This can spotlight all duplicates throughout all three columns and all 10 rows, making it simple to identify information doppelgangers:

Dealing With Duplicates in Duplicates in Google Sheets

Are you able to spotlight duplicates in Google Sheets? Completely. Whereas the method takes extra effort than another spreadsheet options, it’s simple to duplicate when you’ve executed it a few times, and when you’re snug with the method you’ll be able to scale as much as discover duplicates throughout rows, columns, and even a lot bigger information units.