Shuffle a list of numbers / items in excel spreadsheet

Posted on July 28th, 2008 in Learn Excel - 33 comments

Here is a quick Monday tip for data junkies.

shuffle a list of numbers / items in random order in excel

If you want to shuffle a list of numbers or cells in excel in random order you can do that with “data > sort” menu option of excel.

  • First insert a column next to the list you want to shuffle and enter random numbers in each cell in using =rand(). See below: shuffle-cells-using-random-excel-function
  • Now select the entire list and sort it by the new column with random numbers. (Menu > data > sort)
  • If you want a fresh set of shuffled list, just hit f9 (recalculates all formulas) and sort again

Like this? Also learn how to sort horizontally, fill only weekdays while auto-filling dates

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

33 Responses to “Shuffle a list of numbers / items in excel spreadsheet”

  1. Sam Krysiak says:

    Awesome! I’m glad someone else recognises the need to un-sort lists.

  2. tony says:

    Another thing I do is add a third column with the numbers 1,2,3… and always sort with that included. That way I can return to the original order if I need to. (although in this case it is alphabetical).

  3. Chandoo says:

    @Tony: thanks, adding another column with original order helps, especially you are simulating random behavior and would like to get back to original order.

    some of the uses for randomizing lists that I can think of:
    - simulating games, card shuffles
    - simulating gains over a very long period (10-20 years) where in you need to take a random set of market gains to see how the investments would grow.

  4. Mimi Moore Debski says:

    Thank you so much. I am technically challenged, but you just gave me the power to help my 9-year old practice her written multiplication challenges at home. I want to give her pages with the tables in different orders, so she won’t memorize the answers. Thanks, again. Mimi

  5. Chandoo says:

    @Mini.. that is an excellent way to use the shuffle logic..

  6. Nikky says:

    Thanks a lot !!!!! very useful for me…… thanks !!!!

  7. hasnain says:

    excellent…thanks!

  8. Laeeq Ahmed says:

    Thanks very much. I am preparing vocab for Gre and I need to shuffle these words. Thanks again.

  9. Ashlee says:

    I am a teacher and used this to help my students study spelling words. Thank you so much – you saved me hours of shuffling!

  10. Chandoo says:

    @Nikky, Hasanain, Laeeq and Ashlee.. you are welcome :)

  11. shareta says:

    I am trying to create about 40 bingo cards using words in excel is there anyway to create one card then shuffle or sort it to create the other cards

  12. tonya says:

    how can I create bingo cards in excel for a large group of people

  13. good advice says:

    pretty good advice :) one of the simplest things, Random .. can be so mind boggling.. specially when a computer does it.

  14. Shwetha says:

    Sir, can you please write an application program (downloadable) that shuffles the list at the button click instead of we applying the rand() function and sorting it everytime? It will be very helpful. thanks :)

  15. Peter says:

    Great tip – just used it to allocate shirt numbers to the squad for a rugby tour.

  16. Chandoo says:

    @Peter.. awesome… :)

  17. Maria says:

    Awesome!!! I so needed this!

  18. Hui... says:

    @Shwetha
    Add a column of Rand() numbers =rand()
    Add a column which Ranks the numbers =rank(Number, list,1)
    Add a column which looks up the Ranks and extracts the appropriate data from original colums, use Index/Match or Offset/Match
    Press F9 to re-sort

    Have a look at an example at: http://rapidshare.com/files/427379371/Sorter.xls

    • ashok says:

      I WANT TO SHUFFLE THINGS BASED ON SOME CRITERIA LIKE HERE 8 PERSONS A ,B, C,D ,E .F G,H & 8SKILLS ARE 1A,2A 3A,4A, 5A,6A,7A,8A HERE A,B ,G & H KNOWS ALL SKILLS, C & D DONT KNOW 3A, 5A, E & F DONT KNOW 2A & 6A THEN HOW SHUFFLE PERSONS BASED ON THEIR SKILLS without repeatation of same skill again…….THANK YOU….PLEASE HELP ME

  19. Emily says:

    BRILLIANT! Thank you! You just saved me TONS of work!

  20. student says:

    Thank you!

    Very useful for decision tree creating in a university course called “Data Mining”!

    Cheers :)

  21. Alf says:

    Brilliantly simple

    Well thought out 

  22. Gina says:

    Hi, I am a bit technically challenged but I got most of this. However…where did the list of numbers come from? Do you just make them up? Do they have to be in the format 0.123456? Does the =RAND() just go in the first cell of the new column?
    I want to use this to randomise a list of tweets that I will put out over a period of time. The rest I understand and it looks perfect. Thanks! 

  23. saman says:

    i want make  random 100 number in excel with
    int(100*rand()) by i need number is sort
    please help me
    thanks

  24. says:

    Genial! Thank you

  25. Saiful says:

    Thank dude, I am was trying to create a semantically unpredictable sentences in a fixed syntactical structure. This definitely saved me hours..

  26. Davis says:

    The first time I saw this was at a tax certificate sale. Each property was shuffled by the numbers given to the people in the room.

    If your number matched the property you got the certificate. Everyones assigned number shuffled on each property.

    the best anyone could have done was to have their assigned number hit on every property.

    there were more people in the room than properties.

  27. […] case you have the list in Excel, then you can directly randomize the list in excel (check details here). However, in case the items are not in Excel, then it would become very tricky to randomize them. […]

  28. Kin says:

    Really fast and useful tip. Thank-you!

  29. Pranav says:

    Good one!

    One thing that I noticed though,
    at least in Excel 2007, eevrytime you click/enter anything the random numbers change. So even when you sort the data the numbers change.
    Thus just to be able to backtrack. I would make another column where I would copy the random numbers and paste only the values. Then I can delete the random number formula column and sort using random number values column.

    Thanks.

Leave a Reply