To randomize names in Excel, add a helper column and use a random number for each row. With names in A2:A20, enter =RAND() in B2, fill the formula down, then select the complete list and sort by column B from smallest to largest. The names will appear in a shuffled order. You can use the same method in Microsoft Excel for Microsoft 365 on Windows; it also works in recent desktop versions of Excel.
This approach shuffles the existing rows rather than changing the names themselves. It is useful for making a random order for a class list, drawing order, assignment rotation, or other non-sensitive task. Before starting, decide whether any information in other columns must stay attached to each name, because those columns need to be included in the sort too.
What You’ll Need
- An Excel workbook with the names in one column, ideally with a clear header in the first row.
- A blank column beside the names for the random helper values. If the list has related details, leave room for the helper column or put it in an unused column.
- Excel for Microsoft 365 on Windows, or another recent desktop version of Excel. The RAND helper method does not require a special add-in or a newer dynamic-array function.
- A saved copy of the workbook if the list is important. Sorting changes the order of rows, so a backup makes it easy to restore the original arrangement.
Step-by-Step
-
Prepare the list. Put one name in each row, with no completely blank rows in the middle. If row 1 contains a heading such as Name, keep it as a header rather than treating it as a name to shuffle. Check that names are not split across multiple rows and that any information linked to a person, such as a team or email address, is in the same row.
For example, suppose names are in A2:A20 and A1 contains the heading Name. If there are related details in columns C and D, those details must move with their corresponding names. Do not sort only column A while leaving the other information behind.
-
Add a helper-column heading. In the empty cell immediately beside the name heading, type a label such as Random Key. In this example, enter the label in B1. A heading helps Excel identify the helper field in the Sort dialog and helps you avoid sorting the header as if it were a data value.
-
Generate a random value for the first name. With names in A2:A20 and the helper values intended for B2:B20, enter
=RAND()in B2. Excel displays a decimal between 0 and 1. The exact value is not meaningful; its purpose is to give each row a key that can be sorted. -
Fill the formula down the list. Select B2 and drag the fill handle at the lower-right corner of the cell down through B20. You can also copy B2, select B3:B20, and paste. Each row should now have its own RAND formula and a displayed random decimal. Confirm that the last name has a helper value and that there are no gaps in the helper column.
If your list has a different number of names, fill the formula only through the last occupied name row. Do not include unused rows unless you intentionally want blank rows in the shuffle.
-
Select all rows and columns that belong together. Select the complete data range, including its header row, all name rows, the helper column, and any associated details. In the example with names in column A and helper values in B, select A1:B20. If department, score, or contact details are in C and D, select A1:D20 instead. This is the key safeguard that keeps each row’s information together.
-
Sort by the random keys. On the ribbon, choose Data > Sort. In the Sort dialog, turn on My data has headers if the selected range includes a header row. Set Sort by to Random Key, then set Sort On to Cell Values and Order to Smallest to Largest. Choose OK. Excel rearranges the selected rows according to the random decimals, producing a shuffled name order.
If Excel displays a warning that asks whether to expand the selection, choose Expand the selection. That option sorts adjacent row data together. Choosing Continue with the current selection can detach names from their related details.
-
Check the result and decide what to do with the helper column. Scan the shuffled list to make sure the header stayed at the top, every expected name is present, and associated details still match. Once satisfied, you can keep the Random Key column if you may want another shuffle, or remove it. To remove it without affecting the sorted order, select the helper cells and use Home > Clear > Clear Contents, or delete the helper column if it is no longer needed.
Clearing the random values leaves the current row order in place. The list does not automatically reshuffle just because RAND recalculates; Excel changes the random keys, but you must run the sort again to rearrange the rows.
-
Keep a shuffle that you want to preserve. Once the rows are sorted, the displayed names are ordinary cell values, but the helper formulas can generate new keys the next time the workbook recalculates. If you want to preserve the keys as well, copy the helper cells and use Home > Paste > Paste Values. You can then sort by those fixed values again without the keys changing on recalculation. If you only need to preserve the visible order, remove the helper column after sorting and save the workbook.
Common Mistakes
- Sorting only the name column. If each person has details beside their name, sorting just the names breaks the row relationships. Select the entire data range before sorting, including every column that should move together.
- Including the heading as a name. If Excel does not recognize the header, it may place Name or Random Key among the shuffled entries. Include the heading row and check My data has headers in the Sort dialog.
- Leaving blank rows or blank helper cells. A gap can make Excel detect only part of the list, and blank keys may sort separately. Remove unintended blank rows and fill the helper formula to the final name row before sorting.
- Expecting the order to change automatically. RAND can recalculate and show different numbers, but it does not automatically sort the rows again. Run Data > Sort by Random Key after recalculation to produce a new shuffle.
- Assuming the names will be unique. The method shuffles rows; it does not remove duplicate names. If the same name appears twice in the original list, both entries remain. Check for duplicates separately if duplicates are not wanted.
- Deleting the helper column before sorting. The random values are what determine the order. Keep them until the sort is complete and checked; remove them only afterward.
Tips
- Use a table for lists that may grow. Select the list and choose Insert > Table, making sure the table has headers. A table makes it easier to keep the data in a single structured range. Add the random-key field as a table column, fill its formula through the rows, and sort using that column. Confirm the whole table is being sorted.
- Keep a stable copy of the original. Save a second worksheet or workbook copy before changing the order if you will need to return to the original sequence. An added Original Order column containing 1, 2, 3, and so on can also let you restore the initial arrangement by sorting on that field later.
- Use one key per row. Each name needs its own RAND formula. Copying and pasting a single displayed random number into every row does not create a useful shuffle because the keys are identical.
- For a new shuffle, recalculate and sort again. Recalculate the worksheet, then repeat Data > Sort with Random Key as the sort field. Because RAND is volatile, other workbook calculations may also refresh the keys; clear or paste values if you need them to remain fixed.
- Protect confidential information. Randomizing a list is not anonymizing it. Names and identifying details remain in the workbook, and a shuffled order is not a privacy or security measure.
- Keep the task proportionate. RAND-based sorting is a practical way to shuffle a normal list, but it is not a cryptographic random-number process. Do not use it as the basis for a security-sensitive draw or a process that requires an audited, independently verifiable selection method.
Troubleshooting
The sort changed names but not the details. Undo the sort immediately if possible, then select the full set of columns and run the sort again. In the Sort dialog, use the helper heading as the sort field and choose Expand the selection if Excel asks. If the workbook has been saved and closed, restore the backup or use the original-order field if one is available.
The header moved into the list. Open Data > Sort and check My data has headers. Verify that the header row was part of the selected range. If the heading was already sorted among the names, undo the sort and repeat with the correct range and header setting.
Some names did not move. Check whether the selected range stopped before the last name, or whether Excel stopped at a blank row. Select the complete range explicitly, including all relevant rows and columns, then sort again. Also verify that every name row has a random key beside it.
The helper values all look the same or are blank. Click a few helper cells and inspect the formula bar. Each populated row should contain a RAND formula. If you see a fixed value, a blank, or the same copied number in every cell, refill the formula from the first helper cell down the full list. The displayed decimals may look similar, but they should not all be exact copies.
The list changes after you thought it was finished. Recalculation may have changed the RAND results, especially if you run another calculation or edit a formula. The rows normally stay in their current position until a sort is applied, but a later sort can use the changed keys. To keep the current arrangement, clear the helper column after sorting or replace the formulas with values using Home > Paste > Paste Values.
The Sort dialog does not show Random Key. Make sure the selected range includes the helper heading and that the header option is set correctly. If the helper column is outside the selected range, cancel the dialog, select a wider range, and open Data > Sort again. If the field is part of a table, check that the helper heading is spelled as expected and is in the table header row.
Frequently Asked Questions
Can I randomize names without changing the original list?
Yes. Copy the names and any related columns to a new worksheet or unused area, add the helper column to that copy, and sort the copied range. The original list remains in its original order.
Will every name appear exactly once?
Each nonblank row in the selected range stays in the shuffled list, so each entry appears once. If a name was entered twice in the source, it will still appear twice; shuffling does not deduplicate the list.
How do I get a different order?
Recalculate the RAND formulas, then sort the complete range again by Random Key. Simply recalculating changes the keys but does not itself reorder the rows.
Can I shuffle names and keep their scores or email addresses attached?
Yes. Include the name, helper key, and every related column in the same selected range when sorting. Excel then moves each complete row together.
Can I use this in Excel for the web or on a Mac?
The RAND helper and sorting approach is available in Excel for the web and Excel for Mac, although dialog layouts and ribbon wording can vary slightly. Use the Data tab’s sort controls, select the full range, and sort by the helper column. For a large or important list, verify that the header and all associated columns are included before confirming.
Does RAND create a truly secure random draw?
No. It is intended for ordinary spreadsheet shuffling, not security or audited selection. For a consequential draw, use a process designed to be independently checked and appropriate to the rules of that draw.
Related
- How to Open a SharePoint Excel File in the Desktop App
- Open Excel Online in the Desktop App
- How to Open OneDrive Excel Files in the Desktop App
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Function in Snowflake: Definition, Use & ExamplesIn Snowflake, a function is a reusable piece of SQL logic that takes input parameters, performs a defined…
- Excel Formula for Weekday: Get Day of WeekThe core Excel formula for a weekday is =WEEKDAY(date), which returns a number from 1 (Sunday) to 7…
- Gtt per Min Formula: Examples and Safe CalculationThe gtt per min formula is gtt/min = (mL × drop factor in gtt/mL) ÷ time in minutes.…
- Excel Partial Match Lookup: 4 Formulas That WorkThe quickest way to do a partial match lookup in Excel is to use XLOOKUP with wildcards: =XLOOKUP("*"&D2&"*",A2:A10,B2:B10,,2).…
- Excel Filter Based on Multiple Criteria: How It WorksTo filter Excel data by multiple criteria, use Data > Filter and set a condition in each relevant…
- DATEDIF in Excel: Calculate Days Between Dates CorrectlyTo get the number of days between a start date in A2 and an end date in B2,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.