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.
Some of the things you will learn
