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

User · Answer

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

User · Answer

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

User · Answer

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    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  amp  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

User · Answer

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  lt  gt  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    Open the VBA editor  Alt   F11   Right-click on  ThisWorkbook  under your currently open spreadsheet  listed in parentheses after  VBAProject   and select Insert   Module   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

[excel] How to randomize Excel rows

Examples related to excel

Examples related to random