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 needed, or it might be a irritating distraction from the first objective of spreadsheet efforts.

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

We’ve bought you coated with a step-by-step take a look at how you can spotlight duplicates in Google Sheets, full with photographs to be sure you’re heading in the right direction in relation to de-duplicating your information.

Highlighting Duplicate Knowledge in Google Sheets

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

Google Sheets has all of the acquainted features: File, Edit, View, Format, Knowledge, Instruments, and so forth. and makes it straightforward to rapidly enter your information, add formulation for calculations, and uncover key relationships. What Sheets doesn’t have, nevertheless, is a straightforward option to discover and spotlight duplicates.

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

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

So how do you robotically spotlight duplicates in Google Sheets? Whereas there’s no built-in software for this objective, you possibly can leverage some built-in features to focus on duplicate information.

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

Step 1: Open your spreadsheet.

Step 2: Spotlight the information you need to examine.

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

Step 4: Choose “Customized system is.”

Step 5: Enter the customized duplicate checking system.

Step 6: Click on “Achieved” 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 information you need to examine.

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

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

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

Step 4: Choose “Customized system is.”

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

Step 5: Enter the customized duplicate checking system.

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

=COUNTIF(A:A,A1)>1

This system 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. In case you choose a unique coloration, click on on the small paint pot icon within the formatting type bar and choose the colour you like.

Step 6: Click on “Achieved” 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

In case you’ve bought a bigger information set to examine, it’s additionally doable to focus on information duplicates in a number of columns or rows.

This begins the identical approach 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 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 go looking 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 system 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 straightforward 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 straightforward to copy when you’ve performed it a couple of times, and when you’re comfy with the method you possibly can scale as much as discover duplicates throughout rows, columns, and even a lot bigger information units.