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:

How to Sum Top 5 Values - Data

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
Deadpool 2/12/16 $363,070,709
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
Suicide Squad 8/5/16 $325,100,054
Rogue One: A Star Wars Story 12/16/16 $532,177,324
Sing 12/21/16 $270,329,045

Does this article help you? If so, please consider supporting me with a coffee ☕️

Buy Me a Coffee at ko-fi.com

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.

Randomly Sort a List - Add a Column

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.

Randomly Sort a List - Use Rand Function

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.

Randomly Sort a List - Keep Shuffling

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.

Randomly Sort a List - Keep 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!).

Buy Me a Coffee at ko-fi.com

Written by Joseph who loves teaching about Excel.

© 2021, Spreadsheets Made Easy