# 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:

```
```=AND(ISNUMBER(A1),COUNTIF($A$1:$A$10,A1)<=1)

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.

- Select the range of cells where you want to require unique numbers:
- Go to the
**Data**tab and click the**Data Validation**button or just hit**Alt**+**D**+**L**on the keyboard. - From the
**Allow:**dropdown select the**Custom**option: - 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. - 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.

## Question? Ask it in our Excel Forum

Tutorial: Quickly create a large list of numbers in Excel using the Fill Command. This will save y...

Tutorial: How to highlight the rows of the top and bottom performers in a list of data. This allow...

Tutorial: Display all formulas instead of their output values. This allows you to quickly troubles...

Tutorial: Add values from every x number of rows in Excel. For instance, add together every other va...

Tutorial: How to convert scientific numbers to show their full amount; this method also allows you t...

Tutorial: How to SUM only the visible rows from a filtered data set in Excel. To do this, we will us...