The fastest way to spot duplicates in Google Sheets

Google Sheets has a built-in tool that highlights duplicate values without requiring formulas or add-ons. Open your spreadsheet, select the column or range where you want to find duplicates, then go to Format > Conditional formatting. Under "Format rules," choose Custom formula is and enter =COUNTIF($A$1:$A$1000,A1)>1 (adjusting the column letter and row count to match your data). Pick a highlight color and click Done. Every duplicate value will now show in that color.

This method works best when you have a single column to check — like a list of email addresses, product IDs, or customer names. If you need to find duplicates across multiple columns or check whether entire rows repeat, you'll need a different approach.

The conditional formatting approach leaves your data untouched. It only marks which values appear more than once. If you want to actually remove the duplicates instead of just seeing them, Google Sheets has a separate tool for that.

Key Takeaways

  • Conditional formatting with a COUNTIF formula highlights every duplicate value in a column without changing your data.
  • The Data > Data cleanup > Remove duplicates tool deletes duplicate rows entirely, keeping only the first occurrence of each entry.
  • Sorting your data first makes it easier to spot duplicates by eye and to verify that the tool caught everything.
  • If you need to keep duplicates but flag them for review, conditional formatting is safer than deletion.

Using Google Sheets' built-in remove duplicates feature

If you want to delete duplicate rows rather than just highlight them, Google Sheets has a dedicated tool. Select all your data (including headers), then go to Data > Data cleanup > Remove duplicates. A panel will appear asking which columns to check. By default, all columns are selected — this means a row is only removed if every single cell matches another row exactly. If you want to remove rows based on just one column (like removing customers with duplicate email addresses), uncheck all other columns first.

The tool keeps the first occurrence of each duplicate and removes the rest. It does not ask which copy to keep — if you have three rows with the same email address, it will delete rows two and three and keep row one. This is why sorting your data first matters: if the first occurrence is incomplete or outdated, you'll end up keeping the wrong version.

After removal, Google Sheets shows you how many duplicate rows it deleted. Check your data afterward to make sure the result is what you expected. If it removed too much or too little, you can undo the action with Ctrl+Z (or Cmd+Z on Mac).

Finding duplicates across multiple columns

When you need to check whether the same combination of values appears more than once — for example, the same first name and last name together, even if other details differ — conditional formatting requires a more complex formula. Select your data range, go to Format > Conditional formatting, choose Custom formula is, and use a formula like =COUNTIFS($A$1:$A$1000,A1,$B$1:$B$1000,B1)>1. This checks both column A and column B together.

Add more columns to the formula by extending the COUNTIFS function: =COUNTIFS($A$1:$A$1000,A1,$B$1:$B$1000,B1,$C$1:$C$1000,C1)>1 checks three columns at once. The formula becomes harder to read as you add more columns, but it works the same way.

If your data has many columns and you want to check all of them at once, the remove duplicates tool is simpler — just select everything and let it run. But if you only care about certain columns, the conditional formatting approach gives you more control.

Sorting data to verify duplicates before deletion

Before you use the remove duplicates tool, sort your data by the column you're checking. This groups all the duplicates together, making it straightforward to see what you're about to delete. Select your data, go to Data > Sort range, and choose the column to sort by. Now identical values sit next to each other, and you can scroll through to spot any that look wrong.

Sorting also helps you catch near-duplicates — entries that are almost the same but not quite. For example, "john.smith@email.com" and "john.smith@email.co.uk" are different email addresses, but they might represent the same person. The remove duplicates tool will not catch these because it looks for exact matches only. Sorting lets you find them by eye and decide whether to merge them manually.

After sorting, you can still undo the sort if you need the original order back. Go to Data > Sort range and look for an option to sort by a column you added earlier (like row number) if you saved the original order.

Using formulas to count duplicates without highlighting

If you want a count of how many times each value appears instead of just highlighting it, add a helper column with a COUNTIF formula. Click on a blank column next to your data, then enter =COUNTIF($A$1:$A$1000,A1) in the first row. Copy this formula down the entire column. Each cell will now show how many times that value appears in the range. Values that appear once show "1", values that appear twice show "2", and so on.

This approach is useful when you need to report on duplicates or when you want to filter your data to show only entries that appear more than once. You can then use Data > Create a filter to show only rows where the count is greater than 1.

The helper column stays in your spreadsheet until you delete it, so you can refer back to it later. If you no longer need it, select the column and go to Edit > Delete column.

Handling duplicates in very large spreadsheets

Google Sheets can slow down when you explore conditional formatting to thousands of rows. If your spreadsheet has more than 10,000 rows, consider breaking it into smaller sections. Select just the first 5,000 rows, explore conditional formatting, then repeat for the next section. This keeps the spreadsheet responsive while you work.

Alternatively, use the remove duplicates tool on the entire range at once — it typically runs faster than conditional formatting on large datasets. Just make sure you've sorted and reviewed your data first, because once it runs, you can only undo it when ready afterward.

If you're working with a very large file and Google Sheets is running slowly, try downloading it as a CSV file, opening it in a desktop spreadsheet process like LibreOffice Calc, and using that program's duplicate-removal tools instead. Then upload the cleaned file back to Google Drive.

Frequently Asked Questions

Does removing duplicates delete the data permanently?

No, you can undo it when ready by pressing Ctrl+Z (or Cmd+Z on Mac). Once you close the spreadsheet or perform other actions, the undo option disappears. Always make a copy of your spreadsheet before removing duplicates if you're unsure about the result.

Can I remove duplicates but keep the last occurrence instead of the first?

Google Sheets' remove duplicates tool always keeps the first occurrence. To keep the last one, sort your data in reverse order first, run the tool, then sort back to your preferred order. Or add a helper column with row numbers, sort by that column in descending order, remove duplicates, then delete the helper column.

What's the difference between highlighting duplicates and removing them?

Highlighting with conditional formatting shows you which values repeat but leaves all your data in place. Removing duplicates deletes entire rows. Use highlighting when you want to review the duplicates first or keep them for reference. Use removal when you're confident the duplicates are unwanted.

Will the remove duplicates tool catch duplicates with extra spaces or different capitalization?

No, it looks for exact matches only. "John Smith" and "john smith" are treated as different entries. If you suspect spacing or capitalization issues, use Find and Replace to standardize them first, or add a helper column with the TRIM and LOWER functions to clean the data before checking for duplicates.