# Randomise the order of a column of data in Excel

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!

-

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.

-

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?

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

-
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