fbpx
Search
Close this search box.

Shuffle a list of numbers / items in excel spreadsheet

Share

Facebook
Twitter
LinkedIn

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

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

50 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. Chandoo says:

    @Sam.. thanks 🙂

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

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

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

    • AndyK says:

      Same thing here, wanting the kids to practice mult or addition table from 0 to 10...

      2 + ... = 10
      6 + ... = 10
      etc
      Using the answer from Hui, you can use the following to regenerate the first number from the list 0 to 10:
      - one (invisible) column C with formula =RAND() on each row
      - first column A with the following formula =RANK.AVG(C1,C$1:C$11,1)-1
      - copy and paste to every row below
      That way, it recalculates automatically.

      I don't need any INDEX or OFFSET because my list uses numbers 0 to 10.

      • Daniel says:

        Thanks AndyK. I did not know about the RANK.AVG function. I used this which had the same effect:

        = COUNTIF ($A$1:$A$10, ">="&A1)

        What this does is count the number of entries in A1:A10 that are greater than or equal to the value being ranked.

  6. Chandoo says:

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

  7. Nikky says:

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

  8. hasnain says:

    excellent...thanks!

  9. Laeeq Ahmed says:

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

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

  11. Chandoo says:

    @Nikky, Hasanain, Laeeq and Ashlee.. you are welcome 🙂

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

  13. tonya says:

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

  14. good advice says:

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

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

  16. Peter says:

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

  17. Chandoo says:

    @Peter.. awesome... 🙂

  18. Maria says:

    Awesome!!! I so needed this!

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

  20. Emily says:

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

  21. student says:

    Thank you!

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

    Cheers 🙂

  22. Alf says:

    Brilliantly simple

    Well thought out 

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

  24. saman says:

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

  25. says:

    Genial! Thank you

  26. Saiful says:

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

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

  28. […] 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. […]

  29. Kin says:

    Really fast and useful tip. Thank-you!

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

  31. […] Instead, I’ll use a random number generator to pick each place for me. I’ll also shuffle the list each time, so that it doesn’t remain in alphabetical order, and so that each […]

  32. GURU says:

    Thanks a lot 🙂

  33. […] is get an export of all of the pages of your site, perhaps from Open Site Explorer, put them in Excel and shuffle them. Then choose the top 10 that come up.  You can follow the Moz instructions I linked to above, do […]

  34. David says:

    Hi I'm trying to shuffle a 8 x 12 matrix, is that possible in excel?

  35. Thiago says:

    Muito obrigado!

  36. Aviv says:

    Thank you very much! really helped me a lot

  37. James says:

    I have a pool of 40 numbers I need all the possibilities of those 40 numbers in a 10 digit result without duplication. How can I get those results?

  38. radiostar says:

    Hitting F9 just generates random numbers between 0 and 1; it doesn't shuffle the words. It either doesn't work in Excel 2013 or I couldn't get it to work.

    • AndyK says:

      Correct, F9 regenerates the random numbers. But you still need to manually sort the columns afterwards.

  39. monique says:

    I have 20 columns with 9 different names in each. I would like to shuffle these names every 12mins between all 20 colmuns. Can this be done?

  40. Al says:

    Hi,
    I have a list with 20 items and I want to randomly move them many times, at least 100 times by ideally 1000 times - I am trying to do a statistical randomization. I wonder if it is possible to create a formula to do this.

  41. Deepa says:

    Thank you very much. This is exactly what I wanted!!!

  42. Francis says:

    Please I need someone to help me shuffle numbers from 1,2,3... 90. I mean help Mr shuffle numbers from 1 to 90 and each line must contain 5 numbers... Example, if I am to shuffle numbers from 1 to 5 and each line must contain 3 numbers, I would have (1,2,3), (1,2,4), (1,2,5), (1,3,4), (1,3,5) (1,4,5) (2,3,4) (2,3,5) (2,4,5) (3,4,5)

Leave a Reply