Duplicate knowledge is the bane of spreadsheet options, particularly at scale. Given the quantity and number of knowledge now entered by groups, it’s potential that duplicate knowledge in instruments like Google Sheets could also be related and vital, or it may very well be a irritating distraction from the first goal of spreadsheet efforts.
The potential drawback raises query: How do you spotlight duplicates in Google Sheets?
![→ Access Now: Google Sheets Templates [Free Kit]](https://no-cache.hubspot.com/cta/default/53/e7cd3f82-cab9-4017-b019-ee3fc550e0b5.png)
I’ve obtained you lined with a step-by-step have a look at methods to spotlight duplicates in Google Sheets and discover duplicates in Google Sheets, full with pictures to make sure you’re heading in the right direction on the subject of de-duplicating your knowledge.
Desk of Contents
How one can Discover Duplicates in Google Sheets
Google Sheets is a free, cloud-based different to proprietary spreadsheet applications and — no shock, because it’s Google we’re coping with — presents a bunch of nice options to assist streamline knowledge entry, formatting, and calculations.
There are two methods to take away duplicates in Google Sheets: conditional formatting and the UNIQUE operate. I’ll go over each under, however, earlier than you begin following alongside, I’ve two issues to notice:
- You possibly can run a number of conditional formatting guidelines at a time, so that you don’t have to delete any to run your conditional formatting rule to delete duplicates.
- You gained’t get an correct duplicate rely in case you have any further characters or areas in your knowledge, so it’s essential to ensure your set is clear. Even an unintended further area will rely it as a separate knowledge level.
Let’s dive into how one can spotlight and take away duplicates in Google Sheets.
Highlighting Duplicate Knowledge in Google Sheets
Google Sheets has all of the acquainted features: File, Edit, View, Format, Knowledge, Instruments, and so on., and makes it simple to shortly enter your knowledge, add formulation for calculations, and uncover key relationships.
Whereas different spreadsheet instruments, akin to Excel, have built-in conditional formatting instruments to pinpoint duplicate knowledge in your sheet, Google’s resolution requires a little bit extra handbook effort.
So how do you robotically spotlight duplicates in Google Sheets? Whereas there’s no built-in device for this goal, you possibly can leverage some built-in features to focus on duplicate knowledge.
Step-by-Step: How one can Spotlight Duplicates in Google Sheets (With Photos)
Right here’s a step-by-step information to highlighting duplicates in Google Sheets:
Step 1: Open your spreadsheet.
Step 2: Spotlight the information you need to test.
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 “Finished” to see the outcomes.
Step 1: Open your spreadsheet.
First, head to Google Sheets and open the spreadsheet you need to test for duplicate knowledge.
Step 2: Spotlight the information you need to test.
Subsequent, drag your cursor over the information you need to test to focus on it.
Step 3: Underneath “Format”, choose “Conditional Formatting.”
Now, head to “Format” within the prime menu row and choose “Conditional Formatting.” You must then see a popup window titled “Conditional format guidelines.”
Step 4: Choose “Customized method is.”
Subsequent, it’s essential to create a customized method. Click on the down arrow beneath “Format cells if,” and choose “Customized method is” from the dropdown menu. It’s the final possibility to select from, so you possibly can scroll proper to the top.
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 (A) appears like this:
=COUNTIF(A:A,A1)>1
The method searches for any textual content string that seems greater than as soon as in an information set. The default spotlight shade is inexperienced, however you possibly can change it by clicking on the paint can icon within the “Formatting fashion” menu.
Step 6: Click on “Finished” to see the outcomes.
And voilà — we’ve highlighted the duplicate knowledge in Google Sheets.
How one can Spotlight Duplicates in A number of Rows and Columns
You may also spotlight duplicates in a number of rows and columns in case you have a bigger knowledge set. The method begins the identical as above, however you enter an expanded knowledge vary within the Conditional format guidelines menu to account for all of the cells you need to examine.
I’ll use the identical instance above as a place to begin, however I’ll add a number of extra names so we use a method to go looking throughout three columns: A, B, and C, and in addition throughout rows 1-10.
To start out, repeat steps two – 4 from above, however enter the next equation throughout step 5:
=COUNTIF($A$2:G,Oblique(Handle(Row(),Column(),)))>1
This can spotlight all duplicates throughout all three columns and all ten rows, making it simple to identify knowledge doppelgangers:
Discover and Spotlight Duplicates in Google Sheets With the Distinctive Operate
One other method to discover duplicates in Sheets is to make use of the UNIQUE operate, which appears for the distinctive values in your designated vary and produces a duplicate-free record. Right here’s the method:
=UNIQUE(RANGE)
Be aware: This method can solely establish duplicates in a single column.
There’s just one step to this methodology, which is coming into your method into an empty cell. Persevering with with the identical knowledge set from above, I entered =UNIQUE(A1:A10). The picture under is my duplicate-free record (on the left).
To make use of the UNIQUE operate to seek out duplicates in a number of columns and rows, use this method:
=UNIQUE(TOCOL(RANGE))
A disadvantage to utilizing the UNIQUE operate to seek out duplicates in Google Sheets is that it spits out a separate duplicate-free record as an alternative of highlighting and deleting them. It creates an added step because you’ll must manually take away duplicates along with your new record as a reference, so I like to recommend this methodology for these with a smaller knowledge set who don’t thoughts a number of handbook updates.
Alternatively, this methodology is a wonderful possibility for producing a cleaned record to begin contemporary.
How one can Take away Duplicates in Google Sheets
Along with highlighting duplicates, you can too use Google Sheets to delete duplicates with the Knowledge Cleanup characteristic. Beneath, I’ll present you ways.
Step 1: Choose any cell.
Step 2: Navigate to the header toolback, choose “Knowledge,” then “Knowledge cleanup,” then “Take away duplicates.”
Step 3: Within the popup window, choose the columns you need to delete duplicate knowledge from, then choose “Take away duplicates.
Be aware: When you have a sheet header, ensure to pick out “Knowledge has a header row” so it’s not included within the duplicate search.
All duplicates are actually gone!
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 finished 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 knowledge units.
