Buy Now

Google Sheets Highlight Duplicates: Everything You Need to Know

May 31, 2022

The issue of duplicate data catches us all out, whether it is in Microsoft Excel or Google Sheets. If you work with data analysis in Google Sheets, at some point, you will need to highlight duplicates.

This might duplicate data found in the same row or column or within the entire dataset. Therefore, it is important to learn how to highlight duplicate values in Google Sheets.

You can quickly highlight duplicates within Google Sheets with a touch of conditional formatting. This tends to be the simplest way of highlighting duplicates in Google Sheets, whether you highlight duplicate rows or columns.

Once you have identified a duplicate cell or multiple duplicate instances, you can choose whether to keep or delete them.

How to Find Duplicates in Google Sheets Columns

One option available to you for highlighting duplicates in google sheets is to highlight them with color. You have the power to search multiple columns for duplicate data and highlight them automatically.

  • Open your Google Sheets document

  • Ensure the data in your spreadsheet is organized by columns, and all columns have headings.

  • Choose the column you wish to find duplicates in.

  • Select Format, Conditional formatting, and the conditional formatting menu will appear.

  • Confirm your cell range matches your needs.

  • From the Format cells, if drop-down menu, choose Custom formula is. You will notice a new field appear.

  • Type in "=countif(A: A, A1)>1," adjusting the letters to suit your column range.

  • From the Formatting style area, pick a fill color for your duplicates in Google Sheets.

  • Click Done to apply your conditional formatting. Every duplicate should now be filled.

Use Formulas to Find Duplicates in Google Sheets

Formulas are also a fantastic way to highlight duplicate cells within a Google Sheets spreadsheet. This technique for finding duplicate cells can be done via column or by row and will highlight duplicate data in a new sheet or column within your spreadsheet.

Discover Duplicates in Columns Using a Formula

Discovering duplicates in columns allows you to study a single column of data to discover if there are any duplicates within that column range.

  • Open up a Google Sheets spreadsheet you were to find duplicates in.

  • Select an open cell from the same sheet and click on it.

  • Within that empty cell, type "=UNIQUE" and hit Enter. This process will activate the formula feature.

  • Choose the column you want to highlight duplicates in by choosing the letter at the top of your column. Your formula will automatically include the column range for you.

  • Hit Enter to finish the formula.

Discover Duplicate Rows With a Formula

If you want to find duplicate rows within your Google Sheets document, the process is very similar. However, the cell range you gather to compare using the formula differs.

Open your Google Sheets spreadsheet.

Select an open cell from that same sheet.

In your empty cell, type "=UNIQUE" and hit Enter. This process will activate the custom formula.

Choose the rows you want to search for duplicate values. If you want to choose an entire row, click the number you want to highlight.

Hit Enter to finish the formula, and your duplicate data will show.

Find Duplicates in Google Sheets With an Add-On

You also have the option of using a Google Sheets add-on to discover and highlight duplicates. Certain add-ons will allow you to utilize your duplicates more, such as deleting all the cells, comparing the data over multiple spreadsheets, or moving data from one column to another.

Frequently Asked Questions

How do I remove duplicates in Google Sheets?

If you want to highlight duplicates and remove them within a Google Sheets document, click into your Google Sheets file and select the data range you want to check. From there, head to Data, Data Clean-up, then Remove Duplicates.

What is the formula to highlight duplicates in Google Sheets?

Highlight your chosen row or column. Hit Format, then Conditional Formatting. Choose Custom Formula from the Format menu, and enter =countif(A: A,A1)>1.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras sed sapien quam. Sed dapibus est id enim facilisis, at posuere turpis adipiscing. Quisque sit amet dui dui.

Call To Action

Stay connected with news and updates!

Join our mailing list to receive the latest news and updates from our team.
Don't worry, your information will not be shared.

We hate SPAM. We will never sell your information, for any reason.