How to Randomize a List in Excel
Randomizing a list in Excel is useful for various purposes, such as creating randomized samples, shuffling quiz questions, or simply generating a random order for a list of items. Here’s how to randomize a list in Excel using two common methods:
Method 1: Using the RAND Function
The RAND function generates a random number between 0 and 1. You can use this function to assign a random number to each item in your list and then sort the list based on these numbers.
- Next to your list, create a new column. This will be used to hold the random numbers.
- Click on the first cell in your helper column next to the first item of your list. Enter =RAND() and press Enter. Excel will generate a random number in this cell.
- Drag the fill handle down to fill the cells next to your list with random numbers.
- Select both your list and the random numbers. Go to the Data tab and click Sort. Choose to sort by your helper column containing the random numbers. Make sure to select either Smallest to Largest or Largest to Smallest – it doesn’t matter which, as the numbers are random.
- Once your list is randomized, you can delete the helper column if you no longer need it. Your list will remain in the new randomized order.
Method 2: Using the SORTBY Function
SORTBY function allows you to sort a range or array based on the values in another range or array, including those generated by an array formula.
Suppose your list is in column A (A2:A10). In an adjacent column (for example, B2), you can enter the formula =SORTBY(A2:A10, RANDARRAY(COUNTA(A2:A10))).
Excel will display the randomized list in column B. The RANDARRAY function generates an array of random numbers equal in size to your list, and the SORTBY function sorts your list based on those random numbers.
The RAND function recalculates every time the worksheet is opened or changed, which means the numbers will change unless you paste the randomized list as values (Right-click > Paste Special > Values) to keep it static.
marrybb
While the RAND function is pretty straightforward, it can be inefficient for larger datasets due to constant recalculations. The SORTBY method with RANDARRAY is a robust alternative. Good job highlighting both!