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.
.
Leave a Reply
You must be logged in to post a comment.