To shuffle a list of names in Excel without adding or repeating entries, enter =SORTBY(A2:A20,RANDARRAY(ROWS(A2:A20))) in an empty cell, replacing A2:A20 with your list. The formula returns the same entries in a random order; it does not create extra copies. For a method you can freeze or use in older Excel versions, assign each row a random number and sort the entire list by that number.
What “without repeats” means
A shuffle rearranges the entries already in your list. If there are 19 entries, the result still has 19 entries, and each source row appears once. That is different from asking Excel to draw random names repeatedly, where the same person might be picked more than once unless the selection is made without replacement.
There is an important wrinkle: Excel treats rows as entries, not as people. If “Jordan Lee” appears in three source rows, shuffling preserves all three rows. That is correct when the rows represent three separate records, but not when you want each distinct name only once. Decide whether repeats in the source are intentional before choosing a formula. If two people share the same name, removing duplicate text may wrongly merge them; an employee number or other unique ID is safer.
Shuffle a clean list with one formula
In Microsoft 365 for Windows, put the names in a single uninterrupted range, such as A2:A20, and enter the formula below in an empty cell outside that range. It uses SORTBY to reorder the names according to random values generated by RANDARRAY. These functions are available in Microsoft 365 and Excel 2021 or later.
With names in A2:A6 and no blank cells in that range, enter =SORTBY(A2:A6,RANDARRAY(ROWS(A2:A6))). The result contains five entries: Ana, Ben, Cara, Dev, and Eli, each once, but their order will vary. For example, one recalculation might show Cara, Ana, Eli, Ben, Dev. That example illustrates the possible order; it is not a promised result.
The formula spills its results into the cells below the formula cell. Keep those cells empty or Excel may show a #SPILL! error. If you have a header in A1, use A2:A20 rather than including the header in the shuffled range. The basic formula assumes there are no blank cells in the selected range; if blanks are included, blank entries can appear in the output too.
To shuffle a different list, change both references in the formula to the same source range. For example, if the names are in D5:D34, use =SORTBY(D5:D34,RANDARRAY(ROWS(D5:D34))). Keeping the two ranges aligned ensures Excel generates one random sort value for every name.
Use a helper column and sort the rows
A helper column is a practical choice when you want to see the random keys, work with older Excel, or rearrange names alongside related information. It also makes the shuffle easy to inspect. The essential rule is to sort the entire table or the full set of related columns—not the names alone—so details stay attached to the right person.
- Add a header such as Random beside your list. If names are in A2:A20, enter
=RAND()in B2. - Fill B2 down through B20. Each row receives a random number.
- Select the full data range, including every column that belongs to each record. Choose Data > Sort, set Sort by to the Random column, and choose a sort order.
- If Excel asks, confirm that the selection should be expanded so the other columns move with the names. Remove the helper column afterward if you no longer need it.
For instance, if columns A and B contain a name and department, select both columns when sorting by the random values in C. Sorting only column A would scramble the names but leave departments in their original rows. If your range has headers, identify that it has headers in the Sort dialog. This approach works in older desktop Excel versions as well as current ones.
Make the randomized order stay put
RANDARRAY and RAND are volatile: Excel can recalculate their values when the workbook recalculates, so the order may change after an edit or a recalculation. That is useful when you want a fresh shuffle, but inconvenient when you need to distribute a fixed assignment list, print a roster, or keep a record of a draw.
To preserve a formula-generated result, copy the spilled names and paste them into a separate destination as values. On Windows, copy with Ctrl+C, then use Home > Paste > Values; alternatively, press Ctrl+Alt+V, choose Values in Paste Special, and confirm. On Mac, use Home > Paste > Paste Special and choose Values; keyboard shortcuts can differ by keyboard and Excel settings. Pasting values replaces formulas with fixed text, so the order no longer changes when Excel recalculates.
With the helper-column method, you can sort the names by their random keys and then remove the helper column. If you need a record of how the order was produced, keep a copy of the random values or save a separate values-only version. A normal sort does not itself guarantee that the order will remain fixed if you later sort again or regenerate the random keys.
Keep duplicates out—or preserve distinct people
If the source contains repeated text and you want each distinct name to appear once, Microsoft 365 or Excel 2021 can create a distinct list before shuffling. With names in A2:A20 and no blanks, enter =SORTBY(UNIQUE(A2:A20),RANDARRAY(ROWS(UNIQUE(A2:A20)))) in an empty cell. UNIQUE removes repeated values, then SORTBY randomizes the remaining values.
Use that version only when identical text really means the same entry. If two different people are both named Alex Kim, UNIQUE will treat those identical name strings as one value. Instead, shuffle rows based on a unique ID, or include the ID and name together in the data you sort. Also check for accidental differences such as a trailing space: “Sam” and “Sam ” may not behave like the same text. Cleaning the source can prevent confusing results.
When the list contains blank cells, consider removing or filtering them before shuffling rather than treating an empty row as an entry. A quick manual cleanup is often clearest for a short list. For a recurring process, make the input range consistent and document whether blank rows and duplicate names should be kept.
Choose the method that fits your task
The formula is fastest when you want a live shuffled view and have a supported Excel version. The helper-column sort is more transparent and works in older versions, but it changes the order of the source rows unless you copy the data first. Neither approach needs an add-in or a special randomization command.
- Use SORTBY and RANDARRAY for a quick, formula-based shuffle that leaves the original list in place.
- Use RAND and Data > Sort when you need compatibility with older Excel or want to reorder complete records directly.
- Use UNIQUE before sorting only when duplicate text values should genuinely collapse into one entry.
- Paste values when the result must stay fixed instead of changing on recalculation.
For a one-time handoff, creating the formula result and pasting values is usually straightforward. For repeated use, keeping the original data intact and putting the shuffled output in another area reduces the risk of losing the original order. If the workbook is shared, label the original list and the randomized output clearly.
Random selection without replacement
Sometimes the goal is not to shuffle every name but to select a smaller group without choosing anyone twice. A full shuffle provides a simple route: randomize the complete list, then take the first few entries. Because every source row appears once in the shuffled order, the first five rows, for example, contain five different source rows when the source itself has no duplicate records.
For a list of 30 people and a group of five, shuffle all 30 names with the formula method, then use the first five results as the selection. If the chosen names need to be fixed, paste those results as values. If duplicate names in the original represent separate people, retain their IDs so the selection distinguishes them. If duplicates are accidental, clean or deduplicate the source first.
This method avoids the common mistake of generating five independent random picks from the original list: independent picks can repeat the same row. A shuffle is a random ordering of the available entries, so a row cannot occupy two positions in that output. For consequential drawings, such as a prize selection with formal rules, use a process that meets the organizer’s requirements and preserve an auditable record; a spreadsheet formula alone does not establish that a draw meets any particular policy.
Common problems and how to fix them
- The order keeps changing: that is expected for RAND and RANDARRAY. Paste the finished output as values to keep one order.
- Excel displays #SPILL!: clear the cells below and beside the formula where its result needs to spill, or move the formula to an empty area.
- A blank appears in the shuffle: check whether the source range includes empty cells. Use a range containing only names or clean the list first.
- Names are separated from their details: sort the complete data range, not only the name column. Confirm Expand the selection if Excel prompts you.
- A name appears more than once: inspect the source. A shuffle preserves repeated rows; use UNIQUE only if identical text should count as one entry.
Random sort keys can occasionally tie because random values have finite precision. A tie does not add or delete a name; Excel still sorts the existing rows, although tied entries may not have a meaningful order relative to each other. For an ordinary classroom, team, or planning list, this is rarely an issue. If the ordering has formal requirements, do not treat a basic spreadsheet shuffle as a certified random drawing system.
Frequently Asked Questions
Does Excel have a built-in shuffle button?
Excel does not have a dedicated Shuffle command for a list. Use SORTBY with RANDARRAY for a formula-based shuffle, or add RAND values and sort the data by that helper column.
Will SORTBY repeat a name?
SORTBY rearranges the values in its input array; it does not generate replacement names. Each source row appears once in the output. Repeated values already present in the source remain repeated unless you remove duplicates first.
How do I randomize names without changing the original list?
Put the SORTBY formula in an empty area or another worksheet, referring to the original range. The source remains as it was, while the formula displays the randomized order separately.
Why does my randomized list change after I edit a cell?
RANDARRAY and RAND recalculate, so their random keys and the resulting order can change. Copy the result and paste values when you need a stable version.
Can I shuffle names in older Excel?
Yes. Add a helper column with =RAND() in the first data row, fill it down, and sort the full data range by that column. The helper-column approach does not depend on SORTBY or RANDARRAY.
How do I select several names without choosing the same row twice?
Shuffle the full list and take the required number of entries from the top. That selects distinct source rows as long as each person or record is represented by its own row and the source duplicates have been handled appropriately.
Related
- Most Common Excel Keyboard Shortcuts and How to Use Them
- Most Used Excel Keyboard Shortcuts for Everyday Work
- Monthly Employee Work Schedule Template Excel Guide
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 Archive Emails in Outlook 365To archive email in Outlook 365, select a message and choose Archive on the toolbar or ribbon. Outlook…
- How to Put a Filter in Google Sheets and Sort DataTo put a filter in Google Sheets, select your data range, including its header row, then choose Data…
- How to Delete a Blank Last Page in Microsoft Word QuicklyTo delete a blank last page in Word, show formatting marks with Home > Paragraph > Show/Hide ¶,…
- Can You Make a Box and Whisker Plot in Excel?Yes. In Excel for Microsoft 365 on Windows, select your grouped data, then choose Insert > Insert Statistic…
- Conditional Formatting Excel Whole Row: A Practical GuideYes. To format a whole row in Excel based on one cell, select the rows’ data range, choose…
- Excel Split Cell After a Character: Methods That WorkIn Excel for Microsoft 365 on Windows, split a cell at a character with Data > Text to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.