The fastest way to spot duplicates in a single column

Open your spreadsheet and click the column header to select the entire column you want to check. Go to Format in the menu bar, then Conditional formatting. In the panel that opens on the right, choose Format rules at the top, then select Custom formula is from the dropdown. Paste this formula into the box: =COUNTIF($A$1:$A, $A1)>1 — but replace the A's with your actual column letter if you're checking a different column.

Pick a color for the highlighting — red or yellow stands out — and click Done. Every cell that appears more than once in that column will now be highlighted. This works whether your duplicates are exact matches or just similar enough that you want to review them side by side.

Key Takeaways

  • Conditional formatting with COUNTIF highlights every duplicate in a column automatically, without creating a new column or sorting your data.
  • To find duplicates across two columns at once, use COUNTIFS instead, which counts rows where both columns match an existing row above.
  • The Remove duplicates feature deletes rows permanently, so make a copy of your sheet first if you might need to see what was removed.
  • Sorting your data by the column you're checking groups duplicates together visually, which is faster than highlighting if you have only a few rows.

Highlighting duplicates across multiple columns

If you need to find rows where two or more columns match — for instance, rows with the same name and email address — use COUNTIFS instead. Select the range you want to check, go to Format > Conditional formatting, choose Custom formula is, and use this formula: =COUNTIFS($A$1:$A, $A1, $B$1:$B, $B1)>1. Replace A and B with your actual column letters.

This counts how many times that exact combination appears in the sheet. Any row where the combination shows up more than once gets highlighted. It's useful for catching duplicate customer records or contact entries where the same person signed up twice with slightly different information in other columns.

Removing duplicates permanently

If you want to delete duplicate rows rather than just mark them, Google Sheets has a built-in tool. Select all the data you want to check — including headers — then go to Data in the menu and click Remove duplicates. A dialog box will ask which columns to check. Select the columns that define a duplicate for your purposes, then click Remove duplicates.

Google Sheets will delete every row that matches an earlier row in those columns and show you how many rows were removed. This action cannot be undone, so make a copy of your sheet first if you might need to see what was deleted or if you're unsure which rows should stay.

Sorting to group duplicates visually

For a quick visual check of a small dataset, sorting works faster than conditional formatting. Click the column header you want to sort by, then go to Data > Sort sheet (or Sort range if you've selected specific cells). Choose the column and click Sort A to Z. Identical entries will now sit next to each other, making duplicates obvious at a glance.

This method doesn't highlight anything or delete anything — it just rearranges your rows. If your spreadsheet is already sorted by something else that matters, sorting again will change that order, so use conditional formatting instead if you need to keep your current arrangement.

Finding near-duplicates and typos

Sometimes duplicates aren't exact matches. A person might be listed as "John Smith" in one row and "Jon Smith" in another, or an email might have a typo. Conditional formatting and the remove duplicates tool both require exact matches, so they won't catch these.

The most practical approach is to sort by the column in question, then scan the sorted list yourself. Typos and variations will usually sit close together after sorting, and you can spot them visually. For larger datasets where manual scanning isn't realistic, you could use a formula like FUZZY MATCH (available in Google Sheets), but that requires more setup and is slower. For most housing and personal record-keeping, sorting and visual review is enough.

What to do after you find duplicates

Once you've identified duplicates, decide whether to keep one copy and delete the rest, or whether the duplicates represent different transactions or events that should both stay. For a contact list, you usually want one row per person. For a log of payments or applications, duplicate entries might be legitimate — the same person explore twice or paying twice — and deleting them would lose real information.

If you're keeping one and deleting the rest, use the remove duplicates tool or delete rows manually. If you're keeping both but want to flag them for review, leave the conditional formatting in place so they stay highlighted. Document what you decided and why, especially if someone else will use this spreadsheet later.

Frequently Asked Questions

Will conditional formatting slow down my spreadsheet?

No. Conditional formatting runs once when you set it up and updates automatically as you add or change data. Even with thousands of rows, it won't noticeably slow your sheet down. The highlighting is just a visual layer on top of your data.

Can I highlight duplicates in a column that has blank cells?

Yes. The COUNTIF formula will count blank cells as duplicates if there are multiple blank cells in the column. If you want to exclude blanks, use this formula instead: =COUNTIF($A$1:$A, $A1)>1 and add a second rule that says "is empty" with no formatting, so blanks won't be highlighted.

What happens if I remove duplicates and then undo?

Undo works when ready after you remove duplicates — press Ctrl+Z (or Cmd+Z on Mac) right away and the rows come back. But if you close the sheet and reopen it, undo is no longer available. This is why making a copy first is the safer choice.

Can I find duplicates across different sheets in the same spreadsheet?

Conditional formatting and the remove duplicates tool only work within a single sheet. If you need to check for duplicates across multiple sheets, you would need to copy all the data into one sheet first, check for duplicates there, then move the results back if needed.