How you can Spotlight Duplicates in Google Sheets [Step-by-Step]

Duplicate knowledge is the bane of spreadsheet options, particularly at scale. Given the quantity and number of knowledge now entered by groups, it’s attainable that duplicate knowledge in instruments like Google Sheets could also be related and obligatory, or it might be a irritating distraction from the first goal of spreadsheet efforts.

The potential downside raises an excellent query: How do you spotlight duplicates in Google Sheets?

We’ve acquired you lined with a step-by-step take a look at the best way to spotlight duplicates in Google Sheets, full with pictures to be sure you’re heading in the right direction in terms of de-duplicating your knowledge.

Highlighting Duplicate Information 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 knowledge entry, formatting, and calculations.

Google Sheets has all of the acquainted capabilities: File, Edit, View, Format, Information, Instruments, and so forth. and makes it simple to rapidly enter your knowledge, add formulation for calculations, and uncover key relationships. What Sheets doesn’t have, nonetheless, is a straightforward solution to discover and spotlight duplicates.

Whereas different spreadsheet instruments, resembling Excel, have built-in conditional formatting instruments that may pinpoint duplicate knowledge in your sheet, Google’s resolution requires a little bit extra handbook effort.

Step-by-Step: How you can Spotlight Duplicates in Google Sheets (With Photos)

So how do you mechanically spotlight duplicates in Google Sheets? Whereas there’s no built-in instrument for this goal, you possibly can leverage some built-in capabilities to focus on duplicate knowledge.

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

Step 1: Open your spreadsheet.

Step 2: Spotlight the information you need to verify.

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

Step 4: Choose “Customized method is.”

Step 5: Enter the customized duplicate checking method.

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

Step 1: Open your spreadsheet.

First, head to Google Sheets and open the spreadsheet you need to verify for duplicate knowledge.

Step 2: Spotlight the information you need to verify.

Subsequent, left-click and drag your cursor over the information you need to verify to focus on it.

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

Now, head to “Format” within the prime menu row and choose “Conditional Formatting”. Chances are you’ll get a notification that claims “cell is just not empty” — if that’s the case, click on on it, and it is best to see this:

Step 4: Choose “Customized method is.”

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

Step 5: Enter the customized duplicate checking method.

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

=COUNTIF(A:A,A1)>1

This method searches for any textual content string that seems greater than as soon as in our chosen knowledge set, and by default will spotlight it in inexperienced. For those who want a unique shade, click on on the small paint pot icon within the formatting model bar and choose the colour you favor.

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

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

How you can Spotlight Duplicates in A number of Rows and Columns

For those who’ve acquired a bigger knowledge set to verify, it’s additionally attainable to focus on knowledge duplicates in a number of columns or rows.

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

In apply, this implies getting into an expanded knowledge 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 a substitute of simply looking 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 method turns into:

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

This can spotlight all duplicates throughout all three columns and all 10 rows, making it simple to identify knowledge 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 copy when you’ve completed it a few times, and when you’re snug with the method you possibly can scale as much as discover duplicates throughout rows, columns, and even a lot bigger knowledge units.