Generate a Unique List of Random Numbers With a Simple Formula

Sections:

Versatile Formula to Generate a Unique List of Numbers

Excel 365 Formula to Generate a Unique List of Numbers

Simple List in Excel 365

Simple List - Old Method

Versatile Formula to Generate a Unique List of Numbers

=IFERROR(LARGE(ROW($100:$999)*NOT(COUNTIF($C$6:C6,ROW($100:$999))),RANDBETWEEN(1,999-100-ROW(A1)+2)),"")

Min 100

Max 999

Range $C$6:C6

Change the Min value to the lowest possible value that you want to appear in the list. Change all occurrences of this number in the formula.

Change the Max value to the highest possible value that you want to appear in the list. Change all occurrences of this number in the formula.

Change the Range to the address of the first cell above the start of the list. Keep the exact format that it has now, with the first part in absolute references (with $ signs) and the second part in relative references.

Number of values is controlled by copying this formula down the column. Get the formula correct for the first entry and then copy it down as many times as you need to get as many unique random numbers as you need.

Excel 365 Formula to Generate a Unique List of Numbers

=INDEX(UNIQUE(RANDARRAY(10^2,1,100,999,TRUE)),SEQUENCE(10))

Min 100

Max 999

Number of Values 10

Change the Min value to the lowest possible value that you want to appear in the list.

Change the Max value to the highest possible value that you want to appear in the list.

Change the Number of Values to the number of values that you want to appear in the list. Change every bold occurrence of 10 in the formula.

Simple List in Excel 365

This generates a simple integer list of values that moves sequentially up or down.

=SORTBY(SEQUENCE(10),RANDARRAY(10))

Number of items to show is controlled by the number in the formula. Change 10 in both places to however many items you want to show.

Simple List - Old Method

This works for all versions of Excel.

1. Create a list of numbers in a column. Put 1 in the first cell and 2 in the second cell and copy them down as far as you need. (This creates a simple numbered list.)
2. In the column next to the numbered list, input =RAND() in the first cell and copy it down.
3. Sort the column with the random numbers in it and that will randomly sort the original numbered column as well.
4. Delete the random number column and then you are left only with the original column of unique numbers that are displayed in a random order.