Google Sheets doesn’t have a one-click “highlight duplicates” button like Excel, but conditional formatting with a custom formula does the job, and it’s more flexible. You can highlight any repeated value across several columns, flag entire rows that are duplicated, compare one column against another, or mark only the second and later copies. This guide gives you the exact formula for each case.
Quick Answer
Select your range (for example A2:C100), go to Format > Conditional formatting, set Format cells if to Custom formula is, and enter =COUNTIF($A$2:$C$100,A2)>1. Pick a fill color and click Done. Every value that appears more than once anywhere in the range is highlighted.
Which Formula Do You Need?
| Goal | Apply to range | Custom formula |
|---|---|---|
| Any repeated value across columns A to C | A2:C100 | =COUNTIF($A$2:$C$100,A2)>1 |
| Whole row, when columns A and B both match another row | A2:C100 | =COUNTIFS($A$2:$A,$A2,$B$2:$B,$B2)>1 |
| Whole row, when column A repeats | A2:D100 | =COUNTIF($A$2:$A,$A2)>1 |
| Only 2nd and later copies (keep the first unhighlighted) | A2:A100 | =COUNTIF($A$2:$A2,$A2)>1 |
| Values in column A that also appear in column B | A2:A100 | =COUNTIF($B$2:$B,A2)>0 |
| Case-sensitive duplicates | A2:C100 | =SUMPRODUCT(–EXACT($A$2:$C$100,A2))>1 |
Step by Step: Highlight Duplicates Across Multiple Columns
- Select the cells to check, such as A2:C100. Leave out the header row.
- Click Format > Conditional formatting. The rules panel opens on the right.
- Confirm the range in Apply to range.
- Under Format cells if, choose Custom formula is (at the bottom of the list).
- Type =COUNTIF($A$2:$C$100,A2)>1.
- Under Formatting style, pick a fill color.
- Click Done.
How the formula works
Conditional formatting checks the formula for each cell, starting with the top-left cell of the range. COUNTIF($A$2:$C$100,A2) counts how many times the current cell’s value appears in the whole block. The dollar signs lock the block so it doesn’t shift, while A2 has no dollar signs so it moves to each cell in turn. If the count is more than 1, the cell is a duplicate and gets the color.
The formula must reference the first cell of your range. If your range starts at B3, use B3 in the formula.
Highlight Entire Duplicate Rows
To highlight rows where several columns match, like the same name and the same plan, use COUNTIFS with one criteria pair per column:
=COUNTIFS($A$2:$A,$A2,$B$2:$B,$B2)>1
Apply it to the full width of your table (A2:C100). The dollar sign before each column letter ($A2, $B2) keeps every cell in the row looking at columns A and B, so the whole row lights up. Add more pairs, like $C$2:$C,$C2, to require more columns to match.
Highlight Only the Repeats, Not the First Occurrence
If you plan to delete extras, it helps to leave the original unhighlighted. Use an expanding range:
=COUNTIF($A$2:$A2,$A2)>1
For each row, this counts matches only from the top down to that row, so the first copy counts 1 (not highlighted) and later copies count 2 or more.
Compare Two Columns for Matches
To highlight names in column A that also appear in column B, select A2:A100 and use =COUNTIF($B$2:$B,A2)>0. Change it to =0 to highlight values that are missing from column B. To compare with another tab, conditional formatting needs INDIRECT, for example =COUNTIF(INDIRECT(“Sheet2!A2:A”),A2)>0.
Common Problems
- Blank cells are highlighted: Wrap the formula to ignore blanks: =AND(A2<>””,COUNTIF($A$2:$C$100,A2)>1).
- Obvious duplicates aren’t highlighted: Extra spaces or different formatting (like “100” stored as text vs. number) make values different. Use Data > Data cleanup > Trim whitespace, then check again.
- Nothing is highlighted, or the wrong cells are: Check that the formula’s first cell matches the range’s first cell, and that dollar signs are in the right places.
- “Apple” and “apple” count as duplicates: COUNTIF ignores case. Use the SUMPRODUCT/EXACT formula for case-sensitive matching.
- Slow sheet: Use bounded ranges like A2:C1000 instead of whole columns in large files.
Next Step: Remove or List Duplicates
- Remove them: Select the data and choose Data > Data cleanup > Remove duplicates, then pick the columns to compare. This deletes rows, so make a copy of the sheet first.
- List unique values: In an empty column, enter =UNIQUE(A2:C100).
- Count copies: Add a helper column with =COUNTIF($A$2:$A,A2) and sort or filter by it.
Related: How to Count in Google Sheets.
Related: Highlight in Google Sheets.
Frequently Asked Questions
Can I highlight duplicates in Google Sheets on my phone?
Yes. In the Android app, select the cells, tap Format (A icon) > Conditional formatting, and choose Custom formula is. The iPhone app’s conditional formatting options are more limited, so a browser is easier.
Can I use different colors for different duplicates?
Not automatically. Each rule has one style. You can add multiple rules, for example one for values appearing twice and another for three or more times (>2).
Summary
- Select the range and open Format > Conditional formatting.
- Choose Custom formula is.
- Use COUNTIF for any repeated value, or COUNTIFS for duplicate rows.
- Pick a color and click Done.
Kermit Matthews is a freelance writer based in Philadelphia, Pennsylvania with more than a decade of experience writing technology guides. He has a Bachelor’s and Master’s degree in Computer Science and has spent much of his professional career in IT management.
He specializes in writing content about iPhones, Android devices, Microsoft Office, and many other popular applications and devices.