The right way to Highlight Duplicates on the internet Sheets [Step-by-Step]

Posted on Posted in Blog

Duplicate information is the bane of spreadsheet solutions, especially at scale. Provided the volume and variety of data now joined by teams, it is possible that replicate data in tools like Google Bedding may be relevant plus necessary, or it may be a frustrating muddiness from the primary purpose of spreadsheet efforts.

The potential issue raises a good query: How do you highlight replicates in Google Sheets?

We’ve got you covered using a step-by-step look at the best way to highlight duplicates on the internet Sheets, complete with pictures to make sure you’re on the right track when it comes to de-duplicating your computer data.

→ Access Now: Google Sheets Templates [Free Kit]

Highlighting Duplicate Data in Google Sheets

Google Sheets is really a free, cloud-based substitute for proprietary spreadsheet applications and — not surprising, since it’s Search engines we’re dealing with — offers a host of big features to help streamline data entry, format, and calculations.

blank google sheets for highlighting duplicate data

Google Sheets has all of the familiar functions: Document, Edit, View, Structure, Data, Tools, etc . and makes it easy to quickly enter your data, add formulas regarding calculations, and discover essential relationships. What Bedsheets doesn’t have, however , is definitely an easy way to find and highlight replicates.

While additional spreadsheet tools, for example Excel, have built-in conditional formatting equipment that can pinpoint replicate data in your linen, Google’s solution requires a little more manual energy.

Step-by-Step: Ways to Highlight Duplicates in Google Sheets (With Pictures)

So how do you automatically highlight replicates in Google Sheets? While there’s no built-in tool for this purpose, you are able to leverage some pre-installed functions to emphasize duplicate data.

Here’s a step by step guide:

Step 1 : Open your spreadsheet.

Step 2: Highlight the data you want to check.

Step 3: Under “Format”, select “Conditional Formatting. ”

Step 4: Select “Custom formula is. ”

Step five: Enter the custom duplicate checking formula.

Step 6: Click on “Done” to see the results.

Step 1 : Open up your spreadsheet.

First, head to Search engines Sheets and open the spreadsheet you wish to check for duplicate information.

how to highlight duplicate data in google sheets: open spreadsheet

Step 2: Highlight the information you want to check.

Next, left-click plus drag your cursor over the data you need to check to emphasize it.

how to highlight duplicate data in google sheets: select data

Step 3: Below “Format”, select “Conditional Formatting. ”

Now, head to “Format” in the top menu row and select “Conditional Formatting”. You may get a notification that says “cell is not empty” — if so, simply click it, and you should discover this:

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

Step 4: Choose “Custom formula will be. ”

Following, we need to create a customized formula. Under “Format cells if”, select the drop-down menu and scroll down to “Custom formula is”.

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

Phase 5: Enter the custom duplicate checking method.

To search for duplicate data, we need to enter the custom duplicate checking formula, which for the column of information looks like this:

=COUNTIF(A: A, A1)> 1

This formula searches for any textual content string that shows up more than once in our selected data set, and by default will emphasize it in eco-friendly. If you prefer a different colour, click on the small paint pot icon within the formatting style bar and select the color you like.

how to highlight duplicate data in google sheets: insert formula

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

And voilà — we’ve outlined the duplicate information in Google Sheets.

how to highlight duplicate data in google sheets: result

The right way to Highlight Duplicates within Multiple Rows plus Columns

If you’ve got a larger data set to check, it is also possible in order to highlight data replicates in multiple columns or rows.

This starts exactly the same way as the duplicate checking process over — the only distinction is that you change the data range to include all of the cells you want to compare.

In practice, this means entering an extended data range in the Conditional format rules menu and the custom format box. Let us use the example above as a starting point, but rather of just searching column A intended for duplicates, we’re going to search across 3 columns: A, B, and C, as well as across rows 1-10.

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

When we enter our own conditional format guidelines, Apply to Range becomes A1: C10 and our custom formulation becomes:

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

This can highlight all duplicates across all three columns and all 10 rows, making it easy to spot 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 highlight replicates in Google Sheets? Definitely. While the process takes more effort than some other spreadsheet solutions, it’s easy to reproduce once you’ve done it once or twice, and once you’re comfortable with the procedure you can scale up to find duplicates across rows, columns, as well as much larger data sets.

business google sheets templates

Spread the love

Leave a Reply

Your email address will not be published. Required fields are marked *