Randomize Cells in Excel: Formulas, Steps, and Tips

Formulas & functions · Updated November 21, 2026

Yes. In Excel for Microsoft 365 on Windows, you can randomize a list with a formula such as =SORTBY(A2:A10,RANDARRAY(ROWS(A2:A10))), or add random numbers beside the list and sort by them. If the cells are parts of records, shuffle the entire rows together so names, dates, and other related values stay matched.

What Randomizing Cells Means

People use “randomize cells” to mean a few different things. The most common task is to shuffle a list into a random order, such as mixing a roster of names or reordering survey responses. Another is to shuffle complete records, where each row contains one item and its related details. A third is to select a random sample from a larger list.

Those tasks are different from generating random numbers or replacing the existing cell contents with randomly chosen values. Excel’s RAND function generates a decimal from zero up to, but not including, one; it does not directly rearrange a range. To shuffle existing values, use random numbers as sort keys or use a dynamic-array formula that sorts by generated random keys.

Before changing a sheet, decide what must stay together. If column A contains names and column B contains each person’s department, randomizing just column A would assign names to the wrong departments. In that case, the unit to shuffle is the whole row, not an individual cell.

Shuffle a List with a Formula

For a quick shuffle in Microsoft 365, enter a formula in an empty cell outside the source list. It returns a reordered copy and leaves the original range in place. The returned order can change whenever Excel recalculates.

With the values to shuffle in A2:A10, enter this formula in an empty cell. It returns the nine values in a randomized order; the particular order varies each time it recalculates:

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

SORTBY sorts one range according to another array, and RANDARRAY supplies a random key for each row. ROWS counts how many keys are needed. Because this is a dynamic-array formula, the results spill into the cells below the formula automatically. Keep those spill cells empty, or Excel may show a #SPILL! error.

To shuffle records, include all the columns in the range being sorted. With names in A2:A10, departments in B2:B10, and start dates in C2:C10, place the formula in an empty area; it returns all nine rows in a new order while keeping each row’s information together:

=SORTBY(A2:C10,RANDARRAY(ROWS(A2:A10)))

This approach requires a version that supports SORTBY and RANDARRAY, such as Microsoft 365 or Excel 2021. If Excel does not recognize either function, use the helper-column method below or check which Excel version is installed.

Shuffle with a Random Helper Column

A helper column is useful when you want to use Excel’s familiar Sort command, when you need to inspect the random keys, or when a dynamic-array formula is not available. It also works well when the data is in a table.

  1. Insert a blank column beside the list or table and label it something like Random key.
  2. With the list in A2:A10 and the helper column in B, enter the following in B2. It returns one random decimal for the first item:

=RAND()

Fill the formula down through B10. Each row now has a random key. Select the entire data range, including the helper column, and choose Data > Sort. Sort by the Random key column, using either smallest-to-largest or largest-to-smallest. The resulting order is randomized in either direction.

Do not select only the helper column when sorting: that would separate the keys from their records. If Excel asks whether to expand the selection, choose to expand it so the complete rows move together. When you no longer need the keys, you can delete the helper column after sorting.

Select a Random Sample Without Repeats

A shuffled list is also a convenient way to draw a sample without selecting the same entry twice. For a short sample that should update automatically, a dynamic-array formula can shuffle the source range and return only its first few entries.

With nine names in A2:A10, put this formula in an empty cell to return three different names in a randomized order. The exact names can change on recalculation:

=INDEX(SORTBY(A2:A10,RANDARRAY(ROWS(A2:A10))),SEQUENCE(3))

The formula uses SEQUENCE(3) to request the first three positions in the shuffled list. Since a shuffle reorders the original entries, it does not draw the same row twice. Make sure the source range contains at least as many entries as the requested sample size, and leave room for the results to spill. This formula uses dynamic-array functions available in Microsoft 365 or Excel 2021.

For a sample that will be recorded or shared, turn the result into fixed values after choosing the draw. Otherwise, recalculation can change which entries appear, making a later review difficult.

Keep a Randomized Order from Changing

Random formulas are volatile: Excel can recalculate them when the workbook recalculates, so a displayed shuffle is not automatically permanent. A change elsewhere in the workbook, or pressing F9 on Windows, can produce a different order. On a Mac, the recalculation shortcut may depend on the keyboard settings; use Formulas > Calculate Now if needed.

To preserve a shuffle, copy the randomized results and paste them as values. Select the output, choose Home > Copy, then use Home > Paste > Values in the same place or in a new range. This replaces the formulas with their current displayed results. With the helper-column approach, sorting has already changed the row order, but the RAND keys themselves can still recalculate; remove or replace those keys if you need a stable record of the sort.

If the result is important, save a copy of the workbook before making a permanent change. Keeping the original list also makes it easier to verify the shuffled data or run a new randomization later.

Common Problems and Misconceptions

  • Only one column moved. Sort the full record range, not a single column, so related values remain on the same row.
  • The formula shows #SPILL!. Clear cells in the output area, merged cells, or other obstructions that prevent the dynamic result from expanding.
  • The order keeps changing. That is expected while random formulas are active. Copy and paste values to keep a particular result.
  • The random keys are decimal numbers. That is normal for RAND; they are sorting keys, not the shuffled data. You can hide or delete the helper column afterward.
  • Some entries appear identical. A shuffle moves entries; it does not make duplicate values unique. If the source contains the same name twice, both occurrences remain in the result.

A common misconception is that sorting from A to Z is random. Alphabetical sorting is predictable. Sorting by generated random keys is what changes the order unpredictably. Also, a random shuffle is not the same as a guarantee of fairness in a high-stakes draw: workbook formulas are general-purpose spreadsheet tools, not a substitute for a documented, auditable selection process where one is required.

Which Method Should You Choose?

Choose the method that fits the size and purpose of the task:

  • Dynamic formula: Best for a quick, reversible shuffled copy in Microsoft 365 or Excel 2021. The source remains untouched, but the output can change at recalculation.
  • Helper column and Sort: Best for compatibility, visual control, or an ordinary one-time sort. It is easy to understand, but you must select the entire record range and manage the random keys.
  • Random sample formula: Best for a sample that updates automatically and has no repeated source rows. Paste values if the selected sample must remain fixed.

On Excel for Mac, the formulas work where the required functions are supported, and the general sort workflow is similar. Ribbon names and paste controls can vary slightly by release. Excel for the web also supports many modern formulas, though available features and interface details can differ from the Windows desktop app. If a formula is unavailable, the helper-column-and-sort workflow is the straightforward alternative.

Frequently Asked Questions

Can I randomize cells without changing the original list?
Yes. Put the SORTBY formula in an empty area of the sheet. It creates a shuffled copy while leaving the source range unchanged. Paste the result as values if you want to keep that copy fixed.

How do I randomize rows in Excel without mixing up the data?
Sort the entire record range, including every column that belongs to each record. With a formula, include all those columns in the range passed to SORTBY; with a helper column, select the complete table before sorting by the random keys.

Can Excel pick random items without duplicates?
Yes. Shuffle the source list and take the first required number of entries. Each source row appears once in the shuffled list, so the selection has no repeated rows unless the source itself contains duplicate entries.

Why does my randomized list change when I edit something?
RAND and RANDARRAY recalculate, so their random keys can change and produce a new order. Copy the result and paste values to preserve the current arrangement.

Can I shuffle a list in older Excel versions?
Yes. Add =RAND() beside each item, fill it down, then sort the full range by that helper column. This avoids relying on newer dynamic-array functions.

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.