To randomize a list in Excel without repeating numbers, use =SORTBY(A2:A11,RANDARRAY(ROWS(A2:A11))) in Microsoft 365; it shuffles the values in A2:A11 without adding or removing any. For a random sample from a larger list, shuffle the full list and take only the required number of results. These methods do not create duplicate values when the source values are unique.
What “without repeating” means
There are two different tasks people mean by randomizing numbers without repeats. You may want to rearrange a list you already have, or you may want Excel to choose a smaller group from a larger set. In both cases, the key is to shuffle a collection of eligible values rather than generate each result independently.
For example, if the starting list is 2, 5, 8, and 11, a valid shuffle might be 8, 2, 11, 5. Each starting value appears once. A result such as 8, 2, 8, 5 is not a shuffle of that list: it repeats one value and leaves another out.
Also check whether the source itself contains duplicates. A shuffle preserves every source entry, including duplicates. If the source list has two entries containing 5, the shuffled result will also contain two 5s. The methods below prevent Excel from selecting the same entry twice; they cannot make repeated source values distinct unless you first remove or account for them.
Method 1: Shuffle a list with SORTBY and RANDARRAY
This is the most direct option in Excel for Microsoft 365. Assume the numbers to shuffle are in A2:A11. Enter this formula in an empty cell outside the source range:
=SORTBY(A2:A11,RANDARRAY(ROWS(A2:A11)))
The formula creates one random sort key for each source row, then returns the original values in the order of those keys. If A2:A11 contains ten distinct numbers, the result contains those same ten distinct numbers in a new order. The formula does not independently invent replacement numbers, so it cannot omit a source entry or choose one entry twice.
The formula returns a dynamic array, which means results spill into the cells below the formula automatically. Leave enough empty cells for the full result; an occupied cell in the spill area causes a #SPILL! error. Do not type into the spill range, and do not place the formula inside the range it is meant to shuffle.
SORTBY, RANDARRAY, and ROWS are valid in Microsoft 365. Dynamic-array formulas are also available in Excel for the web. In Excel for the web, enter the formula in a blank cell as you would in the desktop app and make sure the spill area is clear.
Method 2: Generate a shuffled sequence of integers
If you need a random order of consecutive integers rather than an existing list, create the sequence first and shuffle it. For example, to arrange the numbers 1 through 20 in a random order, enter this formula in a blank cell:
=SORTBY(SEQUENCE(20),RANDARRAY(20))
SEQUENCE(20) creates the integers 1 through 20, and SORTBY rearranges them using random keys. The result includes each integer exactly once. To change the size of the set, replace both instances of 20 with the desired count. This version assumes the sequence starts at 1.
For a random selection of ten different numbers from 1 through 100, use this formula instead:
=INDEX(SORTBY(SEQUENCE(100),RANDARRAY(100)),SEQUENCE(10))
It shuffles all 100 possible numbers and returns the first ten. Because it selects ten positions from a shuffled sequence, there are no duplicate numbers in the result. Keep the requested sample size at or below the number of available values; for example, you cannot draw 101 different numbers from a set containing only 100.
For a different starting number or interval, build the source sequence with SEQUENCE. For instance, =SEQUENCE(10,1,5,2) creates ten values starting at 5 and increasing by 2: 5, 7, 9, and so on. You can use that sequence inside SORTBY and pair it with RANDARRAY(10) to randomize those values.
Method 3: Use a RAND helper column and sort
A helper column is useful when you want a visible, editable workflow or are working with a table of related information. Suppose the numbers are in A2:A11. In B2, enter =RAND(), then fill the formula down through B11. Each row receives a random key. Select both columns, then choose Data > Sort. Sort by column B, from smallest to largest or largest to smallest.
Sorting both columns keeps each random key attached to the number on its row. If your data has column headings, use the sort dialog’s header setting so Excel treats the first row as headings rather than as a value. Once the rows are in random order, you can delete the helper column if you no longer need it; the values in column A remain in their sorted order.
This method also works when each number belongs to a record, such as a participant name, ticket number, or product code. Select the entire data range before sorting, not just the number column, so that the other fields stay with the correct record. In Excel for Mac, use the Data tab and its Sort command; the dialog may look different, but the process is the same.
Choose the right method for your task
- Shuffle an existing list: Use
SORTBYwithRANDARRAYfor a quick formula result, or use a helper column if you need to sort whole rows or prefer a manual process. - Create a permutation of consecutive numbers: Use
SORTBY(SEQUENCE(...),RANDARRAY(...)). This is a convenient way to create randomized order numbers, question sequences, or a draw order. - Choose a smaller sample without replacement: Shuffle the full source, then use the first required number of results. The
INDEXandSEQUENCEexample above returns a specified number of values. - Use an older Excel version: A RAND helper column and the built-in sort command are usually the most compatible approach. The dynamic-array formulas require a version that supports the functions used.
For a sample from a list with gaps or irregular values, use the actual list as the source instead of generating a sequence. For example, if A2:A101 contains distinct employee IDs, shuffle those IDs and take the first five results. The output then draws only from the entries in the source list, even if the IDs are not consecutive.
Why RAND and RANDBETWEEN can repeat values
A common mistake is to generate every result separately with =RANDBETWEEN(1,100) and assume Excel will avoid earlier results. It will not: each calculation is an independent draw, so the same integer can appear more than once. Making the range larger may make repeats less likely, but it does not guarantee a unique result.
To guarantee no repeats, make one collection of all eligible values and shuffle that collection. A shuffle changes positions without changing membership. This is different from repeatedly drawing random values and checking whether each one has already appeared. Rejection-based approaches need extra formulas or code to handle duplicates and can become cumbersome, especially when the requested sample is close to the size of the available set.
If the source contains repeated values but you need unique values, remove duplicates before shuffling or create a distinct source list. In Microsoft 365, =UNIQUE(A2:A101) returns distinct values from that range; you can then shuffle the resulting list. Be clear about what counts as a duplicate: two identical numbers may represent two separate tickets, or they may be accidental repeated entries. Those cases call for different treatment.
Recalculation and keeping a draw fixed
Random formulas are volatile: Excel may generate new random keys when the workbook recalculates, when related cells change, or when you request a recalculation. That is useful when you want a fresh shuffle, but inconvenient if the order is meant to be a final selection. Pressing F9 in Excel for Windows recalculates formulas; keyboard behavior can depend on the Mac keyboard and settings, so use Excel’s calculation commands if needed.
To preserve a chosen result, copy the spilled results and paste them as values into another area. On Windows, use Home > Paste > Paste Values, or right-click the destination and choose the values paste option. The pasted values no longer depend on the random formula. You can also keep the formula in one column and make a separate values-only record for the final draw.
Before saving a result for a drawing, assignment, or other process that needs an audit trail, record the source list, the date, and the final values. A spreadsheet shuffle provides a practical random ordering, but a formula alone does not document how the source list was prepared or whether anyone changed the workbook afterward.
Common problems and checks
- The result has a spill error: Clear the cells where the returned array needs to appear. Also check for merged cells or a formula placed inside the source range.
- Values seem to change unexpectedly: That is normal for volatile random formulas. Paste the result as values when you want to lock it.
- Duplicates appear in a shuffled list: Compare the source range with the result. The source may already contain repeated numbers, or the formula may be drawing values independently rather than shuffling the list.
- Rows no longer match their other details: Sort the complete table or range, including all associated columns, rather than sorting only the number column.
- The formula name is not recognized: Your Excel version may not include dynamic-array functions such as
SORTBYorRANDARRAY. Use the helper-column method instead.
For a quick verification, compare the count of source entries with the count of returned entries and check whether the source values are distinct. A shuffle should preserve the number of entries. If you need distinct output values, verify that the distinct source count is at least as large as the number you plan to select.
Frequently Asked Questions
Can Excel generate random numbers without duplicates?
Yes. Shuffle a list of unique numbers with SORTBY and RANDARRAY, or generate a sequence and shuffle it. Do not generate each number separately with RANDBETWEEN if duplicates are not allowed.
How do I randomly select five numbers from a list without repeats?
If the unique source values are in A2:A101, use =INDEX(SORTBY($A$2:$A$101,RANDARRAY(ROWS($A$2:$A$101))),SEQUENCE(5)) in a blank cell. It returns five values from the list without selecting the same source position twice. The source must contain at least five eligible entries.
Does SORTBY change the original list?
No. A formula using SORTBY returns a shuffled result in a separate spill range; it leaves the source cells in place. The helper-column method sorts the selected rows in place, so keep a copy of the original order if you need to preserve it.
How can I stop the random order from changing?
Copy the shuffled results and paste them as values into a blank range. The pasted values are fixed and will not recalculate when the workbook changes.
Will a shuffled list always contain unique numbers?
It contains each source entry once, but the displayed numbers are unique only if the source numbers are unique. Remove duplicate source values first if the requirement is one occurrence of each distinct number.
Related
- Monthly Employee Work Schedule Template Excel Guide
- Mirror Image Flip in Excel: What Works and How
- Microsoft Excel Zoom Options: Change Worksheet View
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets COUNTIF by Cell Color: What Actually WorksNo—Google Sheets’ COUNTIF function cannot count cells by their fill color. It checks cell values against a criterion,…
- Excel Workbook Extension: XLSX, XLSM, and MoreThe standard extension for a modern Excel workbook is .xlsx. Use .xlsm when the workbook needs to keep…
- Google Sheets Data Cleaning: Tools, Steps, and TipsGoogle does not make a product called Google Excel: its spreadsheet app is Google Sheets, and it includes…
- Google Sheets SUMIF: Syntax, Examples, and Common FixesTo add values in Google Sheets only when matching cells meet a condition, use SUMIF. For example, if…
- How to Number Lines in Excel: 4 Reliable MethodsTo number lines in Excel, enter =ROW()-1 in the first numbering cell below a header in row 1,…
- Excel Savings Plan Template: Build a Monthly Goal TrackerAn Excel savings plan template can track a target, current savings, monthly deposits, estimated interest, and the balance…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.