How to highlight duplicates in Google Sheets
Learn how to highlight duplicate values in Google Sheets using conditional formatting and COUNTIF, covering single columns, rows, and multiple columns.
Quick answer
Select the range you want to check, then open Format > Conditional formatting. Under Format cells if, choose Custom formula is and enter =COUNTIF($A$2:$A$10,A2)>1, replacing the range with your own. Pick a highlight color under Formatting style, click Done, and every duplicate cell fills with that color instantly.
Steps at a glance
- Select the data range you want to check, excluding headers
- Open Format > Conditional formatting from the menu
- Choose Custom formula is under Format cells if
- Enter a COUNTIF formula referencing your selected range
- Pick a highlight color in the Formatting style section
- Click Done to apply the rule
- Adjust the formula to check rows or multiple columns
Summary
Highlighting duplicates in Google Sheets uses conditional formatting paired with a COUNTIF formula to automatically color any repeated value in a range. This makes it easy to spot data entry errors, duplicate contacts, or repeated order numbers without manually scanning every row.
Step-by-step guide
Step 1
Select the range to check
Click and drag to select the column or range of cells you want to scan for duplicates. Exclude header rows so labels like "Name" or "Email" don't get flagged as repeats.
Step 2
Open Conditional formatting
Go to Format in the top menu, then click Conditional formatting. A panel opens on the right side of the screen where you'll build the highlighting rule.
Step 3
Choose Custom formula is
In the Format cells if dropdown, scroll down and select Custom formula is. This option lets you write a COUNTIF formula instead of relying on a preset rule.
Step 4
Enter the COUNTIF formula
Type =COUNTIF($A$2:$A$10,A2)>1 into the formula box, adjusting the range and cell reference to match your data. The dollar signs lock the range so it doesn't shift as the formula checks each row. If you want to review COUNTIF logic before applying it, see how to use COUNTIF for a closer look at how the function works.
Step 5
Pick a highlight color
Under Formatting style, choose a background color, text color, or bold formatting to make duplicates stand out. A bright fill color like yellow or red usually works best for quick visual scanning.
Step 6
Click Done to apply the rule
Click Done at the bottom of the panel. Every cell matching the formula's condition, meaning it appears more than once in the range, updates with your chosen highlight immediately.
Step 7
Extend the rule to rows or multiple columns
To highlight entire duplicate rows, select the full data range first, then use a formula like =COUNTIF($A$2:$A$10,$A2)>1 with a locked column reference. This checks column A but applies the highlight across each selected row. You can combine this with sorting a table afterward to group duplicates together visually.
Why this matters
You're reviewing a signup list, expense log, or order sheet and suspect the same entry got added twice. Instead of scrolling row by row, highlighting duplicates flags every repeat instantly, so you can clean up data, catch billing errors, or confirm a list is accurate before sharing it with your team.
Frequently asked questions
Can I highlight duplicates without deleting them?
Yes. Conditional formatting only changes how duplicate cells look; it doesn't remove or alter any data. If you later decide to clean up the list, you can separately use the remove duplicates tool to delete repeated rows.
How do I highlight only the second occurrence of a duplicate?
Use a formula like =COUNTIF($A$2:A2,A2)>1 without locking the second reference's row. This counts occurrences only up to the current row, so the first instance stays unhighlighted while repeats are flagged.
Does this work across multiple columns at once?
Yes, by combining COUNTIF with addition, such as =COUNTIF($A$2:$A$10,A2)+COUNTIF($B$2:$B$10,A2)>1, though it's often clearer to check one column at a time or use a helper column that combines values first.
Is this different from just finding duplicates?
Highlighting duplicates visually marks repeats in place using color, while finding duplicates typically means filtering or listing them separately. Both methods rely on similar COUNTIF logic, so you can use whichever fits your workflow.
Create interactive demos like this one — free, no coding required.
Start for free →