Microsoft Excel training

Ensure unique values in a column

When you enter data in an Excel spreadsheet, sometimes you want to be sure that you enter unique values only. For example, if you enter a series of invoice numbers in Column A your Excel spreadsheet, you may want to be sure that you do not enter the same invoice number twice.

If you always enter new data at the end of the column, you can use Excel's built-in data validation to be sure that what you put in there is not also entered somewhere further up the column.

To ensure unique values in a column (Excel 2007), do the following:

  1. Select a range of cells, for example, column A.
  2. Select the Data menu, then click on Data Validation (in Data Tools Group).
  3. In Data Validation dialog box, select Settings -> Allow -> Custom.
  4. In the Formula box, enter the following formula: =COUNTIF($A$2:$A$20,A2)=1
  5. Select the Error Alert tab.
  6. In the Title box, type: Duplicate Entry.
  7. In the Error message box, type: The invoice number you entered already exists in the list above.
  8. Click OK.

Try now to enter the same data twice in column A. You will receive an error message.



   
excel, unique, values