Productivity

How To Sort A List Randomly In Excel

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.

Have you subscribed to Lifehacker Australia's email newsletter? You can also follow us on Facebook, Twitter and YouTube.