Overview
Use conditional formatting with a COUNTIF formula to highlight duplicate values in Google Sheets. Select your range, go to Format → Conditional formatting, choose “Custom formula is,” and enter =COUNTIF($A$2:$A,A2)>1. This counts how many times each value appears; if it appears more than once, that cell gets highlighted. The method works for single columns, multiple columns using COUNTIFS, and can be adapted for blanks, normalized text, and cross-sheet checks.
Duplicate entries in spreadsheets create confusion and can lead to inaccurate analysis or data inconsistency. Before you apply any highlighting rule, understanding what duplicates mean in your context—a repeated value, a full duplicate row, or a duplicate key pair—is essential. This guide covers the baseline conditional formatting method, formula variations for real-world scenarios, a comparison with other cleanup approaches, troubleshooting when rules go wrong, and a concrete worked example for imported lead lists.
Quick answer: highlight duplicates with conditional formatting
The fastest way to highlight duplicates is to select your target range, then use conditional formatting with a COUNTIF formula. Here’s the standard approach:
- Select the range you want to check (e.g., column A with data starting in A2).
- Go to Format → Conditional formatting.
- Choose Format rules → Custom formula is.
- Enter the formula
=COUNTIF($A$2:$A,A2)>1(adjust column and starting row to match your data). - Set a fill color or text format for highlighted cells.
- Click Done.
Any cell in the selected range that appears more than once will now be highlighted.
Single-column formula
The core formula structure is =COUNTIF($A$2:$A,A2)>1. Here’s what each part does: $A$2:$A is your absolute reference to the range being checked—it stays the same as the formula copies down. The dollar sign before the column (A) locks the column, while the dollar before the row (2) locks the starting row. A2 is the current row being compared, and it changes as the formula moves down (A3, A4, etc.). The >1 means “flag this cell if it appears more than once in the range.”
To adapt this formula for a different column or starting row, change $A$2:$A to your actual column and row. For example, if your emails are in column C starting at row 3, use =COUNTIF($C$3:$C,C3)>1.
What the formula is checking
COUNTIF is a Google Sheets function that counts how many cells in a range match a criteria. In this case, it counts how many times each value appears in the entire range. If the count is greater than 1, the formula returns TRUE, and the cell is formatted. If the count is 1 or 0, the formula returns FALSE, and no formatting is applied.
This method is case-insensitive by default—ABC and abc will be counted as the same value and both highlighted if they appear more than once. If you need case-sensitive duplicate detection, you’ll need a more complex formula or a helper column, which is covered later in this guide.
Choose the right duplicate rule before you format
Before you write any formula, clarify what duplicates mean in your spreadsheet. Different scenarios call for different duplicate definitions, and using the wrong one will highlight the wrong cells.
Repeated value, duplicate row, or duplicate key
Three common duplicate meanings require different formulas:
- Repeated value: The same value appears multiple times in a single column. For example, an email address
jane@example.comappears in rows 2, 5, and 7 of the Email column. Use COUNTIF for this. - Duplicate row: An entire row is repeated. For example, two invoices with the same Invoice ID, Date, and Amount. Use a multi-column formula like COUNTIFS or a helper approach.
- Duplicate key: A combination of columns that should be unique is repeated. For example, the same Customer ID and Order ID together appear twice (but the Order ID might repeat in other customers, which is fine). Use COUNTIFS to check only the key columns.
In an imported lead list, you might treat duplicate emails as problematic (repeated value), but duplicate names as acceptable (many people share names). Setting the right duplicate definition prevents false positives.
Formula cookbook for common duplicate scenarios
This section provides copyable formulas for the most common duplicate-highlighting needs. Each formula is ready to paste into a conditional formatting rule.
Ignore blanks and headers
If your data includes blank cells or a header row, use a formula that skips them. Blanks are often highlighted by mistake when using a simple COUNTIF. To avoid highlighting blanks:
=AND(A2<>"",COUNTIF($A$2:$A,A2)>1)
This formula checks two things: the current cell is not blank (A2<>""), AND the value appears more than once. Adjust the column and starting row to match your data. This is especially useful for imported files where some entries may be incomplete.
Highlight duplicate rows based on multiple columns
When you want to flag entire rows where a combination of columns repeats, use COUNTIFS. For example, to highlight records where both Email and Company match:
=COUNTIFS($A$2:$A,A2,$B$2:$B,B2)>1
Here, $A$2:$A and $B$2:$B are your key columns. The formula counts rows where both the Email (column A) and Company (column B) match the current row. If the count is greater than 1, that row is highlighted. Adjust the columns and range to include all your key columns.
Highlight only second and later occurrences
If you want to highlight duplicates but leave the first occurrence unmarked, use an expanding range:
=COUNTIF($A$2:A2,A2)>1
Notice the difference: the first part of the range is absolute ($A$2), but the second part (A2) is relative and expands as the formula moves down. When the formula reaches row 5, it becomes =COUNTIF($A$2:A5,A5)>1. This means the first occurrence of a value (where only it exists in the range from $A$2 to that row) returns FALSE and is not highlighted. The second and later occurrences return TRUE and are highlighted.
Normalize spaces and capitalization
Messy imported data often has extra spaces, inconsistent capitalization, or hidden characters that make duplicate detection unreliable. A simple COUNTIF can miss these. The cleanest approach is to use a helper column, but you can also build it into your formula.
For text normalization, apply LOWER and TRIM inside COUNTIF:
=COUNTIF(ARRAYFORMULA(LOWER(TRIM($A$2:$A))),LOWER(TRIM(A2)))>1
This converts all values to lowercase and removes leading/trailing spaces before counting. However, ARRAYFORMULA is not always stable in conditional formatting. A more reliable method is to create a helper column (column D) with the formula =LOWER(TRIM(A2)), copy it down, and then use standard COUNTIF on the helper column. This gives you full control and works reliably in conditional formatting.
Check duplicates across another sheet
To highlight duplicates that also appear in another sheet or tab, use a COUNTIF that references the other sheet:
=COUNTIF(OtherSheet!$A$2:$A,A2)>1
Replace OtherSheet with the actual name of your other sheet (use single quotes if the sheet name has spaces: 'Other Sheet'!$A$2:$A). This highlights cells in the current sheet that appear anywhere in column A of the other sheet.
Caution: If the sheet name or range changes later, the formula breaks silently. For recurring or shared sheets, document which sheet and range this rule depends on. An alternative is to use named ranges, which are easier to update if the source range moves.
Decision matrix: conditional formatting vs cleanup and automation options
When you have duplicates, you have several paths forward. Visual highlighting is just one. Here’s how they compare:
| Method | Best Use Case | Reversible | Setup Effort | Auditability | Maintenance |
|---|---|---|---|---|---|
| Conditional Formatting | Review before deciding to delete; visual flag; no data loss. | Yes (delete the rule) | 5 minutes | High (rule is visible and can be inspected) | Low; rule stays stable unless sheet structure changes |
| Remove Duplicates | One-time cleanup after review; known duplicate definition. | Partial (deleted rows are gone, but you can undo once) | 2 minutes | Medium (removed rows leave no trace) | None; one-time action |
| Add-ons | Recurring imports; complex duplicate rules; GUI instead of formulas. | Depends on the add-on | 10–20 minutes | Medium (depends on add-on logging) | Depends on add-on stability |
| Apps Script | Scheduled duplicate checks; conditional deletion; complex logic. | Depends on script | 30 minutes to hours | Medium (if logged) | Requires script maintenance |
| Manual review + cleanup | Small datasets; uncertain duplicate legitimacy. | Yes (undo) | Time-dependent | Highest (manual notes in adjacent column) | Ongoing if data recurs |
When visual highlighting is enough
Conditional formatting is the safest first step. Use it when you want to inspect duplicates without changing the data, when you’re unsure whether repeats are legitimate, or when you need to document what duplicates exist before making any deletion decision.
After highlighting, review the flagged cells. Ask: Is this repeat a data entry error, a legitimate duplicate transaction, or expected behavior (like the same customer appearing twice)? Only after answering these questions should you decide to delete or keep records.
When to remove, script, or automate
Once you’ve reviewed highlighted duplicates and confirmed they should be deleted, use the Remove Duplicates tool (Data → Data cleanup → Remove duplicates). This is faster and simpler than conditional formatting for one-time cleanup.
For recurring imports or complex duplicate rules, consider Apps Script or an add-on. Apps Script can run on a schedule, delete duplicates automatically, and log what was removed. However, automation introduces risk: if your duplicate definition is wrong, you might delete records you wanted to keep. Always test on a copy first.
Troubleshooting duplicate highlighting rules
Conditional formatting rules can go wrong in subtle ways. Here are the most common issues and how to fix them.
Wrong cells, every cell, or no cells highlighted
This usually means one of three things: the apply-to range is wrong, the formula references are locked incorrectly, or blank cells are being counted.
- No cells highlighted: Check that your apply-to range starts on the same row as the formula. If your formula is
=COUNTIF($A$2:$A,A2)>1, your apply-to range should beA2:A1000or similar, notA1:A1000(which includes the header row). - Every cell highlighted: Your apply-to range might be wider than your data range, or you might be checking an empty column. Narrow the range to only include data-bearing rows and columns.
- Wrong cells highlighted: Verify that the column letter in your formula matches the column of your apply-to range. If you select column B but your formula references column A, misalignment occurs.
- Blanks highlighted: Use
=AND(A2<>"",COUNTIF($A$2:$A,A2)>1)to skip blank cells.
Locale separators and formula syntax
Google Sheets respects your locale settings for formula syntax. Some locales use semicolons instead of commas to separate formula arguments. For example, in European locales, you might need to write =COUNTIF($A$2:$A;A2)>1 instead of =COUNTIF($A$2:$A,A2)>1. Google Sheets usually auto-converts based on your locale, but if a formula is rejected, try the alternate separator.
Large sheets and expanding ranges
Whole-column references like $A:$A are convenient but can be harder to maintain in large or collaborative sheets. If your sheet has thousands of rows or multiple editors, use a bounded range like $A$2:$A10000 instead. This makes it explicit exactly which rows are being checked and makes the rule easier to audit and update.
When new data is added, check whether the new rows fall within your apply-to range. If you have 1,000 rows of data but your conditional formatting range is only A2:A500, new duplicates won’t be highlighted.
Worked example: duplicate email cleanup in an imported lead list
Here’s a realistic scenario: you’ve imported a CSV of leads from a marketing campaign. The spreadsheet has Email, Company, Contact Name, Source, and Import Date. You want to find duplicate emails and decide whether to merge, delete, or keep them.
Sample columns and formulas
Below is a small sample of what your imported data might look like:
| Company | Contact Name | Source | Import Date | |
|---|---|---|---|---|
| jane@acme.com | Acme Corp | Jane Doe | 2025-01-15 | |
| john@beta.io | Beta Inc | John Smith | Email List | 2025-01-15 |
| jane@acme.com | Acme Corp | Jane Doe | Company Website | 2025-01-18 |
| alice@gamma.net | Gamma Ltd | Alice Johnson | 2025-01-18 | |
| john@beta.io | Beta Inc | J. Smith | Email List | 2025-01-20 |
Notice that jane@acme.com appears in rows 2 and 4, and john@beta.io appears in rows 3 and 5. To highlight these duplicates:
- Select the Email column (A2:A1000).
- Go to Format → Conditional formatting → Custom formula is.
- Enter
=COUNTIF($A$2:$A,A2)>1. - Choose a fill color (e.g., light yellow).
- Click Done.
Now jane@acme.com and both instances of john@beta.io are highlighted.
If your imported emails have extra spaces or inconsistent capitalization (e.g., Jane@ACME.com), create a helper column (column F) with =LOWER(TRIM(A2)), copy it down, and then highlight based on column F using =COUNTIF($F$2:$F,F2)>1. This ensures that jane@acme.com, JANE@ACME.COM, and jane @acme.com are all recognized as the same duplicate.
Review before deleting anything
After highlighting, manually review the results:
- Are the duplicates data entry errors or legitimate re-contacts (same person from different sources)? If legitimate, merge the rows to preserve the history (e.g., combine Source and Import Date into a note).
- Are some duplicate emails acceptable? (Maybe they are, if your lead list is expected to include multiple contacts from the same company.)
- Do any duplicates need to be kept because they represent different contact attempts or updates?
Only after this review should you delete or consolidate records. Use the Remove duplicates tool if you decide to delete, or manually merge rows if you want to preserve information.
Practical next steps for shared or recurring sheets
If your duplicate-highlighting rule is part of a recurring workflow (weekly imports, shared team maintenance), a few practices help prevent silent rule failures and keep the sheet maintainable.
Document the rule and protect ranges
Add a comment or note to your sheet explaining what each conditional formatting rule checks. For example, add a cell note (right-click → Insert note) to column A saying: “Rule: Highlight duplicate emails (case-insensitive, blanks ignored). If this rule breaks after a structural change, contact [your name].”
If the rule is critical to your workflow, protect the range where it’s applied so that other editors don’t accidentally delete rows or columns that would break the rule. Go to Data → Protect sheets and ranges and set it to “Show a warning” or “Restrict edits.”
Name your key columns using Data → Named ranges. Instead of referencing $A$2:$A in your formula, use a named range like EmailList. If the column is ever moved, the named range automatically updates, and your conditional formatting rule keeps working.
Prepare cleaned datasets for downstream use
After reviewing and cleaning duplicates in your sheet, you may want to share the final dataset with others or prepare it for analysis and reporting. When your dataset is clean and deduplicated, you can upload it to tools designed for dataset sharing and exploration.
For example, platforms like TablePage let you upload a cleaned CSV or XLSX file, generate a public interactive page with filterable tables and charts, and share it with no signup required. This is useful if you’re publishing a lead list, survey results, or research data that other team members or external stakeholders need to explore. By highlighting and removing duplicates before upload, you ensure the shared dataset is trustworthy and easy to interpret.
If you’re preparing recurring reports or datasets, consider automating the duplicate-checking step with Apps Script or a scheduled workflow so that cleaned data is always ready to share.