The fastest way to spot duplicates in Google Sheets
Google Sheets has a built-in tool called conditional formatting that highlights duplicate values in seconds. Select the range of cells you want to check, open the Format menu, choose Conditional Formatting, then pick "Highlight duplicates" from the formatting rules. Sheets will color any repeated entries so you can see them immediately.
If you want to remove duplicates instead of just highlighting them, use the Data menu. Select your data range, go to Data > Data Cleanup > Remove Duplicates, and Sheets will delete the extra copies in one step. This method works best when your data is organized in columns with headers.
For more control — like checking only certain columns or keeping the first occurrence instead of the last — you can use formulas like COUNTIF to build your own duplicate detection. This takes longer but lets you decide exactly what counts as a duplicate and what to do with it.
Key Takeaways
- Conditional formatting with the "Highlight duplicates" rule shows you every repeated value in a selected range without changing your data.
- The Data > Data Cleanup > Remove Duplicates tool deletes extra copies automatically, but it removes entire rows, not just the duplicate cell.
- COUNTIF formulas let you find duplicates in specific columns and decide which copies to keep or delete manually.
- Always make a copy of your sheet before removing duplicates, because the deletion cannot be undone.
Using conditional formatting to highlight duplicates
Conditional formatting is the simplest way to see duplicates without changing anything. Select the cells you want to check — this can be a single column, multiple columns, or your entire dataset. Click Format in the menu bar, then Conditional Formatting.
In the formatting rules panel on the right, click the dropdown that says "Format rules" and select "Highlight duplicates." Sheets will automatically color any value that appears more than once in your selection. By default, duplicates turn light red, but you can change the color by clicking the fill icon in the toolbar.
This method is safe because it only changes how the cells look — your actual data stays the same. You can turn off the highlighting anytime by selecting the range again, opening Conditional Formatting, and clicking the trash icon next to the rule.
Removing duplicate rows with the Data Cleanup tool
The fastest way to delete duplicates is through Data > Data Cleanup > Remove Duplicates. First, select all your data including headers. Then open the Data menu, hover over Data Cleanup, and click Remove Duplicates.
A dialog box will appear showing which columns Sheets will use to find duplicates. By default, it checks all columns — so a row is only considered a duplicate if every cell matches another row exactly. If you want Sheets to check only certain columns, uncheck the ones you don't need. For example, if you have a Name column and an Email column, you might uncheck Email so Sheets only looks for duplicate names.
Click Remove Duplicates and Sheets will delete the extra rows immediately. The tool keeps the first occurrence of each duplicate and removes the rest. There is no undo button for this action, so always save a copy of your sheet first or use Ctrl+Z right away if something goes wrong.
Finding duplicates with COUNTIF formulas
If you need more control over which duplicates to keep, use a COUNTIF formula in a helper column. In a blank column next to your data, type =COUNTIF($A$2:$A, A2) (replace A with your actual column letter and adjust the range to match your data). This formula counts how many times each value appears in that column.
Copy the formula down for every row. Any cell that shows a number higher than 1 is a duplicate. You can then sort by this helper column to group duplicates together, or filter to show only rows where the count is greater than 1.
Once you have identified the duplicates, you can delete the rows you don't want manually. This takes longer than the automatic Remove Duplicates tool, but it gives you the choice of which copy to keep — for example, keeping the most recent entry instead of the first one.
Checking for duplicates across multiple columns
Sometimes you need to find duplicates based on a combination of columns rather than a single column. For instance, you might have a list of people with their names and email addresses, and you want to find cases where the same person appears twice with different email addresses.
Create a helper column with a formula that combines the columns you want to check. Use =A2&B2 to join the Name and Email columns into one cell. Then use COUNTIF on this combined column to find duplicates. Alternatively, use the Remove Duplicates tool and select only the columns that matter — Sheets will treat rows as duplicates only if those specific columns match.
Handling duplicates when you have headers
Always include your header row when selecting data for duplicate checking, but make sure the Remove Duplicates tool knows not to treat the header as data. When you open the Remove Duplicates dialog, check the box that says "Data has headers" at the bottom. This tells Sheets to skip the first row and only check the actual data below it.
If you use conditional formatting, you can select just the data rows without the header, or select everything and accept that the header might get highlighted too — it usually won't be a duplicate unless you have a column named the same as a data value.
What to do after you find duplicates
Once you have identified duplicates, decide whether to delete them or keep them for reference. If you used conditional formatting, the duplicates are still in your sheet — you can manually delete the rows you don't want, or leave them highlighted so you remember they exist.
If you used the Remove Duplicates tool, the extra rows are already gone. If you used a COUNTIF formula, sort or filter your sheet to show only the duplicates, then delete the rows you don't need. After you finish, delete the helper column if you created one.
Frequently Asked Questions
Can I undo Remove Duplicates if I delete the wrong rows?
The Remove Duplicates action cannot be undone with the standard undo button if you close the sheet. Always make a copy of your sheet before using this tool, or press Ctrl+Z immediately if you realize a mistake. If you have already closed the sheet, you will need to restore it from your version history if Google Sheets saved a previous version.
Does conditional formatting remove duplicates or just show them?
Conditional formatting only highlights duplicates — it does not delete anything. Your data stays exactly the same. You have to manually delete the rows you want to remove, or use the Remove Duplicates tool to delete them automatically.
What if I want to keep all duplicates but just see where they are?
Use conditional formatting to highlight them. This shows you every duplicate without changing your data. You can then decide case by case whether to delete each one, or leave them all in place and just know where the duplicates are.
Can I find duplicates in just one column without checking the whole row?
Yes. Select only that column, then use conditional formatting to highlight duplicates. Or use a COUNTIF formula in a helper column next to your data. The Remove Duplicates tool checks entire rows by default, but you can uncheck other columns in the dialog to make it check only the column you care about.
What counts as a duplicate in Google Sheets?
An exact match. "John" and "john" are different because of capitalization. Spaces matter too — "John " with a trailing space is different from "John" without one. If you want to ignore capitalization or extra spaces, clean your data first using formulas like TRIM and LOWER before checking for duplicates.