How can I randomize lots of rows in Excel?
```
For example I have an excel sheet with data in 3 rows.
1 A dataA
2 B dataB
3 C dataC
I want to randomize the row order. For example
2 B dataB
1 A dataA
3 C dataC
```
I could make a new column and fill it with random numbers using =RAND() and sort based on that column.
But is this the best way to do it? The RAND equation will provide up to [a million random numbers](http://support.microsoft.com/kb/86523) and I have a quarter of a million rows so it seems like it would work.
Thanks
I searched for a bit and while [this answer about randomizing columns](https://stackoverflow.com/questions/10651553/randomize-columns) is close it seems like way overkill.
Comment randomize Excel rows
Re: Comment randomize Excel rows
Perhaps the whole column full of random numbers is not the best way to do it, but it seems like probably the most practical as @mariusnn mentioned.
On that note, this stomped me for a while with Office 2010, and while generally answers like [the one in lifehacker](http://www.lifehacker.com.au/2012/03/how-to-sort-a-list-randomly-in-excel/) work,I just wanted to share an extra step required for the numbers to be unique:
- Create a new column next to the list that you're going to randomize
- Type in `=rand()` in the first cell of the new column - this will generate a random number between 0 and 1
-
Fill the column with that formula. The easiest way to do this may be to:
- go down along the new column up until the last cell that you want to randomize
- hold down Shift and click on the last cell
- press Ctrl+D
-
Now you should have a column of identical numbers, even though they are all generated randomly.
The trick here is to recalculate them! Go to the *Formulas* tab and then click on *Calculate Now* (or press F9).
Now all the numbers in the column will be actually generated randomly.
-
Go to the *Home* tab and click on *Sort & Filter*. Choose whichever order you want (*Smallest to Largest* or *Largest to Smallest*) - whichever one will give you a random order with respect to the original order. Then click OK when the *Sort Warning* prompts you to *Expand the selection*.
-
Your list should be randomized now! You can get rid of the column of random numbers if you want.
On that note, this stomped me for a while with Office 2010, and while generally answers like [the one in lifehacker](http://www.lifehacker.com.au/2012/03/how-to-sort-a-list-randomly-in-excel/) work,I just wanted to share an extra step required for the numbers to be unique:
- Create a new column next to the list that you're going to randomize
- Type in `=rand()` in the first cell of the new column - this will generate a random number between 0 and 1
-
Fill the column with that formula. The easiest way to do this may be to:
- go down along the new column up until the last cell that you want to randomize
- hold down Shift and click on the last cell
- press Ctrl+D
-
Now you should have a column of identical numbers, even though they are all generated randomly.
The trick here is to recalculate them! Go to the *Formulas* tab and then click on *Calculate Now* (or press F9).
Now all the numbers in the column will be actually generated randomly.
-
Go to the *Home* tab and click on *Sort & Filter*. Choose whichever order you want (*Smallest to Largest* or *Largest to Smallest*) - whichever one will give you a random order with respect to the original order. Then click OK when the *Sort Warning* prompts you to *Expand the selection*.
-
Your list should be randomized now! You can get rid of the column of random numbers if you want.