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. Display the Data Validation dialog box. (In versions of Excel prior to Excel 2007, choose Data | Validation. In Excel 2007 click the Data tab of the ribbon and then click Data Validation in the Data Tools group.)
3. Using the Allow drop-down list, choose the Custom option.
4. In the Formula box, enter the following formula:

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

5. 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 4:

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