Randomise the order of a column of data in Excel

Question

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!

Answer 1

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.

Answer 2

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?