How to Highlight Duplicates in Google Sheets [Step-by-Step]

How to Emphasize Duplicates in Google Sheets [Step-by-Step]


Copy data is the bane of spreadsheet remedies, specially at scale. Specified the quantity and selection of information now entered by groups, it’s doable that replicate information in resources like Google Sheets may possibly be pertinent and necessary, or it could be a irritating distraction from the key goal of spreadsheet efforts.

The opportunity issue raises a very good issue: How do you highlight duplicates in Google Sheets?

We have obtained you included with a stage-by-stage seem at how to highlight duplicates in Google Sheets, entire with photos to make absolutely sure you are on the proper track when it comes to de-duplicating your info.

→ Access Now: Google Sheets Templates [Free Kit]

Highlighting Duplicate Info in Google Sheets

Google Sheets is a absolutely free, cloud-based option to proprietary spreadsheet plans and — no surprise, considering that it is Google we’re dealing with — features a host of good capabilities to enable streamline knowledge entry, formatting, and calculations.

blank google sheets for highlighting duplicate data

Google Sheets has all the common capabilities: File, Edit, Watch, Format, Details, Equipment, etc. and can make it easy to quickly enter your facts, include formulation for calculations, and explore important relationships. What Sheets doesn’t have, nonetheless, is an uncomplicated way to come across and highlight duplicates.

While other spreadsheet applications, such as Excel, have created-in conditional formatting tools that can pinpoint copy facts in your sheet, Google’s resolution calls for a small more handbook hard work.

Step-by-Phase: How to Spotlight Duplicates in Google Sheets (With Photographs)

So how do you instantly emphasize duplicates in Google Sheets? Whilst there is no developed-in software for this function, you can leverage some developed-in capabilities to highlight duplicate details.

Here’s a move-by-action guideline:

Step 1: Open up your spreadsheet.

Action 2: Spotlight the data you want to verify.

Action 3: Beneath “Format”, decide on “Conditional Formatting.”

Stage 4: Select “Custom components is.”

Stage 5: Enter the custom made duplicate examining system.

Phase 6: Click on “Done” to see the success.

Move 1: Open your spreadsheet.

1st, head to Google Sheets and open the spreadsheet you want to check out for duplicate information.

how to highlight duplicate data in google sheets: open spreadsheet

Move 2: Emphasize the knowledge you want to test.

Upcoming, left-click and drag your cursor over the information you want to examine to emphasize it.

how to highlight duplicate data in google sheets:  select data

Phase 3: Less than “Format”, decide on “Conditional Formatting.”

Now, head to “Format” in the major menu row and find “Conditional Formatting”. You may get a notification that claims “cell is not empty” — if so, simply click on it, and you should see this:

how to highlight duplicate data in google sheets: open conditional formatting options

Step 4: Select “Custom method is.”

Future, we require to develop a tailor made formula. Under “Format cells if”, select the drop-down menu and scroll down to “Custom components is”.

how to highlight duplicate data in google sheets: select custom formula is

Stage 5: Enter the custom made copy checking method.

To research for replicate data, we have to have to enter the personalized duplicate checking method, which for our column of data appears like this:

=COUNTIF(A:A,A1)>1

This formulation lookups for any textual content string that appears extra than once in our chosen info set, and by default will spotlight it in green. If you choose a unique colour, simply click on the small paint pot icon in the formatting style bar and choose the shade you choose.

how to highlight duplicate data in google sheets: insert formula

Step 6: Click “Done” to see the benefits.

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

how to highlight duplicate data in google sheets: result

How to Highlight Duplicates in Many Rows and Columns

If you have acquired a larger sized info established to verify, it’s also feasible to spotlight facts duplicates in a number of columns or rows.

This starts the very same way as the copy checking procedure earlier mentioned — the only change is that you adjust the information selection to consist of all the cells you want to review.

In follow, this signifies getting into an expanded knowledge range in the Conditional structure guidelines menu and the personalized format box. Let us use the illustration higher than as a starting off issue, but alternatively of just browsing column A for duplicates, we’re heading to lookup throughout a few columns: A, B, and C, and also throughout rows 1-10.

how to highlight duplicate data in google sheets: data with multiple rows

When we enter our conditional format principles, Implement to Selection gets A1:C10 and our custom formula results in being:

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

This will spotlight all duplicates across all 3 columns and all 10 rows, building it easy to place data doppelgangers:

how to highlight duplicate data in google sheets in multiple rows and columns: result

Dealing With Duplicates in Duplicates in Google Sheets

Can you spotlight duplicates in Google Sheets? Definitely. Whilst the procedure takes additional energy than some other spreadsheet alternatives, it is easy to replicate the moment you’ve accomplished it at the time or 2 times, and after you are relaxed with the course of action you can scale up to come across duplicates throughout rows, columns, and even significantly more substantial info sets.

business google sheets templates

2 thoughts on “How to Emphasize Duplicates in Google Sheets [Step-by-Step]

  1. If you want to get a great deal from this piece of writing then you
    have to apply these techniques to your won webpage.

  2. Can I just say what a relief to discover someone who
    genuinely knows what they’re discussing on the web. You certainly realize how to bring a problem
    to light and make it important. More people need to check this out
    and understand this side of your story. I was surprised that you’re not more popular because you definitely possess the gift.

Leave a Reply

Your email address will not be published.