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.
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|
|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|
|The Secret Life of Pets||7/8/16||$368,384,330|
|Rogue One: A Star Wars Story||12/16/16||$532,177,324|
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
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.
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
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
before sorting again.
Copy the list and do a Paste Special -> Values in order to keep the sort order.