How to find, highlight and remove duplicates in Google Sheets [Step-by-Step]

Duplicate information is a drawback of spreadsheet options, particularly at scale. Given the quantity and number of information entered by groups now, duplicate information in instruments like Google Sheets might be related and vital, or it may be a irritating distraction from the unique objective of the spreadsheet effort.

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

I’ve lined you step-by-step spotlight duplicates in Google Sheets and discover duplicates in Google Sheets to ensure you’re heading in the right direction relating to de-duplicating your information. .

Index

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 working with — presents a bunch 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 distinctive capabilities I will go into each beneath, however, earlier than you begin following alongside, I’ve two issues to notice:

1. You’ll be able to run a number of conditional formatting guidelines without delay, so that you need not run any of your conditional formatting guidelines to take away duplicates.
2. You will not get an correct duplicate rely in case your information has additional characters or areas, so it’s good to make certain your set is clear. Even an unintentional additional area will rely as a separate information level.

Let’s dive into how one can spotlight and take away duplicates in Google Sheets

Highlighting duplicate information in Google Sheets

Google Sheets contains all of the acquainted capabilities: File, Edit, View, Format, Information, Instruments, and so on., and makes it simple to rapidly enter your information, add formulation for calculations, and uncover key relationships.

Whereas different spreadsheet instruments, reminiscent of Excel, have built-in conditional formatting instruments to identify duplicate information in your sheet, Google’s resolution requires a bit of extra handbook effort.

So how do you mechanically spotlight duplicates in Google Sheets? Though there is no such thing as a built-in software for this objective, you need to use some built-in capabilities to focus on duplicate information.

Step by Step: How one can Spotlight Duplicates in Google Sheets (with Footage)

Here is a step-by-step information to highlighting duplicates in Google Sheets:

Step 1: Open your spreadsheet.

Step 2: Spotlight the info you wish to study.

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

Step 4: Choose “Customized system is”.

Step 5: Enter customized duplicate checking system.

Step 6: Click on “Finished” to see the consequence.

Step 1: Open your spreadsheet.

First, go to Google Sheets and open the spreadsheet you wish to verify for duplicate information.

Step 2: Spotlight the info you wish to study.

Subsequent, drag your cursor over the info you wish to study to focus on it.

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

Now, go to “Format” within the high menu bar and choose “Conditional Formatting”. You’ll then see a popup window titled “Conditional Formatting Guidelines”.

Step 4: Choose “Customized system is”.

Subsequent, it’s good to create a customized system. Click on the down arrow below “Format Cell If” and choose “Customized System Is” from the dropdown menu. That is the final possibility to select from, so you possibly can scroll to the tip.

Step 5: Enter customized duplicate checking system.

To seek for duplicate information, we have to write a customized duplicate checking system, which appears like our information in column (A):

=COUNTIF(A:A,A1)>1

The system searches for a 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 the paint can icon within the “Formatting Kinds” menu.

Step 6: Click on “Finished” to see the consequence.

And voilà — we have highlighted the duplicate information within the Google Sheet

How one can spotlight duplicates in a number of rows and columns

You probably have a big information set, you possibly can spotlight duplicates in a number of rows and columns. The method begins as above, however enter an expanded information vary within the Conditional Formatting Guidelines menu to account for all of the cells you wish to examine.

I will use the identical instance above as a place to begin, however I will add just a few extra names in order that we use a system to go looking throughout three columns: A, B, and C, and throughout rows 1-10.

To start, repeat steps two – 4 from above, however enter the next equation throughout step 5:

=COUNTIF($A$2:G,oblique(tackle(row(),column(),)))>1

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

Discover and spotlight duplicates in Google Sheets with distinctive capabilities

One other strategy to discover duplicates in sheets is to make use of the UNIQUE perform, which appears for distinctive values ​​in your specified vary and produces a duplicate-free record. Right here is the supply:

=distinctive(vary)

Observe: This system can detect duplicates in just one column.

This methodology has just one step, which is coming into your system into an empty cell. Working the identical information set from above I entered =distinctive(A1:A10). The picture beneath is my duplicate-free record (on the left).

To make use of the UNIQUE perform to seek out duplicates in a number of columns and rows, use this system:

= distinctive(tocol(vary))

A disadvantage of utilizing the UNIQUE perform to seek out duplicates in Google Sheets is that it creates a separate duplicate-free record as an alternative of highlighting and deleting them. This creates an additional step as a result of it’s important to manually take away duplicates along with your new record as a reference, so I like to recommend this methodology for these with a small information set who do not thoughts just a few handbook updates.

Alternatively, this methodology is a superb different to making a clear slate for a recent begin.

How one can Take away Duplicates in Google Sheets

Along with highlighting duplicates, you can even use Google Sheets to delete duplicates with the Information Cleanup function. Beneath, I will present you ways.

Step 1: Choose any cell.

Step 2: Navigate to the header toolback, choose “Information”, then “Information Cleanup”, then “Take away Duplicates”.

Step 3: Within the popup window, choose the columns you wish to delete duplicate information from, then choose “Take away Duplicates”.

Observe: You probably have a sheet header, make sure to choose “Information accommodates a header row” in order that it’s not included within the duplicate search.

All duplicates are actually gone!

Coping with duplicates in pretend in Google Sheets

Are you able to spotlight duplicates in Google Sheets? completely Whereas the method takes extra effort than another spreadsheet options, it is simple to duplicate as soon as you have performed it a couple of times, and when you’re snug with the method you possibly can scale to seek out duplicates throughout rows, columns, and much more. Bigger information units.