How to Find and Remove Duplicate Entries in Google Sheets

The fastest way to spot duplicates in Google Sheets

Google Sheets has a built-in tool that highlights duplicate values without requiring formulas or manual checking. Open your spreadsheet, select the column or range containing the data you want to check, then go to Format > Conditional formatting. In the formatting rules panel, choose Highlight duplicates from the dropdown menu. Sheets will when ready color any repeated entries, making them visible at a glance.

This method works best when you want to see which values repeat without changing your data. The highlighting stays in place until you remove the rule, so you can reference it while deciding what to do with the duplicates.

If you need to remove duplicates entirely rather than just see them, Google Sheets also has a dedicated removal tool. Select your data range, go to Data > Data cleanup > Remove duplicates. Sheets will delete every repeated row except the first occurrence of each value, keeping your spreadsheet clean in one step.

Key Takeaways

Use Format > Conditional formatting > Highlight duplicates to color repeated entries without changing your data.
Use Data > Data cleanup > Remove duplicates to permanently delete all repeated rows except the first of each value.
The COUNTIF formula lets you create a custom column that counts how many times each entry appears, giving you control over which duplicates to keep.
When checking for duplicates across multiple columns, select all columns together so Sheets treats each row as a complete record rather than checking each column separately.

Using formulas to count and identify duplicates

If you want more control than the built-in tools offer, the COUNTIF function lets you count how many times each value appears. In a new column next to your data, type =COUNTIF($A$2:$A$100,A2) (adjusting the range to match your actual data). This formula counts how many times the value in A2 appears in the entire range. Copy the formula down the column, and any cell showing a number higher than 1 is a duplicate.

This approach is useful when you need to decide which duplicates to keep. You can sort by the count column to group all duplicates together, then manually delete the ones you don't want. You can also filter to show only rows where the count is greater than 1, hiding the unique entries temporarily.

For checking duplicates across multiple columns at once — for example, finding rows where both the name and email match — use a different formula approach. In a helper column, type =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2) to count rows matching both column A and column B. This catches duplicates that would be missed if you checked each column separately.

Removing duplicates while keeping specific rows

The automatic remove duplicates tool keeps only the first occurrence of each value. If you need to keep a different row — for example, the most recent entry or the one with the most complete information — you'll need to manually delete instead. Use the COUNTIF method above to identify which rows are duplicates, then sort or filter to group them, and delete the ones you don't want to keep.

Another approach is to use the UNIQUE function if you're working with a fresh copy of your data. Type =UNIQUE(A2:A100) in an empty area of your sheet, and Sheets will create a list containing only the first occurrence of each value. You can then copy this cleaned list and paste it back into your original location if needed.

If your data has a timestamp or version number, sort by that column first before removing duplicates. This ensures the row you want to keep (the newest or most complete) appears first, so the automatic removal tool keeps the right one.

Checking for duplicates across sheets

When your data is split across multiple sheets in the same file, you can still find duplicates using formulas. Use COUNTIF with a reference to another sheet: =COUNTIF(Sheet2!$A$2:$A$100,A2). This counts how many times the value in your current sheet appears in Sheet2, helping you spot entries that exist in both places.

To find values that appear in one sheet but not the other, use COUNTIF with a condition. Type =IF(COUNTIF(Sheet2!$A$2:$A$100,A2)=0,"Not in Sheet2","") to flag entries that don't exist in the second sheet. This is useful when merging data from different sources and you need to know what's missing.

Handling duplicates with spaces and formatting differences

Sometimes entries look identical but aren't — extra spaces, different capitalization, or different date formats can make duplicates invisible to the tools. Before checking for duplicates, clean your data first. Use Data > Data cleanup > Trim whitespace to remove extra spaces at the beginning or end of entries.

For capitalization differences, use the LOWER function in a helper column: =LOWER(A2). This converts all text to lowercase, so "John" and "JOHN" will match. Then check for duplicates using this helper column instead of the original.

Date formatting can also hide duplicates — the same date might appear as "1/15/2024" in one cell and "January 15, 2024" in another. Check the actual cell values by clicking on each one; if the formula bar shows different formats, convert them to the same format before running duplicate checks.

Preventing duplicates before they happen

Rather than cleaning duplicates after the fact, you can prevent them from being entered in the first place. Select the column where you want to prevent duplicates, go to Data > Data validation, and choose Custom formula is. Type =COUNTIF($A$2:A2,A2)=1 to allow only the first occurrence of each value. Any attempt to enter a duplicate will trigger an error message.

This method works best for columns like email addresses, ID numbers, or product codes where duplicates should never exist. You can customize the error message to tell users why their entry was rejected, making it clear that the value already exists in the sheet.

Frequently Asked Questions

Can I undo a remove duplicates action?

Yes, use Ctrl+Z (or Cmd+Z on Mac) when ready after removing duplicates to restore the deleted rows. If you've made other changes since then, undo will revert those too. To be safe, always make a copy of your sheet before removing duplicates, so you can reference the original if needed.

What if I want to keep all duplicates but just see which ones exist?

Use the conditional formatting highlight method instead of the remove duplicates tool. Go to Format > Conditional formatting > Highlight duplicates, and the duplicates will be colored without being deleted. You can then manually review and decide which ones to remove.

Does the duplicate checker work on hidden rows or columns?

Yes, both the highlight and remove duplicates tools check all data in your selected range, including hidden rows and columns. If you want to exclude certain rows from the check, unhide them, delete them, or move them to a different sheet first.

Can I check for duplicates in just part of a column?

Yes, select only the range you want to check before opening the conditional formatting or data cleanup menu. Sheets will only look at the cells you've selected, ignoring the rest of the column.

What happens if two rows are identical except for one cell?

The built-in remove duplicates tool treats each row as a complete record. If rows differ in even one cell, they won't be flagged as duplicates. Use COUNTIFS with multiple columns if you want to check whether specific columns match while ignoring others.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.