Select Page

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.

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