SubscribeShuffle a list of numbers / items in excel spreadsheet
Posted on July 28th, 2008 in Learn Excel - 13 comments
Here is a quick Monday tip for data junkies.
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:
- 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
|
|
Posts & Navigation
Tags: how to, learn, Learn Excel, random, sorting, spreadsheet, tips |
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
Awesome! I’m glad someone else recognises the need to un-sort lists.
@Sam.. thanks
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).
@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.
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
@Mini.. that is an excellent way to use the shuffle logic..
Thanks a lot !!!!! very useful for me…… thanks !!!!
excellent…thanks!
Thanks very much. I am preparing vocab for Gre and I need to shuffle these words. Thanks again.
I am a teacher and used this to help my students study spelling words. Thank you so much – you saved me hours of shuffling!
@Nikky, Hasanain, Laeeq and Ashlee.. you are welcome
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
how can I create bingo cards in excel for a large group of people