# Shuffle a list of numbers / items in excel spreadsheet

Posted on July 28th, 2008 in

Learn Excel - 36 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

### Share this tip with your friends

### Leave a Reply

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

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

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

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

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

@Peter.. awesome…

Awesome!!! I so needed this!

@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

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

I need a same soluation which Ashok said, any clues for it

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

Thank you!

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

Cheers

Brilliantly simple

Well thought out

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!

i want make random 100 number in excel with

int(100*rand()) by i need number is sort

please help me

thanks

Genial! Thank you

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

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.

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

[…] Shuffle (or randomly arrange) this list […]

Really fast and useful tip. Thank-you!

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.

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

Thanks a lot

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