how to randomize a list in excel

Created on 18 August, 2023 • online tool • 454 views • 2 minutes read

Randomizing a list in Excel involves changing the order of the items in a list so that they are arranged in a random sequence. This can be useful for various purposes, such as creating randomized test samples, conducting experiments, or generating random orders for presentations.


How to Randomize a List in Excel?


Here are five different ways to randomize a list in Excel:


1. Using Excel's RAND Function: 


The RAND function generates a random decimal number between 0 and 1. You can create a new column next to your list and use the RAND function to generate random numbers for each item in your list. Then, you can sort the list based on these random numbers.


Steps:


  1. Insert a new column next to your list.
  2. In the first cell of the new column, enter the formula =RAND().
  3. Fill down the formula for all the items in your list.
  4. Select both the original list and the column with random numbers.
  5. Go to the "Data" tab, click "Sort," and sort the list based on the column with random numbers.


2. Using the RANDBETWEEN Function: 


The RANDBETWEEN function generates a random whole number within a specified range. You can use this function to create a new column with random numbers and then sort the list based on those numbers.


Steps:


  1. Insert a new column next to your list.
  2. In the first cell of the new column, enter the formula =RANDBETWEEN(1, 10000) (adjust the range as needed).
  3. Fill down the formula for all the items in your list.
  4. Select both the original list and the column with random numbers.
  5. Go to the "Data" tab, click "Sort," and sort the list based on the column with random numbers.


3. Using VBA (Visual Basic for Applications): 


If you're comfortable with using Excel's VBA programming, you can create a custom macro to randomize the list.


Steps:


  1. Press Alt + F11 to open the Visual Basic for Applications editor.
  2. Insert a new module and enter the VBA code to randomize your list. Here's a sample code snippet:


 Sub RandomizeList()

   Dim LastRow As Long

   Dim i As Long, j As Long

   Dim Temp As Variant

   

   LastRow = Cells(Rows.Count, 1).End(xlUp).Row

   

   For i = LastRow To 2 Step -1

     j = Int(Rnd() * (i - 1)) + 2

     Temp = Cells(i, 1).Value

     Cells(i, 1).Value = Cells(j, 1).Value

     Cells(j, 1).Value = Temp

   Next i

End Sub



Run the macro by pressing F5.


4. Using Excel's Power Query (Get & Transform): 


Power Query is a powerful tool in Excel for data transformation. You can use it to randomize a list as well.


Steps:


  1. Select your list.
  2. Go to the "Data" tab and click "Get Data" > "From Table/Range."
  3. In the Power Query Editor, select your list column.
  4. Go to the "Transform" tab and click "Sort Ascending."
  5. Close and load the data back into Excel.


5. Using Online Randomization Tools: 


If you're looking for a quick solution without using Excel functions or programming, you can use online randomization tools. There are various websites that can help you shuffle or randomize lists. Simply copy your list, paste it into one of these tools, and let it randomize the order for you.

Categories

Popular posts