How to randomize Excel rows

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.


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.

    Random numbers... that are the same...

    The trick here is to recalculate them! Go to the Formulas tab and then click on Calculate Now (or press F9).

    Actually random numbers!

    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.


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.


Here's a macro that allows you to shuffle selected cells in a column:

Option Explicit

Sub ShuffleSelectedCells()
  'Do nothing if selecting only one cell
  If Selection.Cells.Count = 1 Then Exit Sub
  'Save selected cells to array
  Dim CellData() As Variant
  CellData = Selection.Value
  'Shuffle the array
  ShuffleArrayInPlace CellData
  'Output array to spreadsheet
  Selection.Value = CellData
End Sub

Sub ShuffleArrayInPlace(InArray() As Variant)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ShuffleArrayInPlace
' This shuffles InArray to random order, randomized in place.
' Source: http://www.cpearson.com/excel/ShuffleArray.aspx
' Modified by Tom Doan to work with Selection.Value two-dimensional arrays.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  Dim J As Long, _
    N As Long, _
    Temp As Variant
  'Randomize
  For N = LBound(InArray) To UBound(InArray)
    J = CLng(((UBound(InArray) - N) * Rnd) + N)
    If J <> N Then
      Temp = InArray(N, 1)
      InArray(N, 1) = InArray(J, 1)
      InArray(J, 1) = Temp
    End If
  Next N
End Sub

You can read the comments to see what the macro is doing. Here's how to install the macro:

  1. Open the VBA editor (Alt + F11).
  2. Right-click on "ThisWorkbook" under your currently open spreadsheet (listed in parentheses after "VBAProject") and select Insert / Module.
  3. Paste the code above and save the spreadsheet.

Now you can assign the "ShuffleSelectedCells" macro to an icon or hotkey to quickly randomize your selected rows (keep in mind that you can only select one column of rows).