Randomize a list in Excel

Sorting is an important feature that helps to create different algorithms. Because of its incredible usage, Microsoft Excel has provided other sorting options, including ascending or descending sorting, color sorting, as well as custom sorting. However, it does not offer one important sorting feature, i.e., the random sort. While working with Excel, situations occur where you need to randomize data such as winning lottery tickets, allocation of night shift, etc.; in such cases, the random sort option comes very handy.

In this tutorial, we will cover a handful of different random sorting options using Excel inbuilt functions such as RAND(), RANDARRAY() and SORTBY () and their step-by-step implementation in Excel.

Randomize in Excel using RAND() formula

Though Randomizing a list in excel is an important feature but is no inbuilt function to perform this operation in Excel. However, we can create our own formula to generate random numbers. Here, we can take use the inbuilt RAND function to incorporate this function. The RAND() function returns an automatically generated random number in the selected cell.

Syntax

RAND()

NOTE: Every time a cell in your worksheet is recalculated, a new random number is generated.

In the following image, we have taken a list of names in column A. What if we want to randomise this list and set the order of the name automatically?

Below given are the step-by-step implementation to generate random numbers which will automatically randomise the above list:

STEP 1: Insert a helper column

Add a new column next to column A. We will apply the formula of randomization in this column. However, if your dataset consists of a single column, skip this step.

Step 2: Apply the RAND() function

Next, we will incorporate the inbuilt RAND() function in Excel. RAND() function creates a random number in your Excel worksheet.

Start your formula with = and write the RAND() function. Press enter and it will automatically generate a random number.

Step 3: Copy the formula down the cells.

Drag the formula down the column to replicate it down the cells. The fastest way to do this is by double-clicking the fill handle.

Step 4: Sort the name is an Order

Next, we will sort the helper column in ascending order which we can recently filled with random numbers. (If required you can also sort the numbers in a series of descending order. But the problem is it will move the column headers at the bottom of the table, which is not ideal).

• Select any number in the helper column
• From the Excel ribbon, click on the Home tab > Editing group and click Sort & Filter.
• From the pop-up list we will select ZA sorting option since we want to sort the numbers from largest to Smallest.

Step 5: Excel will generate the random numbers.

As a result, Excel will automatically expand your selection and it will sort the list of names specified in column A.

Refer to the following screenshot.

Points to Remember:

1. The Excel RAND() is a volatile function. Every time a cell in your worksheet is recalculated, a new random number is generated. Therefore, it's a good option if you are unhappy with the automatic random number function. Simply keep hitting the sort button until you get the optimal output.
2. Because we are using the RAND() function, therefore it will generate a random number every time you make any changes to your worksheet. To prevent random numbers from recalculating the entire sheet, simply copy the random numbers and paste them as values using the Paste Special feature.
3. Using the same formula you can randomize multiple columns. To get it done, place multiple columns together aligned side by side so that the columns are contiguous, and then perform the above steps.

Randomising using multiple functions

Excel provides multiple ways to perform a task. If you are using version Excel 365 or Excel 2021, you can incorporate the combination of RANDARRAY(), SORTBY() and ROWS() functions to randomize a list in your Excel worksheet. Below given are the step-by-step implementation to generate random numbers, which will automatically randomize the above list:

Step 1: Generate numbers using RANDARRAY

The first step is to use the RANDARRAY function. This function generates a list of random decimal numbers between 0 and 1. The array below consists of 8 rows and 1 column.

Formula: =RANDARRAY (8,1)

Note: The RANDARRAY function, entered into cell B1, fills multiple cells. Superb! This feature of automatically filling of data in Excel 365/2021 is called spilling.

Step 2: Random Numbers will be generated

As a result, Excel will generate a list of random numbers. You will have the following output.

Step 3: Sort the list with SORTBY function

Within the same formula we will introduce another function named SORTBY. The built-in SORTBY function is used sort the content of the specified range on the basis of the given array values. By default, the SORT function sorts in ascending order.

Formula Used: =SORTBY (A2:A9,B2:B9)

You will have the following output:

Step 4: Merge RANDARRAY function inside SORTBY

So far, we have applied two different formulas in different places in our worksheet. What if we merge them both in a single formula? Refer to the below formula.

Formula Used: =SORTBY(A2:A9,RANDARRAY(8,1))

As a result, the RANDARRAY function will generate a list of random numbers, and later SORTBY will sort the selected names using the corresponding array values. The above formula will randomise the list and will sort the different rows in ascending order.

It will return the following output:

If you have a longer list of, say, 20 names, change the value from 8 to 20 in the formula shown above, or even better, use the ROWS function.

Formula Used: =SORTBY (A2:A9,RANDARRAY(20,1))

Note: The ROWS function simply counts the number of rows in a range.

We covered almost everything that will help you to randomise a list in an Excel worksheet. Go ahead and implement the same function and formula to generate a list of random numbers.