How to create Excel data validation for unique values

by | Jul 9, 2018 | Excel Tips & Tricks

How to create data validation for unique values

Entering data into an Excel file manually carries the risk of incorrect entries. Using Excel data validation feature reduces and in many cases complete eliminates this risk. In this article, we are going to show how to combine COUNTIF function with Data Validation feature.

Syntax

=COUNTIF( absolute reference of list, relative reference of first item in the input list) < 2

Steps

  1. Select the data list that should contain all entries
  2. Click Data Validation icon under DATA tab in ribbon
  3. Select Custom in Allow dropdown
  4. In Formula section type the validation formula
  5. Start with =COUNTIF(
  6. Select or type the absolute reference list that contains values $C$5:$C$10,
  7. Select or type the first cell of the relative reference list C5)
  8. Type <2 to limit the count of values to 1
  9. Click OK to save the validation rule

How

Excel Data Validation feature is a great tool to ensure that users enter the correct value. As well as its preset options to restrict user entries, the tool allows you to create your own lists by using formulas. Selecting Custom option in Allow dropdown shows the Formula input that you can enter your own validation formula.

The validation formulas works like Conditional Formatting formulas. When a data validation rule is set, Excel allows user to enter a value if the formula returns TRUE values. You can find formula samples about Conditional Formatting in the following articles: How to highlight … examples

To avoid duplicate entry, you can use the COUNTIF function which returns the count of cells that meets a specified condition. If all values should be unique, then each value should only exist once in the list. So, counting the value that user enters in the list and making sure that count should not repeat more than once applies the rule we need.

=COUNTIF($C$5:$C$10,C5)<2

It is important to note that while the list range is an absolute reference, the cell in the second argument is a relative reference. This condition cell is also the first cell in the list. To set the first cell as condition and a relative reference tells Excel to populate the reference of the condition for each cell in the list.

If you select the 5th cell in the list (C9) and check the Data Validation formula, you can see formula is altered as below

=COUNTIF($C$5:$C$10,C9)<2.