Require a Unique List of Numbers in a Range in Excel

I'll show you how to require a user to enter a unique number into a range of cells in Excel.  This feature will require that the value entered into the cell must be a number and that it must be different than any other number in that range.  To do this, we will be using data validation along with a custom formula.

Create the Custom Formula

The first thing that we need to do is to create a custom formula that we will later use in conjunction with the Data Validation feature in Excel.

I always create formulas like this first and in the worksheet itself because it helps to troubleshoot problems, you will see why when we go to use this formula with data validation.

Let's say that we want to require unique numbers in column A from rows 1 to 10.

Here is the formula we need:

There are three functions here.

The AND function allows this formula to check that the values in the cells are numbers AND not a duplicate of anything else in the range.

The ISNUMBER function makes sure the value in the cell is a number.

The COUNTIF function counts the cells in the range that are the same as the current cell, which is what allows the formula to check for, and prevent, duplicate values in the list.

What to change for your spreadsheet:

Change everywhere it says A1 to the cell reference that starts your list of unique numbers.

Change $A$1:$A$10 to the range reference that is where you want your list of unique numbers.  Make sure to keep the dollar signs $ in front of the column and row references for your range.  This is important so that these cell references don't change when you apply data validation to a range of cells.

Steps to Require Unique List of Numbers in a Range in Excel

Now that we have our custom formula, we can add the data validation.

1. Select the range of cells where you want to require unique numbers:
   
2. Go to the Data tab and click the Data Validation button or just hit Alt + D + L on the keyboard.
   
3. From the Allow: dropdown select the Custom option:
   
4. Paste the custom formula that we made above into the Formula: section:
   
   It is so much easier to paste the formula in here instead of writing it in here because some of Excel's formula error checking will not run in this window.  Also, Excel won't show you the arguments for the functions you are using as you input them.
5. Hit OK and that's it.

Now, you won't see anything in the spreadsheet but we can test it out:

When I tried to enter 123 twice, it stoped me.

Now, let's test it with text:

So you can see that no text is allowed and no duplicate numbers are allowed.

Notes

Data validation is a very powerful tool to use to keep data in Excel organized and uncorrupted.

My biggest tip when creating custom formulas for data validation is to create the formula in the worksheet before you input it into the data validation window because, in the worksheet, Excel will help you to know what to enter and to troubleshoot any problems you may have with the formula.  However, with many data validation custom formulas, if you put it in the spreadsheet, it probably won't give you much, if any, useful output once you have completed making the formula; this tip is only for creating the formula.

More data validation in Excel tutorials.

Make sure to download the accompanying spreadsheet so you can see this tutorial in action.