An oldie but a goodie, this little titbit was much appreciated when it was first posted back in 2012, so I’ve dug it out of the archives to share with a new generation of Excel wizards. While Excel (still) has no way to natively sort a list randomly, it’s a pretty easy process with a few short steps.
From Angus’s original story, the way to do this is with a bit of random number generation:
- 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.
It’s true that there’s no such thing as true random in the world of computing, so if you’re unhappy with your first random sorting, then you can hit sort again before you delete the numbers for a totally different order — and so on and so on until you’re happy with the list you’ve got. (Though at that point it almost ruins the point of sorting it randomly in the first place!)
Lifehacker’s Classic Hacks is a regular segment where we dig up the most popular, useful and offbeat advice from our archives and update it for your modern lifestyle.
Comments