How to Randomly Sort a List in Excel

August 15, 2017

Have you ever needed to take a list and randomly sort it? How would you do it? It’s not as intuitive as you think. We’re so used to sorting lists in alphabetical order or from smallest to largest, but this idea of randomly sorting a list is not very common in Excel. Let’s jump right into it and show you how to randomly sort a list in Excel.

Sample Data

Say we have some data that we’d like to sort:

Normally, we could sort by Movie title, the date that it opened (as it’s shown in the picture), or by the total amount it grossed. But what if we wanted to “shuffle” this list? How could we go about doing that?

By the way, this is the same data that we used in the Sum the Top 5 Values post.

You can also copy and paste this table to follow along:

Movie Date Opened Total Gross
Zootopia 3/4/16 \$341,268,248
Batman v Superman: Dawn of Justice 3/25/16 \$330,360,194
The Jungle Book (2016) 4/15/16 \$364,001,123
Captain America: Civil War 5/6/16 \$408,084,349
Finding Dory 6/17/16 \$486,295,561
The Secret Life of Pets 7/8/16 \$368,384,330
Rogue One: A Star Wars Story 12/16/16 \$532,177,324
Sing 12/21/16 \$270,329,045

Randomly Sort a List in Excel

To sort a list randomly in Excel, first you need to add a new column to your data. When using Excel Tables, you can simply type in a new column name at the next available table header and it will be automatically joined to your table.

Here, we type in “Sort Order” and press Enter.

Next, we need a way to randomly sort the list. We can use the `RAND()` function in Excel to help us with that. The `RAND()` function will return a number at random between 0 and 1.

After we add the formula, we can sort by that column. This will randomize the list for us.

Also, each time the `RAND()` function is calculated, you get a different number. This is why you see the table has all different numbers.

Keep Shuffling

Another cool tip is that if you’re not happy with the initial shuffling of the list, you can simply keep sorting between Ascending and Descending to keep shuffling the list. This works because when you sort the list, it recalculates the `RAND()` function, giving a new number each time.

Keeping the List Order

But what if you don’t want the `RAND()` function to keep recalculating? What if you want to keep the sort order?

If you want to keep the sort order, you can simply save the values from `RAND()` before sorting again.

Copy the list and do a Paste Special -> Values in order to keep the sort order.

Wow, you read the whole article! You know, people who make it this far are true learners. And clearly, you value learning. Would you like to learn more about Excel? Please consider supporting me by buying me a coffee (it takes a lot of coffee to write these articles!).

Written by Joseph who loves teaching about Excel.