How to generate unique random numbers in Excel

Although Excel doesn't lack functions to generate random numbers, there is no built-in option to generate unique numbers. On the other hand, we can assume that it would be very uncommon to get same number from Excel's random number functions. Anyways, we can eliminate this possibility by combining random number generation functions with others. In this guide, we’re going to show you how to generate unique random numbers in Excel.

Download Workbook

Excel formulas for generating unique random numbers

At the time we are writing this article, Excel has three dedicated formulas to generate random numbers:

RANDARRAY is the newest of the three. Since it's available for Excel 365 users only, you may have missed the function. The RANDARRAY function is a combination of older functions with ability of populating array of cells.

RANDARRAY([rows],[columns],[min],[max],[whole_number])

[rows]	Optional. The number of rows of randomly generated numbers to be returned. The default value is 1.
[columns]	Optional. The number of columns of randomly numbers to be returned. The default value is 1.
[min]	Optional. The minimum number to be returned. The default value is 0.
[max]	Optional. The maximum number to be returned. The default value is 1.
[whole_number]	Optional. A Boolean value that specifies the type of numbers to return. FALSE: Decimal number to 15 decimal places. (default) TRUE: Whole number

Populating multiple cells is an Excel 365 feature called dynamic array. You can learn more in Dynamic Arrays in Excel.

RAND is very straightforward function without any arguments that it can generate a random number between 0 and 1.

RAND()

RANDBETWEEN is another older function that can return a random generated integer number between given limits.

RANDBETWEEN(bottom, top)

Excel 365 & Dynamic arrays

Thanks to dynamic array concept and corresponding functions you can generate unique random numbers with a single formula.

=SORTBY(SEQUENCE(<count of numbers>),RANDARRAY(<count of numbers>))

This formula uses the SEQUENCE function to generate a sequence of integers starting from 0 to a given number (<count of numbers>). The SORTBY function sorts the sequence randomly thanks to the RANDARRAY function to generate a given number of random numbers (<count of numbers>).

Because the numbers coming from the SEQUENCE function is unique, there will not be any possibility of duplication. You can choose any number of numbers from the sorted numbers. Here is a sample to generate 10 unique numbers:

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

Traditional way

If you do not have Excel 365, you can mimic the same approach by using multiple columns. First column should include the randomly generated numbers from the RAND function. Since you cannot use the dynamic array feature, copy down the formula as much as the number you want to generate.

=RAND()

The second column will include the LARGE function which returns the nth largest value in an array or a range. You need to supply the range of cells and the rank of the number you want to return. Instead of using static numbers for the rank (n value), you can use the ROW function with a cell reference from the first row, e.g., "A1". Keep the cell reference relative in the ROW function and copy down the formula.

=LARGE(<random numbers>,<nth value>)

Finally, you can use the MATCH function to return the index of the sorted number in the randomly generated numbers (the first column). Because none of the numbers can acquire the same index, you will get unique random numbers.

=MATCH(<sorted value>, <random numbers>, 0)