Google Sheets Highlight Duplicates: Everything You Need to Know
May 31, 2022
The issue of duplicate data catches us all, whether in Microsoft Excel or Google Sheets. If you work with data analysis in Google Sheets, you will need to highlight duplicates at some point.
This might duplicate data found in the same row or column or within the entire dataset. Therefore, learning how to highlight duplicate values in Google Sheets is essential. You can quickly highlight duplicates within Google Sheets with 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 instances, you can choose whether to keep or delete them. 
How to Find Duplicates in Google Sheets Columns
One option for highlighting duplicates in google sheets is to highlight them with color. You can 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. You will notice a new field appear.
- Type in "=countif(A: A, A1)>1," adjusting the letters to suit your column range.
- Pick a fill color for your duplicates from the Formatting style area 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 if you were to find duplicates.
- Please 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 to highlight duplicates by choosing the letter at the top. Your formula will automatically include the column range for you.
- Hit Enter to finish the formula.
Discover Duplicate Rows With a Formula
The process is very similar if you want to find duplicate rows within your Google Sheets document. 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. 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 can also use a Google Sheets add-on to discover and highlight duplicates. Specific 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 for highlighting 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.
Want to Make Excel Work for You? Try out 5 Amazing Excel Templates & 5 Unique Lessons
We hate SPAM. We will never sell your information, for any reason.
