Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to prevent duplicate entries in a column in Excel and Google Sheets
Excel & Google Sheets
=IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","Unique")✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| ID | Status |
| 101 | Unique |
| 102 | Unique |
| 101 | Duplicate |
| 103 | Unique |
| 104 | Unique |
=IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","Unique")→Unique
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
- 1Enter your data into a column, starting from row 2.
- 2In the cell next to your first data entry (B2), enter the formula provided for Excel or Google Sheets.
- 3Drag the fill handle down to apply the formula to other cells in the column.
- 4Cells with duplicate entries will now display 'Duplicate', while unique entries will show 'Unique'.
Tips
- Ensure your formula starts from the second row to avoid counting the header as a duplicate.
- Adjust the range in the COUNTIF function if your data starts in a different row.
- This method highlights duplicates but does not prevent them from being entered. Consider using data validation for stricter control.
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
What if I want to highlight duplicates instead of labeling them?
Use conditional formatting with the formula =COUNTIF($A$2:A$100,A2)>1 to highlight duplicate cells.
Can this be used for multiple columns?
Yes, adjust the formula to reference the specific column you want to check for duplicates.
Does this work with text and numbers?
Yes, the COUNTIF function works with both text and numeric data.