![The way to Discover, Spotlight & Take away Duplicates in Google Sheets [Step-by-Step]](https://i3.wp.com/blog.hubspot.com/hubfs/find-duplicates-in-google-sheets%20.png#keepProtocol?w=598&resize=598,398&ssl=1 "The way to Discover, Spotlight & Take away 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 attainable that duplicate information in instruments like Google Sheets could also be related and essential, or it could possibly be a irritating distraction from the first function of spreadsheet efforts.
The potential drawback raises a superb 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 bought you lined with a step-by-step have a look at how one can spotlight duplicates in Google Sheets and discover duplicates in Google Sheets, full with pictures to make sure you’re heading in the right direction in terms of de-duplicating your information.
Desk of Contents
The way to Discover Duplicates 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 — presents a number of nice options to assist streamline information 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 beneath, however, earlier than you begin following alongside, I’ve two issues to notice:
- You’ll be able to 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 received’t get an correct duplicate depend in case you have any further characters or areas in your information, so it’s essential be sure that your set is clear. Even an unintended further area will depend it as a separate information 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 many others., and makes it simple to shortly enter your information, add formulation for calculations, and uncover key relationships.
Whereas different spreadsheet instruments, corresponding to Excel, have built-in conditional formatting instruments to pinpoint duplicate information in your sheet, Google’s resolution requires a bit of extra guide effort.
So how do you routinely spotlight duplicates in Google Sheets? Whereas there’s no built-in instrument for this function, you possibly can leverage some built-in features to focus on duplicate information.
Step-by-Step: The way to 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 info you need to examine.
Step 3: Beneath “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 examine for duplicate information.
Step 2: Spotlight the info you need to examine.
Subsequent, drag your cursor over the info you need to examine to focus on it.
Step 3: Beneath “Format”, choose “Conditional Formatting.”
Now, head to “Format” within the prime menu row and choose “Conditional Formatting.” You need to then see a popup window titled “Conditional format guidelines.”
Step 4: Choose “Customized method is.”
Subsequent, it’s essential 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 choice to select from, so you possibly can scroll proper to the tip.
Step 5: Enter the customized duplicate checking method.
To seek for duplicate information, we have to enter the customized duplicate checking method, which for our column of information (A) seems 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 coloration is inexperienced, however you possibly can change it by clicking on the paint can icon within the “Formatting type” menu.
Step 6: Click on “Executed” to see the outcomes.
And voilà — we’ve highlighted the duplicate information in Google Sheets.
The way to Spotlight Duplicates in A number of Rows and Columns
You can too spotlight duplicates in a number of rows and columns in case you have a bigger information set. The method begins the identical as above, however you enter an expanded information 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 couple of extra names so we use a method to look throughout three columns: A, B, and C, and likewise 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
It will spotlight all duplicates throughout all three columns and all ten rows, making it simple to identify information doppelgangers:
Discover and Spotlight Duplicates in Google Sheets With the Distinctive Perform
One other strategy to discover duplicates in Sheets is to make use of the UNIQUE operate, which seems for the distinctive values in your designated vary and produces a duplicate-free record. Right here’s the method:
=UNIQUE(RANGE)
Word: This method can solely establish duplicates in a single column.
There’s just one step to this technique, which is getting into your method into an empty cell. Persevering with with the identical information set from above, I entered =UNIQUE(A1:A10). The picture beneath is my duplicate-free record (on the left).
To make use of the UNIQUE operate to search out duplicates in a number of columns and rows, use this method:
=UNIQUE(TOCOL(RANGE))
A downside to utilizing the UNIQUE operate to search 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 should manually take away duplicates together with your new record as a reference, so I like to recommend this technique for these with a smaller information set who don’t thoughts a couple of guide updates.
Alternatively, this technique is a superb choice for producing a cleaned record to start out recent.
The way to Take away Duplicates in Google Sheets
Along with highlighting duplicates, you can too use Google Sheets to delete duplicates with the Knowledge Cleanup function. Under, I’ll present you the way.
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 information from, then choose “Take away duplicates.
Word: In case you have a sheet header, be sure that to pick “Knowledge has a header row” so it’s not included within the duplicate search.
All duplicates at the moment are 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 performed 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 information units.
