Require a Unique List of Numbers in a Range in Excel

Add to Favorites

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.


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

Excel Function: AND(), COUNTIF(), ISNUMBER()
Downloadable Files: Excel File

Our Excel Courses

Professional Input Forms in Excel

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

Send Emails from Excel

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

Similar Content on TeachExcel
Quickly Create a Huge List of Numbers in Excel
Tutorial: Quickly create a large list of numbers in Excel using the Fill Command.  This will save ...
Generate a Unique List of Random Numbers With a Simple Formula
Tutorial: Sections: Versatile Formula to Generate a Unique List of Numbers Excel 365 Formula to Gen...
Highlight, Sort, and Group the Top and Bottom Performers in a List in Excel
Tutorial: How to highlight the rows of the top and bottom performers in a list of data. This allows...
Show All Formulas in a Worksheet in Excel
Tutorial: Display all formulas instead of their output values. This allows you to quickly troubles...
Email List of People from Excel Using a Macro
: Send Emails from Excel Course Send emails to everyone in a list in Excel using a macro. Th...
Sum Values from Every X Number of Rows in Excel
Tutorial: Add values from every x number of rows in Excel. For instance, add together every other va...