Ensuring Unique Values in a Column

When you are entering information in a worksheet, you may want to ensure that the data being entered is actually unique for a particular column. For instance, if you are entering a series of invoice numbers in column D of a worksheet, you may want to ensure that you don't enter the same invoice number twice.

There are a couple of ways that you can approach such a problem. If you are always entering your new information at the bottom of a column, you can use Excel's data validation feature to make sure that whatever you are entering in the current cell does not match anything higher up in the column. Simply follow these steps:

1. Select column D.
2. Click the Data tab of the ribbon.
3. Click the Data Validation tool, in the Data Tools group. Excel displays the Data Validation dialog box. (See Figure 1.)



Figure 1. The Data Validation dialog box.

4. Using the Allow drop-down list, choose the Custom option.
5. In the Formula box, enter the following formula:

     =MATCH(D1,$D:$D,0)=ROW(D1)

6. Click on OK.

If you may be entering data anyplace within column D, and you want to know if you are entering a value that is elsewhere in the column (above or below the current cell), then you can follow the above steps, but use this formula in step 5:

=COUNTIF($D:$D,D1)<2

There is one other thing to keep in mind: All bets are off if a user copies a value into a cell in the column. This results in the data validation information be overwritten as the copy is completed. If such a thing is likely to happen with your data, the only way around it is to create a macro that checks, after each action, whether the data validation has been removed from cells where it should be present. Such is not a trivial undertaking.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10673) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021. You can find a version of this tip for the older menu interface of Excel here: Ensuring Unique Values in a Column.