To shuffle names in Excel for Microsoft 365, enter this formula in an empty cell: =SORTBY(A2:A20,RANDARRAY(ROWS(A2:A20))). It returns the names from A2:A20 in a random order; the order can change whenever Excel recalculates the formula.
How the shuffle formula works
With names in A2:A20, the formula uses two functions. RANDARRAY(ROWS(A2:A20)) creates a column of random numbers with as many entries as there are rows in the name range. SORTBY then sorts the names according to those random numbers. Each name stays in the list, but its position is randomized.
The formula assumes that A2:A20 contains names without blank rows. Put it somewhere with enough clear space below it for the results to appear. Excel returns the shuffled list as a dynamic array, so you enter the formula once rather than copying it down. For example, if the source list is Ava, Leo, Mina, and Omar, one possible result is Mina, Omar, Ava, Leo. A later recalculation may produce a different order.
The result is a rearrangement of the source names, not a new list of names. If the source contains a name twice, both occurrences remain. If you need each person to appear just once, first remove duplicates from the source list or use Excel’s Remove Duplicates feature on a copy.
Shuffle a list step by step
- Put one name in each cell in a single column, such as A2:A20, with a header in A1 if you want one.
- Choose an empty cell outside the source list, such as C2. Make sure the cells below it are clear.
- Enter
=SORTBY(A2:A20,RANDARRAY(ROWS(A2:A20)))in C2 and press Enter. - Use the new list in C2 and the cells below it. Keep the source list unchanged if you may want to generate another shuffle.
The formula is available in Microsoft 365 and Excel 2021. In Excel for the web, the same formula can be entered in a blank cell; its result spills into nearby cells. The formula uses commas as argument separators, as in standard English-language Excel settings. Some regional settings use semicolons instead, so Excel may require those separators when you enter a formula.
Skip blank cells in the source list
If the source range contains empty cells, the basic formula may include blank entries in the shuffled output. For a list that may have blanks between names, use FILTER to remove them before shuffling. With names in A2:A20 and possible blanks in that range, enter this formula in a clear cell:
=LET(names,FILTER(A2:A20,A2:A20<>""),SORTBY(names,RANDARRAY(ROWS(names))))
FILTER keeps only nonblank cells, and LET gives that filtered list the name names so the formula can refer to it more than once. The result contains only the nonblank names in a randomized order. This version requires Microsoft 365 or Excel 2021. If every cell is blank, FILTER has no matching name to return and may show a calculation error; make sure the source has at least one name.
To limit the source to a fixed number of rows, change A2:A20 in the formula to match the actual input range. Avoid using an unnecessarily huge range: it can make formulas harder to manage, and a range extending through many empty rows is not a substitute for filtering.
Keep names connected to their details
If each person has information in other columns, shuffle complete rows rather than names alone. For example, suppose names are in A2:A20, departments in B2:B20, and shifts in C2:C20. With the records in A2:C20, enter this formula in an empty area:
=SORTBY(A2:C20,RANDARRAY(ROWS(A2:A20)))
This returns each row as a unit, so the department and shift remain alongside the correct person. The same approach works for a roster with names, contact details, or assigned tasks: make the first argument the full block of related columns, and make the number of random sort values match its row count.
Do not sort only the name column if the other columns must stay matched to those names. A separate shuffle of names can break the relationships between rows. Also leave enough clear cells to the right and below the formula for the entire result to spill. If there are blank records inside the data block, decide whether they should be included or filtered out before using the shuffled output.
Why the order changes and how to keep it
RANDARRAY is volatile: Excel recalculates random values when the workbook recalculates. As a result, the shuffled list can change after an edit, a recalculation, or another action that causes dependent formulas to update. This is useful when you want a fresh draw, but it can be inconvenient if you need to preserve a particular order for a roster or assignment.
When you are happy with the shuffle, copy the result and paste it as values into another location. In Excel for Microsoft 365 on Windows, use Copy, then Home > Paste > Paste Values. This stores the current names as fixed entries rather than keeping a live formula that can reshuffle. On Mac, the Paste Values command is also available from the Home tab; ribbon labels or shortcut behavior can vary slightly by version and keyboard settings.
Another option is to keep the formula in one area and use the spilled output elsewhere only while you need a temporary random order. Do not assume the result is permanently fixed just because it has appeared on screen. If it matters that a particular order is retained, save a values-only copy or record it separately.
Use a helper column in older Excel versions
Excel versions without SORTBY or RANDARRAY can shuffle a list with a helper column and the built-in Sort command. With names in A2:A20, enter this formula in B2:
=RAND()
Fill the formula down beside every name. Each helper cell displays a random decimal. Select the whole data range, including both the names and helper values, then choose Data > Sort. Sort by the helper column, using Smallest to Largest or Largest to Smallest. The two directions both produce a shuffled order; they do not represent a meaningful ranking.
Include the entire row range when sorting. If Excel asks whether to expand the selection, choose that option so each random value moves with its name. Sorting only the helper column would separate the random keys from the names and could lead to incorrect results. Once the order is set, you can delete the helper column if it is no longer needed.
The helper values are also random formulas, so they can change when Excel recalculates. To preserve the sorted order, copy the shuffled names and paste them as values, or replace the helper formulas with their current values before sorting again. This method is useful in older desktop versions, but it takes more steps than the dynamic-array formula.
Common problems and practical limits
- #SPILL!: The formula’s output area is not empty, or a merged cell is blocking it. Clear the cells in the spill area and check for merged cells, then let Excel return the array again.
- The names are still in the original cells: The formula returns a separate shuffled result; it does not rearrange or overwrite the source list. Put the formula in a different area.
- The output changes unexpectedly: RANDARRAY recalculates. Paste the result as values when you need a lasting order.
- Related data no longer matches: The formula may have shuffled only the names. Use the full record range as the first SORTBY argument so entire rows move together.
- The formula is rejected: Check that the Excel version supports the functions and that the argument separator matches your regional settings. In versions without these functions, use the helper-column method.
There is also a difference between shuffling and selecting a winner. A shuffled list gives every entry a position, while choosing only the first item from a shuffle is a way to produce one random-looking selection. For an informal classroom activity or simple allocation, that may be sufficient. For a consequential drawing, use a process with appropriate controls, record the source entries and outcome, and follow any applicable rules; a spreadsheet formula alone does not establish that a draw is fair or auditable.
Frequently Asked Questions
Does Excel have a SHUFFLE function?
No. For supported versions, combine SORTBY with RANDARRAY to randomize a range. Older versions can use RAND in a helper column and sort the complete range.
How do I shuffle names without repeating them?
The formula rearranges each source entry once, so it does not create extra copies. It preserves duplicates already present in the source. Remove duplicate entries first if each name should occur only once.
How do I shuffle names but keep each person’s information with them?
Sort the whole row range, not just the name column. For records in A2:C20, use =SORTBY(A2:C20,RANDARRAY(ROWS(A2:A20))) in a clear area so every row’s details move together.
How can I stop the shuffled list from changing?
Copy the spilled result and use Home > Paste > Paste Values. That replaces the live formula with the current displayed entries, which will not reshuffle when RANDARRAY recalculates.
Can I shuffle names in Excel for the web?
Yes, if the workbook supports the functions. Enter the SORTBY and RANDARRAY formula in a blank cell as you would in desktop Excel. Make sure the spill area is clear; use the helper-column approach if the functions are unavailable in your Excel version.
Related
- Excel Flip Data Upside Down: Reverse Rows in Excel
- Excel Find Table Names: Where to Look and How
- Excel Find Shortcut Key: Search Cells Fast
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MID Function: Can It Return Variable-Length Text?Yes. Excel’s MID function can return a variable number of characters when you calculate its num_chars argument from…
- Excel COUNTIF and SUMIF With Dates: Practical ExamplesTo count or sum rows by date in Excel, use COUNTIF or SUMIF with a date criterion; for…
- COUNT vs COUNTIF in Excel: Which One Should You Use?COUNT counts cells containing numbers, while COUNTIF counts cells that meet a condition you specify. Use COUNT for…
- Excel Formula to Return Text Left of a Character in a CellTo return the text to the left of a character in Excel, use =LEFT(A2,FIND("-",A2)-1), replacing A2 with the…
- Excel Vertical Lookup: VLOOKUP, XLOOKUP, and TipsAn Excel vertical lookup searches down the first column of a table for a value, then returns related…
- How to Add a Formula in Excel for Addition: Simple StepsTo add numbers in Excel, select the cell where you want the answer, type a formula that starts…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.