Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

How can I randomise the order of a column of data in excel?

For example, if I have a column of data of 10 rows

0
1
2
3
4
5
6
7
8
9

What functional could randomly re-order them to something like

5
2
8
1
7
9
4
0
3
6

Thanks!

share|improve this question

3 Answers 3

up vote 6 down vote accepted

I believe the simplest way to do this is to have another column next to the data column. I'll call this the "rand column". Enter =RAND() in the first cell of the rand column and autofill down.

Now do a custom sort on both columns, using the rand column in the "Sort By" combo box. Now the data column will be sorted by the values in the rand column, which are random, and so your data column will itself be ordered randomly.

You could then hide the rand column. Hitting F9 would generate a new column of values for the random column, but you would have to redo the custom sort to re-randomize the data column.

If you are looking for something more tidy where you don't need a column beside the data column, you should probably write a macro that takes the current selection and randomizes it.

Barring a macro, another way to do it would be to use array formulas and the Index function. This would be more complicated, but would allow you to hit F9 to reorder the data column.

share|improve this answer

Add a new column (and hide it if necessary) and use the command =RAND(), then sort on this column.

Do you need a reproducible ordering, or is a newly random order each time sufficient?

share|improve this answer
    
newly random order each time is ok, but how would I use the rand command? –  akashic Mar 9 '10 at 15:28
    
Having created the new column, on each row put =RAND() in the new cell and it will put a random number (between 0 and 1) in that cell. This will be a new random number each time, so if you sort on it, you'll get a new random order each time. –  Unsliced Mar 9 '10 at 15:47

Have a look at this post.

It shows you how to do this and also has a video to guide you.

share|improve this answer
2  
Welcome to Super User! Whilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference. –  slhck Dec 14 '13 at 11:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.