Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to highlight duplicate values in Excel and Google Sheets
Topic:Conditional formattingExcel & Google Sheets
=COUNTIF($A$2:$A$6,A2)>1✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Name |
| Alice |
| Bob |
| Alice |
| Carol |
| Bob |
=COUNTIF($A$2:$A$6,A2)>1→TRUE
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Select the range you want to check (e.g., A2:A6).
- 2Open Conditional Formatting: Home → Conditional Formatting → New Rule (Excel) or Format → Conditional Formatting (Sheets).
- 3Choose 'Use a formula' and enter =COUNTIF($A$2:$A$6,A2)>1 — lock the range but leave the cell reference relative so each row checks itself.
Tips
- Excel also has a built-in shortcut: Home → Conditional Formatting → Highlight Cell Rules → Duplicate Values — it applies the same logic without typing a formula.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
How do I highlight only the second occurrence, not the first?
Use =COUNTIF($A$2:A2,A2)>1 with a mixed reference — the range expands row by row, so the first occurrence sees a count of 1 and is not highlighted.
Can I highlight duplicates across two columns?
Yes — expand the COUNTIF range to cover both columns: =COUNTIF($A$2:$B$6,A2)>1.
More on Conditional formatting
See all →How-to
How to highlight cells greater than a value
Use Conditional Formatting with a simple greater-than rule to automatically color cells that exceed a threshold value.
How-toHow to highlight the highest value in a range
Color the maximum value in a range automatically using a Conditional Formatting rule based on the MAX function.
How-toHow to highlight the lowest value in a range
Color the minimum value in a range using a Conditional Formatting rule built on the MIN function.
How-toHow to highlight blank cells
Use conditional formatting with ISBLANK to automatically highlight empty cells in your spreadsheet for easy visual identification.
How-toHow to highlight cells that contain specific text
Use conditional formatting with SEARCH or ISNUMBER(SEARCH()) to highlight any cell containing a specific word or phrase automatically.
How-toHow to shade every other row
Use conditional formatting with MOD and ROW to automatically apply alternating row colors for a banded, readable table layout.