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 |
|---|---|---|
| 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 ☕️
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.
