Essential to Expert Skills progression

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, 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.

Share this article

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

Recent Articles

Excel protected view warning

Excel protected view: Be careful – files from the Internet can contain viruses. Unless you need to edit it’s safer to stay in Protected View. This article explains what this error message means.

VLOOKUP inexact match illustration (chicken and egg).

VLOOKUP inexact match

VLOOKUP lesson with sample file that will teach you everything there is to know when creating a VLOOKUP inexact match Excel function.

VLOOKUP exact match

Simple VLOOKUP tutorial with sample file that will teach you everything there is to know when creating a VLOOKUP function with an exact match.

Clients

When you use The Smart Method you’re in good company Here are some of the companies that The Smart Method have helped empower with excellent

Microsoft Office update channels

Microsoft Office update channels are explained in this article. Beta, Current, Monthly Enterprise and Semi-annual enterprise are all covered.

Leave a Reply

Your email address will not be published.

5 × three =