How to Generate Unique Random Number Lists in Older Excel Versions
Question details
The user needs to create lists of unique, non-repeating random numbers in older versions of Excel that do not support modern dynamic array functions like RANDARRAY, SEQUENCE, and UNIQUE.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating randomized, non-duplicate sets of sequential numbers (e.g., 1-15, 16-30, 31-45) for data sampling or randomization tasks without a Microsoft 365 subscription.
- Observed behavior
- Modern dynamic array formulas utilizing SORTBY and RANDARRAY return errors in legacy Excel releases, necessitating a multi-step formula approach combining RAND, INDEX, MATCH, and RANK to manually shuffle the data.
Ensure you have a blank column adjacent to your target numbers to use as a helper column, and note that legacy array formulas require pressing Ctrl+Shift+Enter in older versions of Excel.
Use a Helper Column with RAND, RANK, and INDEX
Create a randomized list by assigning a random value to each sequential number, ranking those random values, and extracting the original numbers in a newly shuffled order.
This legacy approach circumvents the need for the RANDARRAY or SORTBY functions. It relies on the volatile RAND() function to assign temporary, completely randomized weights to your source numbers.
By utilizing INDEX and MATCH alongside the RANK function, Excel forces the numbers into a randomized, non-repeating hierarchy. This works perfectly in Excel 2010, 2013, 2016, and Excel 2019.
Enter your base set of sequential numbers into a column. For example, type the numbers 1 through 15 in cells A2:A16.
In cell B2, input the formula =RAND(). Press Enter, then drag the fill handle from the bottom-right corner of B2 down to B16 to generate a random decimal for each number.
Select the first cell where you want your unique random list to appear (e.g., D2). Enter the formula: =INDEX($A$2:$A$16,MATCH(ROWS($D$2:D2),RANK($B$2:$B$16,$B$2:$B$16,0),0))
Instead of simply pressing Enter, hold down Ctrl+Shift+Enter. This surrounds the formula in curly brackets. Drag the fill handle from D2 down to D16 to complete the shuffled list.
Press F9 on your keyboard to instantly recalculate the RAND() functions and reshuffle your list. To generate numbers for a different set, simply change the values in A2:A16 to 16-30 or 31-45.
Generate Unique Random Numbers Easily with WPS Office
Skip the complicated array formulas. WPS Spreadsheet supports modern dynamic array functions natively, allowing you to generate unique, randomized sets of numbers in a single step—completely free of charge.
- 1. Open a new worksheet: Launch WPS Office and open a new or existing spreadsheet document.
- 2. Enter the modern array formula: Click on the cell where you want the randomized list to begin. To generate randomized numbers from 1 to 15, type: =SORTBY(SEQUENCE(15),RANDARRAY(15))
- 3. Adapt for different number ranges: To generate randomized sets starting from a different number, like 16 to 30, use: =SORTBY(SEQUENCE(15, 1, 16, 1), RANDARRAY(15))
- 4. Execute the formula: Press Enter. The randomized list will automatically spill down the column instantly, with no helper columns needed.
Frequently Asked Questions
How do I generate unique random numbers between 16 and 30 using the legacy method?
Set up your source column (e.g., A2:A16) and type the numbers 16 through 30 in those cells. Keep the helper column with the =RAND() formula and the INDEX/MATCH formula exactly the same. The formula dynamically references whatever is in your source column.
Why does my random number list keep changing every time I type something?
Formulas that utilize RAND() or RANDARRAY() are volatile, meaning Excel recalculates them after every sheet edit. To stop this behavior, copy your final randomized list, right-click the destination cell, and choose 'Paste as Values' to overwrite the formulas with static text.
Why do I get a #NAME? error when using SORTBY and RANDARRAY?
The #NAME? error occurs because the SORTBY, SEQUENCE, and RANDARRAY functions are part of a modern dynamic arrays update. If you are using Excel 2019, 2016, or older, those functions do not exist in your software. You must use the legacy workaround combining RAND, RANK, and INDEX, or switch to a modern free alternative like WPS Office.
