Shuffle a List in Excel: Formulas, Sorting, and Tips

Formulas & functions · Updated November 20, 2026

Yes. In Microsoft 365, you can shuffle a list with a formula using SORTBY and RANDARRAY; in any recent Excel version, you can add a RAND helper column and sort by it. If your list is part of a table, shuffle the entire rows together so names stay matched with their related information.

Two practical ways to shuffle a list

The right method depends on whether you want a live formula result or a shuffled list you can sort and keep. A formula is convenient for generating a new arrangement, while a helper column works in older versions and gives you direct control over the sort.

  • Formula method: Use SORTBY with RANDARRAY to return a randomly ordered copy of a range. This requires Microsoft 365 or Excel 2021.
  • Helper-column method: Generate a random number beside each item with RAND, then sort the list by those numbers. RAND and Excel’s built-in sorting tools are available in older versions too.

Both methods rearrange existing items; they do not remove duplicates or create new entries. For example, if a list contains the same name twice, a shuffle still contains that name twice. A shuffle also differs from selecting a random sample: a full shuffle returns every item, while a sample returns only some of them.

Shuffle a list with a formula

Suppose the names to shuffle are in A2:A20, with no header included in that range. In an empty cell outside the range, enter this formula:

=SORTBY(A2:A20,RANDARRAY(ROWS(A2:A20)))

The formula spills a reordered version of the names into the cells below it. Its output contains the same entries as A2:A20, each appearing once for each occurrence in the source range; the specific order is random and changes when Excel recalculates. Because the order is not fixed, there is no single set of names that this example always returns.

SORTBY orders the source range using the random values generated by RANDARRAY. ROWS counts the entries in the source range, so RANDARRAY creates the corresponding number of sort keys. The formula assumes the cells where results will spill are empty. If Excel shows a #SPILL! error, check for existing content or merged cells in the output area.

SORTBY and RANDARRAY are available in Microsoft 365 and Excel 2021, including supported versions of Excel for the web. The same formula approach can shuffle a multi-column range: set the source range to all the columns in each record, and use RANDARRAY with the number of rows as the sort keys. Keep the header row out of the source range, or it could be shuffled like an ordinary record.

Shuffle complete rows with a helper column

If your list has related columns—for example, a person’s name, department, and score—sorting only the names would break the relationships. Instead, add one random key per row and sort the complete dataset by that key.

  1. Insert a temporary column beside the data and give it a clear heading, such as Random.
  2. With records in A2:C20 and the helper column in D, enter the following in D2:

With records in A2:C20 and the helper column starting in D2, use this formula and fill it down through D20:

=RAND()

Each helper cell displays a random decimal between 0 and 1. The precise values vary, so there is no fixed numeric result; each row receives its own sort key. Next, select the full dataset, including the helper column, and choose Data > Sort. In the Sort dialog, choose the Random column under Sort by, then choose Smallest to Largest or Largest to Smallest. Either direction gives a randomized order.

When prompted, choose to expand the selection so Excel sorts the related columns together. If you select only the helper cells or only one data column, the rows can become mismatched. After sorting, you can delete the temporary helper column; the records remain in their new order. This procedure works in desktop Excel for Windows and Mac. Excel for the web also provides sorting tools, although the dialog’s appearance can differ.

Make the shuffled order stay put

Random formulas are volatile: Excel can recalculate them when the workbook recalculates, which can create a different shuffle. That behavior is useful when you want to reshuffle, but inconvenient if you are preparing a fixed list for printing, a classroom activity, or a one-time assignment.

For a formula-generated shuffle, copy the spilled results and paste them as values into a clear area. In Excel for Windows, use Home > Paste > Paste Values; the Paste Values option is also available from the right-click menu. On Mac, use the Paste Values command from Home or the context menu. The pasted entries no longer depend on the random formula, so later recalculation will not reorder them.

For the helper-column method, the sorted rows are already in place. Removing the RAND helper after the sort leaves the current row order intact, although another sort or manual rearrangement can of course change it. If you need to preserve the original order as well, make a copy of the data before shuffling.

Choose the method that fits your Excel version

Use the formula approach when you have Microsoft 365 or Excel 2021 and want a separate, automatically generated shuffled result without rearranging the source data. It is quick for a simple list and easy to refresh. Its main trade-off is that the result can change when Excel recalculates, so paste values if you need a stable copy.

Choose a helper column when you need compatibility with older desktop versions, want to shuffle whole records in place, or need to inspect the random keys used for sorting. It involves a few more clicks, but the process is visible and does not rely on dynamic-array formulas. If you have a filtered table or a complicated dataset, first confirm that the intended records are included and that the entire record range will be sorted together.

Excel for Mac has RAND and sorting features as well as current dynamic-array functions in supported Microsoft 365 and Excel 2021 editions. Excel for the web supports many modern functions, but available features can depend on the account and update channel. If a function name is not recognized, check the Excel version rather than assuming the formula is mistyped.

Common problems and ways to avoid them

  • The formula returns #NAME?: Your Excel version may not support SORTBY or RANDARRAY. Use the RAND helper column instead, or open the workbook in a supported version.
  • The formula returns #SPILL!: Clear the cells below and beside the formula where its output needs to appear. Also check for merged cells in that area.
  • Names no longer match their details: Sort all columns of each record together. In the Sort dialog, make sure the range includes every related column and expand the selection if Excel asks.
  • The order changes unexpectedly: RAND and RANDARRAY recalculate. Paste the results as values or remove the helper column after sorting to keep the current arrangement.
  • Some rows look unchanged: Random keys can be close together, and a small shuffle may by chance resemble the original order. Sorting is based on the keys, not on a promise that every item will move.

A random sort is generally suitable for informal shuffling, such as mixing a reading list or randomizing practice questions. It is not a substitute for a documented, independently verifiable process where fairness or auditability is important. RAND produces pseudorandom values, and a worksheet shuffle does not itself provide a record of how the draw was conducted.

What a shuffle does—and does not—guarantee

A normal shuffle changes the sequence, not the contents. It keeps duplicate entries, blank entries included in the selected range, and every other selected row. Review the range before sorting: an accidental blank row or an extra record outside the selected range can lead to a result that looks incomplete or inconsistent.

Neither method promises that every item will occupy a different position from its previous one. A random shuffle can leave an item in its original place, or even produce an order that looks nearly unchanged. Also, RAND keys are random decimal values rather than unique identifiers. Two keys could theoretically match; if tied keys matter for a particular workflow, add a secondary sort key. For ordinary lists, an exact tie is unlikely, but a simple spreadsheet shuffle should not be presented as a guaranteed drawing system.

Frequently Asked Questions

Can Excel shuffle a list without a formula?
Yes. Put RAND formulas in a helper column, sort the full list by that column, and then remove the helper if you no longer need it. The formula-based SORTBY method is optional.

How do I shuffle names without separating them from other data?
Give each row a random helper value, then sort the entire data range by that helper column. Do not sort the names column by itself if other columns contain information belonging to those names.

How do I reshuffle the list?
For a formula output, recalculate the workbook or edit the formula so Excel recalculates it. For the helper-column method, recalculate the RAND values and sort by the helper column again. The current order may change each time.

Can I shuffle only part of a list?
Yes. Set the formula range to the items you want shuffled, or select only the intended records and their related columns for the helper-column sort. Keep headers out of the data range and take care not to omit linked columns.

Can I make a random order that never changes?
Yes. Copy the formula results and paste values into another range. If you used a helper column, sort the records and then delete that column. Both choices preserve the current order until you deliberately rearrange the data.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.