Excel Formula to Randomize a List (With Examples)

Formulas & functions · Updated November 19, 2026

To randomize a list in Microsoft 365, enter =SORTBY(A2:A20,RANDARRAY(ROWS(A2:A20))) in an empty cell, replacing A2:A20 with your list’s range. Excel spills the same entries into a newly shuffled order; the original list stays where it is.

The formula and where to put it

Put the formula outside the source range and leave enough blank cells below it for the results. For a list of 19 items in A2:A20, for example, enter the formula in C2. The shuffled list spills down from C2, with one item per row. Keep the header out of the range: if A1 is a heading, using A2:A20 prevents Excel from moving the heading into the shuffled results.

For a five-item example, assume the entries are in A2:A6 and you enter the formula in C2. It returns those five entries in a new order, such as Apricot, Pear, Apple, Plum, Banana. That is one possible result, not a fixed output: recalculation can produce a different order.

The formula uses functions available in Microsoft 365 and Excel 2021 or later. It creates a separate randomized result rather than rearranging the source cells. That is useful when you want to preserve the original order, compare the shuffled version with the source, or keep an untouched copy of the data.

How the shuffle works

RANDARRAY generates a column of random numbers, and ROWS tells it how many numbers to generate. SORTBY then sorts the list using those numbers as sort keys. Because each item gets a key, the items are reordered without being removed or repeated by the shuffle.

The formula does not select new list entries or make duplicate values unique. If the source contains Apple twice, both entries remain in the result. The random numbers determine order; they do not change the list’s contents. This distinction matters when someone asks for a randomized list without duplicates: a shuffle preserves the original number of occurrences, so duplicates must be removed separately if that is the goal.

Each recalculation can generate fresh keys, so the displayed order is temporary unless you freeze it. The result is also a dynamic array: one formula produces multiple cells. Avoid typing into the cells where the results need to spill, or Excel will show a spill error instead of displaying the full list.

Shuffle complete rows, not just one column

If each name belongs with other information, sort the whole data range as a unit. For example, suppose names are in A2:A6, departments in B2:B6, and locations in C2:C6. With the complete records in A2:C6 and the formula entered in E2, use:

=SORTBY(A2:C6,RANDARRAY(ROWS(A2:A6)))

The result contains five rows and three columns, rearranged together. A name stays paired with its department and location. A formula that randomizes only A2:A6 would shuffle names by themselves and leave the other columns in their original order, which can silently corrupt the records.

Include every column that belongs to each record in the range being sorted. Leave the header row outside the formula range, then add or copy the header above the spilled result if you want a labeled output. The spill area must be empty across all three columns and all five result rows.

Excel Tables are convenient for storing source data, but a dynamic-array formula cannot spill inside a table. Put the formula in ordinary worksheet cells outside the table. If the list changes size often, check that the formula range includes the current records; a fixed range such as A2:C6 will not automatically expand just because new rows are added below it.

Randomize a list in older Excel versions

Excel versions without SORTBY and RANDARRAY can use a helper column. The helper assigns a random number to each record, and you sort the records by that column. Assume the complete records are in A2:B6 and the helper values will go in C2:C6. Enter this formula in C2, then fill it down through C6:

=RAND()

Each helper cell displays a decimal between 0 and 1. The exact values vary; for illustration, suppose C2:C6 show 0.74, 0.18, 0.91, 0.42, and 0.33. Select the data and helper columns together, then choose Data > Sort. Sort by column C, smallest to largest, and the records will appear in the order associated with those five values. The source records remain paired because the entire rows were selected for sorting.

  1. Put a header such as Random key in C1, and fill =RAND() down beside every record.
  2. Select the full data range, including the helper column and headers. Do not select only the random keys.
  3. Choose Data > Sort, choose the helper column as the sort column, and select Smallest to Largest or Largest to Smallest.
  4. Remove the helper column if you no longer need it, or keep it if you may want to sort by the same keys again.

In this example, five records produce five random keys and therefore five sorted records. If two keys happen to match, their relative order may not be dependable, although matching random decimals are uncommon. This helper method is also useful when you want to inspect the keys or sort the original records in place rather than create a separate spilled result.

