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:

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.

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.

Quick recap

Select your range, use Conditional Formatting with COUNTIF, and pick a clear highlight color. For multiple columns, lock the full range with dollar signs. To finish the job, use Remove duplicates or filter by color. Keep this flow handy and your lists stay clean.