How to Highlight Duplicates in Google Sheets

If you use Google Sheets regularly like I do for tracking data and making reports, then you know one thing that can get super annoying: duplicates. Whether it’s keywords, emails, or any list you’re managing, hunting down repeated entries by eye is just a waste of time and patience.

That’s why knowing how to highlight duplicates in Google Sheets quickly became a lifesaver for me. Instead of scrolling endlessly or double-checking manually, you can use a simple formula with Conditional Formatting and let Google Sheets do the heavy lifting.

The Quick Way: Conditional Formatting

Good news: you don’t need any add-ons or complicated scripts. Google Sheets already has a built-in feature called Conditional Formatting that lets you highlight duplicates easily.

If you’ve never used it, think of it as setting a rule that says: “If this value appears more than once, highlight it.” This rule is written as a formula that you enter in Conditional Formatting.

Here’s the step-by-step process coming up next.

Step-by-Step: Highlight Duplicates Using a Formula

Let’s get to the part where you actually highlight duplicates using the COUNTIF formula inside Conditional Formatting.

🛠️ Steps:

1. Open your Google Sheet
   Go to the file where you want to find duplicates.
2. Select the range
   Highlight the cells you want to check, for example A1:A100 if it’s just one column.
3. Go to Format > Conditional formatting
   A panel will appear on the right side.
4. Under “Format cells if…” select Custom formula is
5. Paste this formula:

Make sure to replace A:A and A1 if you’re working with another column like B:B or starting from a different cell.

1. Choose a formatting style
   Pick a highlight color that stands out (I usually go with light red or yellow).
2. Click “Done”
   Now, any duplicate entries in your selected range will be highlighted automatically.

What the Formula Actually Means

At first, the formula might look like magic, but here’s the simple breakdown:

• COUNTIF counts how many times a value appears in a range.
• A:A means the entire column A, where Google Sheets looks for duplicates.
• A1 is the current cell being checked.
• The formula asks: “Is this value found more than once in the column?” If yes, it’s a duplicate and gets highlighted.

What’s neat is the formula adjusts automatically for each cell in your selected range, checking each one against the whole column.

Bonus: Highlight Duplicates Across Multiple Columns

Sometimes your data spans multiple columns, such as names in one column and emails in another, and you want to find duplicates across both.

Here’s how you do that:

1. Select the full range
   For example, if your data covers columns A and B from rows 1 to 100, select A1:B100.
2. Open Conditional Formatting and again choose Custom formula is.
3. Use this formula:

The dollar signs $ lock the range so it stays fixed for all cells.

1. Pick your highlight color and click “Done.”

Now, Google Sheets will check both columns together and highlight any value that appears more than once anywhere in that range.

Extra Tip: Remove or Filter Duplicates

Highlighting duplicates is great to spot them, but sometimes you want to actually clean up your sheet or focus on unique entries.

Here are two quick ways to do that:

Remove Duplicates

Google Sheets offers a simple tool to remove duplicates fast:

• Select your data range.
• Go to Data > Data cleanup > Remove duplicates.
• Choose which columns to check and click Remove duplicates.
• Sheets will tell you how many duplicates it removed.

Filter Duplicates

If you want to filter and see only duplicates:

• Use the same Conditional Formatting formula to highlight duplicates.
• Add a filter to your columns.
• Filter by color to show only highlighted (duplicate) cells.

This way, you can easily remove or work with duplicates without sorting manually.

Final Thoughts

Duplicates used to make me want to throw my laptop. But now? One formula and a quick highlight rule, and I’m done. Google Sheets actually makes it easy once you know where to click and what to paste.

This trick saves me every time I’m cleaning up long lists, fixing imports, or just trying to spot mistakes. If your data lives in Sheets, you’ll want this in your back pocket. If you ever need to pull matching info from another sheet, check out my VLOOKUP guide. It pairs really well with this.

Anyway, hope this made your spreadsheet life a little easier. Let me know if there’s another annoying problem I should write a fix for.