Excel tutorials

The only Excel books you will ever need

  • The only constantly updated Excel 365 titles more…
  • Thoroughly covers Power Pivot and Power Query more…
  • Covers business intelligence and OLAP features more…
  • Simple, easy, understandable, 2-page lessons more…
  • Available as both printed books and e-Books more…
  • Used by schools, colleges and universities  more…
  • Available for 365, 2021, 2019, 2016 and Mac versions more…

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:
=COUNTIF(A:A,A2)=1

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.

These are the only up-to-date Excel books currently published and includes the new Dynamic Arrays features.

They are also the only books that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.

Share this article

Recent Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Which tutorial do you need?