To randomize the numbers 1 through 20 without duplicates in Excel for Microsoft 365, enter this formula in an empty cell: =SORTBY(SEQUENCE(20),RANDARRAY(20)). It spills all 20 numbers in a shuffled order; because it shuffles a set that already contains each number only once, no number repeats.
If you need only a random sample, rather than a shuffle of every number, use a formula that selects a specified number from a larger unique range. The key is to shuffle the possible values first and take the required number of results, instead of drawing each result independently.
What randomizing without duplicates means
There are two related tasks that are easy to confuse. A shuffle rearranges every value in a list, while a sample selects only some of the possible values. In both cases, no duplicates means each value can appear at most once in the output. For example, shuffling 1 through 20 gives 20 results, whereas selecting 5 from 1 through 20 gives 5 distinct results.
A formula that generates random integers one at a time may repeat values. That is because each draw can land on a number that appeared earlier. Excel’s RANDARRAY can produce random values, but it does not, by itself, guarantee unique values. A reliable approach is to start with a finite list of distinct candidates, assign random sort keys, and then take the whole shuffled list or the number of entries you need.
Check the candidate list before randomizing. If the source itself contains repeated numbers, shuffling it will not remove them. If the task requires distinct values, use a distinct candidate range or remove duplicates before using a random sort.
Shuffle a complete number range with one formula
In Microsoft 365 or Excel 2021, put the following formula in an empty cell to shuffle the numbers 1 through 20:
With the formula entered in, for example, D2 and the cells below it empty, use:
=SORTBY(SEQUENCE(20),RANDARRAY(20))
SEQUENCE(20) creates the ordered list from 1 to 20. RANDARRAY(20) creates 20 random sort keys, and SORTBY returns the number list in the order of those keys. Each candidate occurs once before sorting, so the resulting list contains exactly one of every number from 1 to 20. The result spills down the cells below the formula; leave that spill area clear.
To shuffle a different complete range, change the sequence size. For instance, =SORTBY(SEQUENCE(50),RANDARRAY(50)) returns every integer from 1 through 50 in a random order. This changes the order, not the values: the first result is not necessarily a specially generated random number, but one member of the original range.
Choose a random sample without replacement
For a sample, first shuffle all candidates and then return only the first few. With a sample of 10 distinct numbers from 1 through 100, enter this formula in an empty cell:
With the formula in G2 and the spill area below G2 clear, use:
=INDEX(SORTBY(SEQUENCE(100),RANDARRAY(100)),SEQUENCE(10))
The formula builds the numbers 1 through 100, randomizes their order, and returns the first 10 entries. It therefore gives 10 distinct numbers, with no repeats. The number of possible results is limited by the candidate range: for a sample of 10, the range must contain at least 10 distinct candidates.
To sample from 101 through 200 instead, with 10 results, enter this formula in an empty cell:
With the formula in G2 and the spill area below G2 clear, use:
=INDEX(SORTBY(SEQUENCE(100,,101),RANDARRAY(100)),SEQUENCE(10))
Here, SEQUENCE(100,,101) starts at 101 and creates 100 consecutive values, ending at 200. Change the final SEQUENCE(10) to request a different sample size, provided the requested count does not exceed 100. These formulas use dynamic-array functions available in Microsoft 365 and Excel 2021; they may not work in older Excel versions.
Randomize an existing list of numbers
If you already have numbers in A2:A20 and want each distinct number listed once in a random order, use this formula in an empty cell:
With the source numbers in A2:A20, with no blanks, and the output cell and cells below it clear, use:
=SORTBY(UNIQUE(A2:A20),RANDARRAY(ROWS(UNIQUE(A2:A20))))
UNIQUE removes repeated values from the source, ROWS counts how many distinct values remain, and SORTBY shuffles them using random sort keys. For example, if A2:A6 contains 4, 7, 4, 9, 7, the output contains 4, 7, and 9 in some random order. This returns each distinct value once; it does not preserve duplicate counts from the original list.
Use this only when removing source duplicates is what you intend. If the task is to shuffle a list while preserving every entry, including repeated values, use the helper-column method below. The formula assumes the source contains numbers and no blanks. Clean or filter a list with blank cells first if a blank should not appear as an output value.
Use a helper column in older Excel versions
In Excel versions without dynamic-array functions, a helper column provides a straightforward way to shuffle an existing list. Put the candidate numbers in A2:A21, then generate one random key alongside each number. With the number in A2 and the helper cell in B2, enter:
=RAND()
Fill B2 down through B21. Select both columns, then use Data > Sort and sort by column B. Sorting the complete rows keeps each number paired with its random key, and the numbers in column A end up in a random order. The number list must already be unique if the output itself must contain no duplicates; sorting cannot remove repeated source values.
For a random sample of 10 from a longer list of unique candidates, sort the full list by the helper column and keep the first 10 rows. If the list contains duplicates and you need distinct values, remove duplicates from a copy of the list first using Data > Remove Duplicates, or prepare a unique candidate list before sorting. This method works in older desktop versions and is also useful when you want to see or audit the random sort keys.
Why the results change and how to keep them
RANDARRAY and RAND are volatile random functions: Excel can recalculate them when the workbook recalculates, when related data changes, or when you press F9 on Windows. As a result, a formula that produced one set of numbers can show a different order later. That behavior is useful when you want a fresh draw, but inconvenient when the result must be recorded or shared as a fixed assignment.
To preserve a draw, select the spilled results, copy them, and use Home > Paste > Paste Values in a destination range. This replaces the formulas with their current displayed numbers. In Excel for Mac, the same Paste Values command is available from the Home tab; in Excel for the web, use the Home tab’s Paste options. Keep a copy of the formula or original workbook if you may need to generate another draw. Pasting values prevents automatic changes, but it does not provide a permanent audit record of how the draw was conducted.
For a helper-column shuffle, you can similarly copy the sorted number column and paste values into another location. If you leave the random-key formulas in place, recalculation can alter the keys and a later sort can produce a different order.
Common problems and checks
- The formula returns #SPILL!: One or more cells where the output needs to go are not empty, or the formula is inside an Excel Table that cannot accommodate the spill. Clear the destination cells or place the formula in a clear area outside the table.
- Numbers repeat: Check whether the input list contains duplicates. A shuffle preserves repeated entries unless you use
UNIQUEor remove duplicates first. Also check that you are not generating independent random integers one by one. - The formula name is not recognized:
SEQUENCE,SORTBY,RANDARRAY, andUNIQUErequire a recent Excel version, such as Microsoft 365 or Excel 2021. Use the helper-column method in older versions. - The result changed unexpectedly: Random formulas recalculate. Copy and paste values when you need to freeze a specific result.
- The sample has fewer possible values than requested: Increase the candidate range or reduce the sample size. A sample of 12 unique numbers cannot be selected from a candidate set containing only 10 distinct values.
Random sort keys can theoretically tie, since random values are not guaranteed to be different. A tie does not create duplicate numbers in a sequence-based shuffle, but it can make the relative order of tied entries less clearly randomized. For ordinary worksheet draws this is rarely a practical issue; for high-stakes selection, use an appropriately controlled and documented randomization process rather than relying on a casual spreadsheet sort.
Which method should you use?
- Use SORTBY with SEQUENCE when you want a quick shuffle of a consecutive integer range in Microsoft 365 or Excel 2021.
- Use INDEX with SORTBY when you need only a specified number of distinct values from a larger consecutive range.
- Use UNIQUE with SORTBY when an existing list contains repeats and you want each distinct value only once.
- Use a RAND helper column and Data > Sort when you have an older Excel version, want to inspect the random keys, or need to shuffle rows while keeping other columns attached.
These methods do not make duplicates impossible in every imaginable sense: their guarantee depends on the candidate values being distinct before sampling, and on using a shuffle or sampling method rather than repeated independent draws. For a normal worksheet task, checking the source range and using the appropriate method is usually enough to meet the no-repeat requirement.
Frequently Asked Questions
Can Excel generate random numbers without duplicates?
Yes. Create a distinct candidate list, randomize its order with SORTBY and random sort keys, then return the whole list or only the number of entries needed. The result cannot repeat a candidate if each candidate appears only once in the starting list.
Does RANDARRAY guarantee unique numbers?
No. It generates random values and can return repeated values. Use it as the sort-key array for a list of distinct candidates, rather than treating each generated value as a guaranteed unique draw.
How do I get 10 unique random numbers from 1 to 100?
In Microsoft 365 or Excel 2021, enter =INDEX(SORTBY(SEQUENCE(100),RANDARRAY(100)),SEQUENCE(10)) in an empty cell. It spills 10 distinct values selected from the shuffled range 1 to 100.
How do I stop the random numbers from changing?
Copy the results and use Home > Paste > Paste Values. This replaces the volatile formulas with the values currently displayed in the cells.
Can I randomize a list that already has duplicates?
Yes, but decide whether repeats should remain. To keep every row, including repeated numbers, use a RAND helper column and sort the full rows. To show each distinct number once, remove duplicates first or use UNIQUE before shuffling.
Related
- Can’t Insert an Image in Excel? Causes, Checks, and Fixes
- Negative Number Format in Excel: How to Change It
- Excel To-Do List: How to Make One
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Add a Header in Word: Step-by-Step InstructionsTo add a header in Microsoft Word, open the document, choose Insert > Header, select a layout, and…
- Share a Specific Calendar in Outlook: Access and StepsTo share one calendar in Outlook, select that calendar—not just the Calendar view—open its sharing settings, add the…
- Excel Subtotal Visible Rows: Sum Filtered Data CorrectlyTo subtotal only the rows visible after filtering in Excel, use the SUBTOTAL function with function number 9…
- Google Sheets COUNTIF With Multiple Ranges: How It WorksGoogle Sheets’ COUNTIF function accepts one range at a time, so to count matching values across separate ranges,…
- How to Archive Multiple Emails in OutlookTo archive several emails in Outlook, select the messages in your inbox or another mail folder, then choose…
- Google Sheets Filter Dropdown: How to Use ItIn Google Sheets, a filter dropdown is the small control in a column header that lets you sort…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.