The Smart Method Logo

Publishers of the world’s most comprehensive and up-to-date Excel tutorials


Applying a unique constraint in Excel

Excel is often used as a data entry tool. It’s possible to prevent many data entry errors by using Excel’s built-in validation feature.

Validation rules are covered in depth in our Expert Skills course.

You can achieve a great deal with simple validation rules, but it’s often the case that you want to impose a unique constraint; that is, you don’t want the user to be able to enter the same thing more than once. You can do this by using a formula-based validation rule.

The formula to impose a unique constraint is something like:

Leaving the Ignore blank box checked will allow blank entries to bypass the validation so you can create a unique list with gaps between items. If you’d rather prevent multiple blank items from being added, uncheck Ignore blank.

Validation rules are processed at the point when the user enters some data. This rule uses the COUNTIF function to see how many items in column A are the same as what the user just entered. There should only be one match if there aren’t any duplicate entries in the column, so checking for =1 ensures that duplicates can’t be entered.

The COUNTIF function is also covered in more depth in the Expert Skills course.

Example workbook

You can download an example workbook showing this validation in action.

Share this article

Share on facebook
Share on twitter
Share on linkedin
Share on stumbleupon
Share on email

Related Articles


Understand XLOOKUP (part two)

note In most cases, the binary search option is not useful The XLOOKUP function uses a sophisticated linear search method by default. It is my


Understand XLOOKUP (part one)

note XLOOKUP will not work in legacy Excel versions (Excel 2019 and earlier) The XLOOKUP function was added in the Jul 2020 semi-annual version 2002.

Leave a Reply

Your email address will not be published.

fifteen + fifteen =