Keep the randomized order from changing

RAND and RANDARRAY are volatile functions: Excel can recalculate them after workbook changes or a recalculation command. That means a list may reshuffle when you edit an unrelated cell, depending on calculation settings. This behavior is useful when you want a fresh order, but inconvenient when you are preparing a roster, assigning a review sequence, or recording a draw.

To keep the current result, copy the spilled output and paste it as values into a different empty range. In Microsoft 365 for Windows, use Home > Paste > Paste Values, or right-click the destination and choose the values paste option. The resulting cells contain fixed entries rather than formulas, so later recalculation will not reshuffle them. For a helper-column workflow, you can similarly copy the sorted records and paste values elsewhere before clearing the helper keys.

If you only want to refresh the order on demand, leave the formula in place and recalculate when needed. In Excel for Windows, F9 recalculates open workbooks; on some Mac keyboards you may need Fn+F9, depending on the function-key setting. You can also use Formulas > Calculate Now. Calculation settings and keyboard behavior can differ by platform, but the principle is the same: recalculation can generate new random keys.

Common problems and useful checks

  • A spill error appears: Clear the cells blocking the output, including any invisible spaces or merged cells in the spill area. The formula needs room for every result.
  • The formula name is not recognized: Your Excel version may not include the dynamic-array functions. Use the helper-column method, or check that the function names and commas were entered as shown.
  • Rows no longer match: Make sure the formula’s first argument includes all related columns. In the manual method, select the data and helper column together before sorting.
  • The order keeps changing: This is expected when random functions recalculate. Paste the results as values if you need a stable order.
  • The heading appears among the results: Exclude the header row from the formula range, or keep it separate when selecting data for a manual sort.
  • Some items seem repeated: A shuffle preserves duplicates already in the source. Check the original list before assuming the formula created extra copies.

Also check for blank cells within the source range. A blank is part of the selected range and can be returned as a blank result, so a range that extends past the actual list may appear to contain an extra item. If the list’s size changes, update the range or use a source range that deliberately includes the intended rows.

Which randomization method should you choose?

Use the spill formula when you want a quick, separate randomized copy and have Microsoft 365 or Excel 2021 or later. It is compact, easy to update, and leaves the source untouched. Choose the helper column when you have an older Excel version, need to sort the records in place, or want to see the random keys used for sorting.

For a simple classroom activity or a randomized review order, either method is usually practical. For an official drawing, prize allocation, or any situation where fairness must be demonstrated, do not treat a volatile spreadsheet formula as an auditable process by itself. Save a copy of the workbook, document the source list and procedure, and follow the applicable rules for the draw. Excel’s random functions are intended for ordinary spreadsheet calculations, not cryptographic security.

Excel for the web supports many dynamic-array formulas, including the shuffle approach in current Microsoft 365 accounts, though interface details and recalculation behavior can vary. On Mac, the formula syntax is the same; the main differences are keyboard shortcuts and some interface details. If a formula is unavailable in the Excel version you use, the helper-column approach is the straightforward fallback.

Frequently Asked Questions

How do I randomize a list in Excel without duplicates?

A shuffle does not add or remove entries, so it preserves any duplicates already in the source. If every source value is unique, the shuffled result will also contain unique values. If the source has repeated entries and you need each value only once, remove or filter duplicates before shuffling.

Can I randomize rows while keeping the columns together?

Yes. Include every related column in the array being sorted, such as A2:C20, and use a random key for each row. The row-based example above does this, so each record’s fields move together rather than becoming mismatched.

Why does my randomized list keep changing?

The formula uses volatile random functions that can generate new keys when Excel recalculates. Copy the result and paste values if you need to preserve the current order, or leave the formula active when you want it to refresh.

Can I randomize a list in older Excel?

Yes. Add =RAND() in a helper column beside each item, fill it down, and sort the complete data range by that column. This works without the dynamic-array formula, provided your Excel version supports the RAND function.

Does randomizing a list change the original data?

The SORTBY formula creates a separate result and does not rearrange the source cells. The helper-column method changes the order of the selected rows when you sort them, so keep a backup or copy the records first if preserving their original order matters.

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.