Random Sort in Excel

Man holding dice that represent how to 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.

Excel table with two columns and a list of 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.

Excel table with Name and Random columns and a list of names

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:

Sort dialog box in Excel

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

Posted in , ,
Avatar photo

Excel University

We love sharing the things we've learned about Excel, and we built Excel University to help us do that. Our motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.

2 Comments

  1. David N on May 25, 2023 at 6:47 pm

    Why not show people how to use SORTBY with RANDARRAY to do the whole operation in a single formula?

Leave a Comment