To randomize a list in Excel, add a helper column with =RAND(), fill it down beside your data, then sort the entire data range by that helper column. For a random number or a single random item, use RANDBETWEEN instead. These methods work in Microsoft Excel for Microsoft 365 on Windows; because random formulas recalculate, you can copy and paste their results as values when you need to keep a shuffle fixed.
What You’ll Need
- An Excel workbook with the values or records you want to randomize.
- A spare column for random sort keys if you are shuffling a list or table.
- A saved copy of the workbook if the data is important. Sorting changes row order, so a copy makes it easy to return to the original arrangement.
- Excel for Microsoft 365 on Windows for the steps below. The basic formulas also work in many older Excel versions. Excel for the web has similar formula and sort options; on Mac, use the Data tab’s Sort controls, with the same general process.
Step-by-Step
- Prepare the data you want to shuffle. Put each item in a separate row, or keep each record together across its row. If the range has column headings, include them when sorting and tell Excel that the data has headers. Remove completely blank rows within the range if you want every record included. For example, if student names are in column A and their class details are in columns B and C, all three columns need to stay together when you shuffle.
- Add a random helper value beside every data row. With your records in A2:C20 and row 1 containing headers, enter
=RAND()in D2. This gives the first record a random decimal from 0 up to, but not including, 1. Fill the formula down through D20: select D2 and drag its fill handle to D20, or copy D2 and paste it into D3:D20. Each row now has a random sort key. If your data starts or ends on different rows, adjust the formula range and fill-down destination to match. - Sort the complete range using the helper column. Select A1:D20, including the headings, then choose Data > Sort. In the Sort dialog, select the helper-column heading, such as Random, in the Sort by box; choose Cell Values and either Smallest to Largest or Largest to Smallest. Check My data has headers if your first row contains headings, then select OK. Excel moves each complete record together and orders the rows by their random keys. Do not sort only column D, or the keys will become separated from the records they were meant to shuffle.
- Keep the shuffled order if it must not change. RAND is volatile: Excel can generate new values during recalculation, which can change the sort keys. After sorting, select the helper values, copy them, then choose Home > Paste > Paste Values to replace the formulas with fixed numbers. If you no longer need the helper column, delete it after converting the formulas to values. The records remain in their current order until you sort them again. You can also copy the shuffled records to another worksheet as values if you want a separate fixed version.
- Generate one random whole number when you need a number, not a shuffled list. With an empty cell selected, enter
=RANDBETWEEN(1,100)to return an integer from 1 through 100, including both endpoints. Change 1 and 100 to your required lowest and highest values. For example, for a random day number from 1 through 31, use=RANDBETWEEN(1,31). The result may change when Excel recalculates, so copy and paste as values if you need to record a particular draw. - Pick one random item from a list. With choices in A2:A20, enter
=INDEX(A2:A20,RANDBETWEEN(1,ROWS(A2:A20)))in a blank cell. INDEX returns the item at the position selected by RANDBETWEEN, and ROWS counts how many choices are in the range. This example treats each nonblank row as one choice, so remove blank entries if they should not be selected. The same item may be picked again after recalculation; this method is not a no-repeat drawing. - Shuffle a list with one dynamic-array formula, if preferred. With values in A2:A20, enter
=SORTBY(A2:A20,RANDARRAY(ROWS(A2:A20)))in an empty cell. In Microsoft 365, the formula returns a shuffled list that spills into the cells below. Keep those spill cells empty, or Excel will show a spill error. The source list stays in place, and the displayed order can refresh when the workbook recalculates. For a fixed version, copy the results and paste values into a clear range. If you need to shuffle entire records, use the helper-column method or apply SORTBY to the full data range so each row remains intact. - Make a fresh shuffle when required. Re-sort by the RAND helper column to change the order; new random values may also appear when Excel recalculates. Press F9 on Windows to recalculate formulas in a workbook set to automatic calculation. On Mac, use Fn+F9 on keyboards where function keys control hardware features, or choose Formulas > Calculate Now. Remember that changing the keys alone does not rearrange a list that has already been sorted: sort the full data range again after the keys change.
Common Mistakes
Sorting only the helper column: This separates the random keys from the names or records. Always select the complete data range, including every column that belongs to each row, before sorting. If Excel asks whether to Expand the selection, choose that option so associated columns move together.
Including the heading as a data value: If Excel treats the header as an ordinary record, it may move into the shuffled list. Include the header row in the selected range and check My data has headers in the Sort dialog. If the range has no heading, do not enable that setting.
Expecting RAND to stay still: RAND and RANDBETWEEN are volatile functions. They can produce different results after recalculation, edits, or reopening the workbook. Convert results to values when the exact outcome matters, rather than relying on a formula to preserve a draw.
Assuming a random sort key guarantees a unique order: RAND values can theoretically match. A tie is unusual for a short list, but Excel may place tied rows in either order. For most everyday shuffles this is fine; if a specific, auditable sequence is essential, save the resulting order as values and review it.
Using a single-item picker to draw without replacement: INDEX with RANDBETWEEN can return an item that has already appeared. To draw several different records without repeats, shuffle the complete list once and take the first required number of rows.
Tips
Use a descriptive heading such as Random Key for the helper column so you can identify it in the Sort dialog. Keeping the helper beside the data also makes it clear which values control the order. When the data is formatted as an Excel table, add the helper as a table column and make sure the sort applies to the whole table.
To select a sample without replacement, shuffle the full data range with the RAND helper method, then take the first few rows. For example, after shuffling 80 volunteer records, the first 10 rows form a sample of 10 distinct records. Save those selected rows as values if you need the same sample later. A random shuffle does not guarantee that a small sample will represent every subgroup; it only gives each row a randomized position.
Choose the simplest method for the job. Use RAND plus sorting when you want to rearrange records and preserve row relationships. Use RANDBETWEEN for a whole-number draw, and use INDEX with RANDBETWEEN when you want one item from a list. SORTBY with RANDARRAY is convenient for a quick shuffled output in Microsoft 365, but the helper-column method is easier to inspect and works in more Excel versions.
For repeatable work, keep an untouched source sheet and make the randomized copy on another sheet. Note the date, the number of records, and any selection rules alongside the saved results. Excel’s built-in random formulas do not provide a seed setting in these examples, so saving the generated values is the practical way to preserve a particular outcome.
Troubleshooting
The list did not shuffle: Check that the RAND formulas were filled down and that the sort was performed after the keys were generated. If all keys appear unchanged, press F9 on Windows to recalculate, then sort the whole range again. In Excel for the web, editing a formula or using the workbook’s recalculation options can refresh results; menu availability can vary by browser and workbook settings.
Rows no longer match their details: Undo immediately with Ctrl+Z on Windows, then select the full dataset and sort again. On Mac, use Command+Z. If you have already made more edits, restore the original from a saved copy or version history rather than trying to match rows by eye. For a sortable range with blank columns between fields, make a clean contiguous copy first.
Excel displays a spill error for SORTBY: Clear any content, including spaces, from the cells where the shuffled results need to appear. Check that the output area is not blocked by merged cells and that the formula is not placed inside an Excel table, where a dynamic array cannot spill normally. Put the formula in a clear area outside the table.
A random formula shows the same result repeatedly: A repeated value can happen by chance, especially with a small range such as 1 to 3. Press F9 to recalculate and try again. If calculation is set to Manual, choose Formulas > Calculation Options > Automatic in Excel for Microsoft 365 on Windows. Excel for Mac has calculation options on the Formulas tab as well, though the exact ribbon layout can differ.
The formula returns an error or includes an unwanted blank: Verify that the referenced range is correct and that there are no merged cells or unexpected blank entries in the list. For a random item, the INDEX formula counts every row in its stated range, including blank rows. Tighten the range to the actual choices, or remove blanks first. If your installation uses a regional list separator other than a comma, Excel may require that local separator when entering formulas.
Frequently Asked Questions
Can Excel shuffle a list without a helper column?
Yes. In Microsoft 365, enter SORTBY with RANDARRAY beside or above the source list, for example =SORTBY(A2:A20,RANDARRAY(ROWS(A2:A20))) when the list occupies A2:A20. The helper-column method is preferable when you need to sort complete records, inspect sort keys, or use an older Excel version.
How do I randomize rows without separating their columns?
Select the entire data range, not just the names or random keys, and sort by a RAND helper column. Excel then moves each row as a unit. If the records are in an Excel table, sort the table by its helper column.
How can I select random names without duplicates?
Shuffle the full list once, then take the first required number of names. This makes the selected names distinct as long as each name appears only once in the source list. If duplicate names represent separate people, use a unique ID or another identifying field to distinguish them.
Why did my random result change?
RAND, RANDARRAY, and RANDBETWEEN recalculate, so their outputs can change after a recalculation or other workbook activity. Copy the results and choose Home > Paste > Paste Values to preserve the current values.
Can I make Excel produce the same shuffle every time?
Not with these random formulas alone, because they do not use a user-specified seed. Generate the shuffle, then paste the random keys or shuffled results as values and keep that saved copy. Do not recalculate or re-sort the preserved version unless you want a new order.
Does the method work in Excel for Mac or the web?
The formulas and overall approach are available in current Excel for Mac and Excel for the web. Ribbon placement and keyboard shortcuts can differ: on Mac, use the Data tab’s sorting controls and Command+Z to undo; in the web app, use its Data sorting options. Dynamic-array formula availability depends on the Excel version.
Related
- Print an Excel Document on One Page: Fit to Page
- Print Excel on One Page: Fit a Worksheet to Paper Size
- Paste Transpose in Excel: Shortcut and Steps
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Workbook Comparison Tool: How to Compare FilesFor Microsoft Excel for Microsoft 365 on Windows, the dedicated workbook comparison tool is Spreadsheet Compare, available with…
- Excel Power Query Tutorial for BeginnersTo use Power Query in Excel for Microsoft 365 on Windows, select your data source from the Data…
- How to Insert a Vertical Line in Word: 5 Easy MethodsTo add a vertical line in Word, choose Insert > Shapes > Line, then drag while holding Shift…
- How to Insert a Line in Word Online in Your BrowserTo insert a line in Word for the web, open Insert > Shapes, choose a line, then drag…
- Excel Cell Shortcut Keys for Selecting and Editing DataFor Excel for Microsoft 365 on Windows, the most useful cell shortcuts are F2 to edit a cell,…
- How to Add a Bottom Header in Microsoft Word EasilyTo put text at the bottom of a page in Word, add a footer: choose Insert > Footer,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.