'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Generate Unique Random Numbers from 1 to 6 in Excel
Question details
The user wants to generate a randomized array of numbers from 1 to 6 exactly once without duplicates, but is experiencing a #SPILL! error and formula syntax issues.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a randomized, non-repeating sequence of numbers between 1 and 6 using a formula.
- Observed behavior
- The formula returns a #SPILL! error when the destination cells are not empty, or fails to execute properly due to extra commas in the syntax.
Ensure that the cells immediately below or adjacent to your target cell are completely empty so the generated random numbers have enough room to spill.
Use a Dynamic Array Formula (SORTBY, SEQUENCE, RANDARRAY)
Combine modern array functions to generate a sequence of numbers from 1 to 6 and randomly sort them without duplicates.
To get a unique list of randomized numbers, you can generate a sequential list first using SEQUENCE, and then scramble its order using SORTBY paired with RANDARRAY. This ensures every number from the sequence appears exactly once.
Click on the empty cell where you want the first number of your random sequence to appear.
Type the formula: =SORTBY(SEQUENCE(6), RANDARRAY(6)) into the formula bar.
Ensure there are no extra commas or missing brackets in your formula, as these will trigger an error.
Press Enter. The numbers 1 through 6 will automatically populate in a randomized order down the column.
Generate Unique Random Numbers Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array formulas like SEQUENCE, SORTBY, and RANDARRAY. You can easily generate randomized unique lists for games, sampling, or data testing with no complex scripting required.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document where you need the random numbers.
- 2. Select your starting cell: Click on an empty cell with at least 5 empty cells directly beneath it.
- 3. Input the array formula: Type =SORTBY(SEQUENCE(6), RANDARRAY(6)) and press Enter.
- 4. View the results instantly: WPS Spreadsheet will immediately spill the 6 unique random numbers into the column.
Frequently Asked Questions
Why am I getting a #SPILL! error when generating random numbers?
A #SPILL! error occurs when an array formula tries to output multiple values into adjacent cells, but one or more of those destination cells already contain data, spaces, or formulas. Clearing the blocking cells will immediately resolve the error.
Can I generate these unique random numbers across a row instead of a column?
Yes. You can adjust the SEQUENCE function to generate columns instead of rows by specifying the column parameter. Use the formula: =SORTBY(SEQUENCE(1, 6), RANDARRAY(1, 6)) to spill the numbers horizontally across a row.
How do I change the formula to generate unique numbers from 1 to 10?
You simply need to change the numerical parameters inside the functions. Replace the '6' with '10' like this: =SORTBY(SEQUENCE(10), RANDARRAY(10)). The formula will now return a randomized, non-repeating sequence from 1 to 10.
What does the 'PopulationSize' parameter refer to in array functions?
In array formulas, PopulationSize (or the number of rows/columns specified) dictates the total number of values the function needs to produce. For generating a sequence from 1 to 6, the required population size is 6.
