Random Sort in Excel

Despite the many ways you can shuffle rows, columns, and cells, there’s no built-in option to random sort in Excel. That can be useful for randomly assigning tasks or responsibilities, selecting samples, and much more.

Instead, you can write a formula that allows you to randomly sort any data you select.

Video

How to Random Sort in Excel Using the RAND Function

Excel’s RAND function is used to generate a random number between zero and one, but we can also use it to shuffle other values.

Let’s say you have a list of names like the one below. You’ll start by creating a Random column to the right of the names.

Now that you have a new column for your random numbers, it’s time to use the RAND function to generate those numbers!

To use the RAND function, simply enter =RAND() into the first cell of your new Random column. This will generate a random number between 0 and 1 for that cell.

Once you’ve generated your first random number, you can easily fill the rest of your Random column with additional random numbers.

Click on the cell containing your first random number (C3), and drag the Excel fill handle (the small square in the bottom right corner of the cell) down to fill the rest of the column with random numbers.

Now you have a column full of random numbers that can be used to sort your data.

Convert Formulas to Static Values

The Excel RAND function will generate new random numbers each time the worksheet is updated.

To keep the values from recalculating all the time, copy the cells and then paste them as values. To do this, Copy the range and then Paste Values.

By doing this, the generated random numbers will be converted into static values that won’t change – even if the worksheet does! This means the list will remain randomized in the desired order and will not be affected by subsequent recalculations.

Sort by Random Values

To sort by the random values, select your Random column, select Sort & Filter on the Home tab, and choose to sort your data in ascending order.

Note that the Sort Warning box may pop up asking if you want to expand the selection. Be sure to click Expand the selection so all the data is sorted.

You can also select the Sort option from the Data ribbon tab, but you may get an additional dialog box that looks like this:

If so, just be sure you check the box that says My list has headers and that Random is the selected column.

Do you know any other easy ways to random sort in Excel? Let us know in the comments!

Sample file

RandomSortInExcel.xls