Software

Restrict duplicate data using Excel Validation

To maintain order in a sheet, you sometimes have to restrict data entry to unique values. When you want users to enter only unique values, use a validation rule.

Excel sheets accept duplicate values of course, but that doesn't mean you'll always want to allow them. There are times when you'll not want to repeat a value. Instead of entering a new row (or record), you'll want the user to update existing data. You can train users, but that doesn't mean they'll comply. They'll try to, but specific rules are easy to forget, especially if updates are infrequent. The easiest way to protect a sheet from duplicate values is to apply a validation rule. If a user tries to enter a duplicate value, the appropriate validation rule will reject the input value and (usually) provide helpful information as to what the user should do next.

For example, let's suppose users track hours worked using the sheet shown below. You want each worked date (column A) entered just once—there's no signing in and out for lunch or other activities. (This setup would be too restrictive for most situations, but it sets up the technique nicely.) Realistically, a user could easily see that they're re-entering an existing date, but in a sheet with a lot of data, that wouldn't be the case. At any rate, there's nothing to stop the user from entering the same date twice.

To apply a validation rule that restricts input values to only unique values, do the following:

  1. Select A2:A8 (the cells you're applying the rule to).
  2. Choose Validation from the Data menu and click the Settings tab. In Excel 2007/2010, click the Data tab and choose Data Validation from the Data Validation dropdown in the Data Tools group.
  3. Choose Custom from the Allow dropdown list.
  4. The Custom option requires a formula that returns True or False. In the Formula field, enter the following expression: =COUNTIF($A$2:$A$8,A2)=1.
  5. Click the Error Alert tab and enter an error message.
  6. Click OK.

Once you set the rule in place, users must enter unique date values in A2:A8. As you can see below, Excel rejects a duplicate date value, displays a simple explanation, and tells the user what to do next—click Retry and enter a unique date.

This particular validation formula accepts any value, it just won't accept a duplicate value. The cell format is set to Date, which restricts entry to date values. You can use this formula to restrict any type of data, not just date values.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox