If you’re working on a list of student names, product codes, or emails, duplicate data could exist without your knowledge. It’s quite easy to find and deal with duplicates in Google Sheets. Using built-in functions, you can count them, highlight them, or even remove them.
What Does Finding Duplicates Mean in Google Sheets?
In Google Sheets, finding duplicates means finding any data that shows up more than once in a certain column. These types of duplicates can cause issues like mailing the same recipient repeated emails. It is useful to:
- Avoid mistakes in summaries, reports, and calculations.
- Avoid sending the same person many emails or messages.
- Keep track of unique entries such as order numbers, usernames, or product data.
Identifying Duplicates in Google Sheets without Deleting
Sometimes, all you want to do is identify the values that are duplicated in a column or sheet, without deleting any information. This is very helpful when double-checking data or reviewing applications. To identify duplicates, what you have to do is:
➤ Select the column or range.
➤ Click on the Format menu and choose Conditional formatting.
➤ In the Conditional format rules sidebar, under “Format cells if“, choose “Custom formula is”
➤ Enter this formula:
➤ Choose a highlight color to mark the duplicates and click Done.
➤ Now see the result. Duplicate names are highlighted.
Finding Duplicates in Two Columns in Google Sheets
If you have two columns of data and want to find which values appear duplicated in the two columns, you can easily do this by using a formula or conditional formatting. What you have to do is:
➤Select cells A2:A6
➤Go to Format > Conditional formatting
➤ Under “Format cells if“, choose Custom formula is
➤ Type this formula:
➤ Choose a highlight color and click DONE.
➤ Now you can see the result. A2 and A3 cells are highlighted because they also exist in column B.
Using VLOOKUP to Find Duplicates in Google Sheets
Using the VLOOKUP function, you can find out whether a value in one column exists in another. This is helpful when comparing lists and trying to identify duplicates across two columns. What you have to do is:
➤ Click on cell C2 and type this formula:
➤ Press Enter, and you will see the result. If the column A names are found in Column B, it will show “Duplicate “.
➤ Drag the formula down to the rest of the rows.
Highlighting Duplicates in Google Sheets Using a Formula
With Conditional Formatting in Google Sheets, you can use a custom formula to highlight duplicate values. This method is useful when you want to visually identify duplicates in a column without deleting or altering the data. Let’s look at the dataset below, where student names are listed in two columns.
➤ Select the A1:A6 cell range
➤ Click Format > Conditional formatting.
➤ Under “Format cells if”, select “Custom formula is”.
➤ Enter this formula:
➤ Choose a background color to highlight the duplicates and click DONE.
➤ Now see the result. Duplicate names are highlighted in your selected column.
Highlighting Duplicates Across Multiple Columns in Google Sheets
When you are working with a lot of data and want to highlight any data that appears more than once, no matter which column it’s in. You can find it easily by using a formula or conditional formatting in Google Sheets. What you have to do is:
➤ Select the range of the data A2:C6
➤ Click Format > Conditional formatting.
➤ Under “Format cells if”, select “Custom formula is”.
➤ Enter this formula:
➤ Choose a background color to highlight the duplicates and click DONE.
➤ Now see the result. Duplicate names are highlighted across multiple columns.
Highlighting Duplicates Across Multiple Sheets in Google Sheets
Conditional formatting and a formula like COUNTIF can be used to find and highlight duplicate values when working with data from two or more sheets. Let’s look at the dataset where the class 3 and class 4 student names are listed in two sheets.
➤ Select the A2:A6 cell range in the “class 3 student” sheet.
➤ Go to Format > Conditional formatting.
➤ Under “Format cells if”, select “Custom formula is”
➤ Enter this formula:
➤ Choose a highlight color and click Done.
➤ Now see the result. Duplicate names are highlighted in the “class 3 student” sheet.
Frequently Asked Questions
Is it possible to find duplicates without the use of formulas in Google Sheets?
Yes, one function is integrated into Google Sheets. Choose Data > Cleanup Data > Remove Duplicates
Can I undo the removal of a duplicate?
Yes, you can undo. By pressing Ctrl + Z for Windows or Command + Z , you can undo.
Concluding Words
Finding duplicates in Google Sheets is very easy. Whether you’re working with product listings, names, or anything else, it helps you keep organized, clean up your data, and prevent errors.