Have a list of names in a spreadsheet and want to sort them into a random order? There’s no native function to do that in Excel, but you can achieve it with a little random number generation. Here’s how I do it.
Sorting names randomly can be useful (for instance if you’re assigning tasks or picking a competition winner). This probably isn’t the most sophisticated way to do it, but it works. Assuming your list of names is already in Excel, follow these steps.
- Insert a new column on the list next to your list of names. (Click the Insert drop-down on the Home tab and choose ‘Insert Sheet Column’.)
- In the first cell in that column, enter the formula
=RAND(). (This generates a random number between 0 and 1.) - Fill that formula to the end of your list by holding Shift, pressing the down key until you’ve selected to the row where your list ends, and typing Control-D to auto-fill. This will place a different random number in each cell next to the relevant names.
- Select the column filled with random numbers by clicking on the initial row letter at the top.
- Click on the ‘Sort & Filter’ button on the Home tab and choose ‘Sort Smallest To Largest’. Excel will offer to expand the selection (so you’ll actually sort the names as well). Click OK.
- Select the column again by clicking on the initial row letter and then click Delete. This gets rid of the random numbers, as you no longer need them.
.
Each time you hit sort the numbers change.. so if you're not happy with one randomisation then keep hitting sort before deleting the column
And if you do that lots and lots of times, you might be able to get the list back into alphabetical order!
This is great. I teach Excel and sorting is one of the first things I teach and I like the idea of the student being able to practice this over and over easily. Thanks Angus!
One thing you can do to stop the random numbers changing every time you do something - once generated, copy the row and "paste as values" to preserve the random set.
In the third step, you can double click on the fill handle to fill down the random numbers. It is a little faster than shift down crtl-d.
This is very helpful. thanks!
Thank you, Thank you, Thank you. You are the only person on the internet that knows how to do this. It worked perfectly! You instructions were easy to understand-i'm a novice user and I just followed your prompts and wa! la! In two seconds it was right in front of me. Love you......
wa! la! ?
Voilà
THANK YOU SO MUCH! I have spent two long days trying to figure out how to do this and every other page I found had their instructions so complicated it was ridiculous. I just wanted someone to tell me, "Do this command in this cell, etc." And that is exactly what you did! Bless you!!! This is such a simple solution! You have no idea how happy you have made me! LOL
I have a question...are the random numbers generated between two given values unique...in other words, is it giving me a random number between x & y in each cell independently, which could create duplicate numbers...for example, I am trying to spread a list of the numbers 1 - 1,444 randomly onto a 38 X 38 grid in Excel...I used =randbetween and it filled out the grid, but the numbers were not unique...thoughts?