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.

Comments

    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......

    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?

      I'm not sure I understand what you're saying correctly, so this might not even help. But 38x38=2584 unique numbers (assuming no integers) If you are trying to spread the numbers 1-1444, on a grid that is bigger (i.e. 2584 different cells) the numbers in each cell wont be unique...

    Thanks man...helpful post; there should be an easier way, but this worked for me!

    This is awesome! thanks!

    This is great. How do I randomize two columns to get random 2-word combos, one from Column A and one from Column B (always in that order)?

    EXCELlent!! Luv that back door - it's like getting into the club for free.

    You just saved me.

    Thanks for this info... but I can't find the "Sort & Filter’" button on the home tab.

    I used to Randomize by clicking on data and then choosing “sort by” random in the dropdown menu.. but now when I hit data - I get the error message "Missing Column/Row Name"

    Any advice?

    BTW - I'm on a MAC and using Excel 2011 -- is that why I don't see it?

    thank you so much - worked first time like a charm

    How do i now assign a 5 color team to the random list?

    Thank you so much!

    Nice Work! Great too that closing and opening XLS generates a new random number!

    Iam trying to put names in one row and machines on the other 4 rows. I dont want the names to randomly generate only the machines. how can i do this?

Join the discussion!

Trending Stories Right Now