[excel] 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.

This question is related to excel random

The answer is


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.


Use Excel Online (Google Sheets).. And install Power Tools for Google Sheets.. Then in Google Sheets go to Addons tab and start Power Tools. Then choose Randomize from Power Tools menu. Select Shuffle. Then select choices of your test in excel sheet. Then select Cells in each row and click Shuffle from Power Tools menu. This will shuffle each row's selected cells independently from one another.


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