How to randomize Excel rows

Question

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 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 is close it seems like way overkill.

Answer 1

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 work,I just wanted to share an extra step required for the numbers to be unique:

1. Create a new column next to the list that you're going to randomize
2. Type in =rand() in the first cell of the new column - this will generate a random number between 0 and 1

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

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

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

6. Your list should be randomized now! You can get rid of the column of random numbers if you want.

Answer 2

I usually do as you describe:
Add a separate column with a random value (=RAND()) and then perform a sort on that column.

Might be more complex and prettyer ways (using macros etc), but this is fast enough and simple enough for me.