How to Generate Random Numbers Without Duplicates in Excel Using SORTBY and RANDARRAY

Whether you are running statistical sampling audits, drawing raffle lottery winners, conducting randomised clinical trials, or creating balanced project team assignments, generating unique random numbers is an essential spreadsheet operation. Standard functions like RAND() or RANDBETWEEN() generate independent random values for each cell, which inevitably produces duplicate collisions when selecting from a pool. In modern Microsoft Excel (Microsoft 365 and Excel 2021+), dynamic array functions allow you to generate a strictly unique, non-repeating set of random numbers with a single, elegant formula.

Why Traditional RANDBETWEEN Causes Duplicates

The classic formula =RANDBETWEEN(1, 100) samples with replacement: each cell calculates independently of its neighbouring cells. By statistical probability, generating just 20 numbers between 1 and 100 has over an 85% chance of containing at least one duplicate pair (the classic Birthday Paradox). Eliminating duplicates historically required complex VBA macros or cumbersome helper columns. Dynamic array functions solve this by creating a predetermined sequential list and shuffling it randomly.

The Core Formula: Combining SORTBY, SEQUENCE, and RANDARRAY

To produce a list of unique, non-repeating numbers in modern Excel, combine the SORTBY, SEQUENCE, and RANDARRAY functions:

=SORTBY(SEQUENCE(total_pool, 1, start_num, 1), RANDARRAY(total_pool))

Here is how the dynamic formula operates under the hood:

• SEQUENCE(total_pool, 1, start_num, 1): Generates an ordered vertical column of strictly unique consecutive integers from start_num to the upper limit.
• RANDARRAY(total_pool): Generates an identical-length array of volatile random decimal numbers between 0 and 1.
• SORTBY(...): Reorders the guaranteed-unique sequential list using the random numbers as the sort index, producing a flawlessly shuffled array with zero duplicates.

Practical Example 1: Shuffling All Numbers in a Range (1 to 50)

To generate every integer from 1 to 50 in random order without a single repeated value:

1. Click into an empty cell where you want the list to begin (for example, A2).
2. Type the formula:

   =SORTBY(SEQUENCE(50, 1, 1, 1), RANDARRAY(50))

3. Press Enter. Excel’s dynamic calculation engine automatically spills the 50 unique integers down column A.

Practical Example 2: Picking N Unique Random Numbers from a Larger Pool

In many lottery, raffle, or audit scenarios, you only need a subset of unique numbers—such as picking 5 unique winning tickets out of 100 entries. Wrap the formula with the CHOOSEROWS or TAKE function:

=TAKE(SORTBY(SEQUENCE(100, 1, 1, 1), RANDARRAY(100)), 5)

This formula shuffles the entire pool of 100 tickets, then cleanly extracts only the top 5 rows, ensuring that no ticket can be selected twice.

Practical Example 3: Randomly Selecting from a List of Names Without Duplicates

If your pool consists of text entries (such as employee names in B2:B30) rather than numbers, replace SEQUENCE with your cell range:

=TAKE(SORTBY(B2:B30, RANDARRAY(ROWS(B2:B30))), 4)

This formula randomly selects 4 distinct team members from your roster without repetitive selections.

How to Freeze and Lock Your Random Numbers

Because RANDARRAY is a volatile formula, your random list will automatically recalculate every time any cell in the workbook is edited or when pressing F9. Once you are satisfied with your random distribution and wish to lock the values permanently:

1. Select the spilled range of generated numbers.
2. Press Ctrl + C to copy the values.
3. Right-click the top-left cell of the selection and click the Values (V) clipboard icon (or press Ctrl + Alt + V, choose Values, and press Enter).

The formula is replaced with permanent static numbers, preserving your randomised sample securely.