Inserting UNIQUE entries

Use DATA VALIDATION to restrict user input into a cell or a range of cells.

If you have to enter information into a range of cells on a worksheet, it might be that that entry has to be a UNIQUE value.Perhaps the best example is entering an invoice number. That number must be UNIQUE. In Excel you can use Data Validation to force a user to never enter the same invoice number twice. How does it work?

  • Select the range of cells where the UNIQUE invoice numbers (entries) must be entered. For example B6: B10
  • Click on the Data tab and choose: Data Validation > Data Validation …
  • Click the Settings tab of the Data Validation dialog
  • For Allow: choose Custom
  • Enter the following formula at Formula: = COUNTIF($B$6:$B$10; B6)<2
  • When required, fill in the Input Message and Error Alert tabs
  • Click OK


If you enter an invoice number that already exists in one of the cells in B6: B10, Excel will not allow it and will inform you about it!


Click here for a Dutch version of this tip.

Download workbook