Data Validation Unique Values Only: How to Do It
- Average speed
- km/h
- Result
- Finish time
- 50 min
- You entered
- Distance
- 10 km
- You entered
Data validation with unique values only prevents duplicate entries in a spreadsheet cell range, such as Excel or Google Sheets. You set a rule that rejects any input already present in the specified range, forcing each entry to be distinct. This guide explains how to set it up, the limitations, and workarounds for common problems.
Formula
The validation rule uses a COUNTIF formula to check whether a value already exists in the range. The general formula is:
=COUNTIF(range, criteria) = 0
Where:
- range – the cells to check for duplicates (e.g.,
$A$2:$A$100). - criteria – the cell being validated (e.g.,
A2).
The rule allows the entry only if the count of matching values is zero. For a range that includes the current cell, use =COUNTIF($A$2:$A$100, A2)=1 to allow the first occurrence.
Worked Example
Suppose you have a list of email addresses in cells A2:A10 and you want to prevent duplicates. Select A2:A10, go to Data Validation, choose “Custom formula is” (Google Sheets) or “Custom” (Excel), and enter:
=COUNTIF($A$2:$A$10, A2)=1
If you type john@example.com in A2, it is allowed because the count is 1. If you type the same email in A5, the count becomes 2, so the validation rejects it.
How to Set Up Unique Validation in Excel
- Select the range where you want to enforce uniqueness (e.g., A2:A100).
- Go to the Data tab and click Data Validation.
- Under “Allow”, choose Custom.
- In the formula box, enter
=COUNTIF($A$2:$A$100, A2)=1(adjust the range to match your selection). - Optionally, set an input message and error alert (e.g., “Duplicate entry not allowed”).
- Click OK.
How to Set Up Unique Validation in Google Sheets
- Select the range (e.g., A2:A100).
- Click Data → Data validation.
- Under “Criteria”, choose Custom formula is.
- Enter
=COUNTIF($A$2:$A$100, A2)=1. - Choose whether to show a warning or reject the input.
- Click Save.
Common Problems and Limitations
- Blank cells: The formula counts blanks as duplicates. To ignore blanks, use
=OR(A2="", COUNTIF($A$2:$A$100, A2)=1). - Case sensitivity: COUNTIF is not case-sensitive, so “John” and “john” are treated as duplicates. For case-sensitive uniqueness, use a more complex array formula (e.g.,
=SUMPRODUCT(--EXACT($A$2:$A$100, A2))=1in Excel). - Pasted data: Validation only applies to manual typing. Pasting a block of data bypasses the rule, so duplicates can still be pasted.
- Existing duplicates: Validation does not flag or remove duplicates already in the range. Use “Remove Duplicates” or conditional formatting to clean existing data.
- Partial ranges: If you add rows below the validated range, they are not covered. Extend the range or use a table (Excel) where new rows inherit validation.
Alternatives to Data Validation for Uniqueness
- Conditional formatting: Highlight duplicates with a formula like
=COUNTIF($A$2:$A$100, A2)>1to visually identify them, but it does not block entry. - Remove Duplicates: A one-time tool to delete duplicate rows (Excel: Data tab → Remove Duplicates; Sheets: Data → Data cleanup → Remove duplicates).
- UNIQUE function: In Excel 365 and Google Sheets,
=UNIQUE(A2:A100)creates a separate list of unique values, but does not enforce uniqueness in the original range. - Table with calculated column: In Excel, you can add a helper column with a formula that flags duplicates, then filter or sort.
Frequently Asked Questions
Why does my unique validation allow duplicates?
The most common cause is an incorrect range reference. If the range in the COUNTIF formula does not include the cell being validated, or if it uses relative references (like A2 instead of $A$2), the rule may not work as expected. Also, pasting data bypasses validation entirely.
Can I make the validation case-sensitive?
Yes, but it requires a different formula. In Excel, use =SUMPRODUCT(--EXACT($A$2:$A$100, A2))=1. In Google Sheets, use =ARRAYFORMULA(SUM(--EXACT($A$2:$A$100, A2)))=1. These treat “John” and “john” as different.
Does data validation apply to new rows added below the range?
No, unless you convert the range to a table (Excel) or extend the validation range manually. In Excel, if you use a table, new rows automatically inherit the validation rule. In Google Sheets, you must update the range or use a script.
How do I ignore blank cells in the unique rule?
Use a formula that allows blanks: =OR(A2="", COUNTIF($A$2:$A$100, A2)=1). This way, empty cells are not counted as duplicates, so you can leave blanks without triggering an error.
Can I use unique validation across multiple columns?
Yes, but the formula must reference the combined range. For example, to ensure that the pair of values in columns A and B is unique, use =COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2)=1. This checks both columns together.
Related
Formula used
in t | Finish time | min | 50 | 0 in d | Distance | km | 10 | 0 out pace | Average pace | min/km | t / d | 2 out speed | Average speed | km/h | d / (t / 60) | 2
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.