Skip to main content

Kutools for Office — One Suite. Five Tools. Get More Done.

Randomize a list in Excel (Easy step-by-step tutorial)

Author Amanda Li Last modified

Performing random sort is a quick and easy way to ensure that you end up with all cases turning up with equal probability. In this tutorial, we will talk about how to easily randomize, or shuffle a list in Excel in a few steps.

A screenshot illustrating the randomization result in Excel

Video: Randomize a list in Excel

Randomize a list with functions

In this section, I will teach you how to shuffle a list with built-in Excel functions.

Randomize a list with RAND function

Users of any Excel version can randomly sort their original dataset by using the RAND function with the help of the Sort feature, please follow the steps below.

Step 1: Enter the RAND formula

Select the cell next to the top cell of the list you will randomize, input the below RAND formula, and press "Enter".

=RAND()

RAND formula in a cell

Step 2: Fill the RAND formula to other cells

Double-click on the fill handle (the small green square in the lower-right corner) of the formula cell to apply the formula to below cells.

RAND formula applied to other cells

Step 3: Sort the RAND results to shuffle the list

1. Select "B2:B8", the list that contains the RAND formula.

2. Click on "Sort & Filter" > "Sort Smallest to Largest" in the "Editing" group on the "Home" tab.

Sort Smallest to Largest option on the ribbon

3. In the pop-up dialog box, select "Expand the selection", and then click on "Sort".

Selecting the 'Expand the selection' option

Result

Now, you are all set! The list is randomized as shown below.

The list is randomized

Note: The Excel RAND function is volatile: It recalculates its result on every worksheet change. So, the numbers in column B changed instantly after sorting. If you want to shuffle the list again, repeat the above step 3. Otherwise, you could simply delete the column with the RAND function.
Randomize a list with RANDARRAY, SORTBY and ROWS functions (Excel 365/2021)

If you are users of newer Excel versions, for example, Excel for Microsoft 365 or Excel 2021, or Excel for the Web, the RANDARRAY, SORTBY and ROWS formula will help you quickly get a list shuffled at the position you specified.

Step 1: Enter the RANDARRAY, SORTBY and ROWS formula

Select a blank cell where you want to get the randomized result, type in the below formula (Note that you should change "A2:A8" in below formula to the original list you want to randomize) and press "Enter".

=SORTBY(A2:A8,RANDARRAY(ROWS(A2:A8)))

Note:
  • Excel returns a "#SPILL!" error when the spill range (B2:B8 in this case) is not blank.
  • The formula is volatile: It recalculates its result on every worksheet change. If you want to shuffle the list again, press "F9".
  • The formula result (randomized list) is not editable. If you need to edit the randomized list, follow the next step.

(Optional) Step 2: Copy and paste the formula result to make it editable

If you only want to see a random result, you can skip this step. If you want to edit the randomly shuffled list, you should copy the formula result and paste values only:

1. Select the formula result and press "Ctrl" + "C" to copy it.

2. Right-click on a cell where you will paste the copied result. (You can also paste the value to its original location.)

3. Select the "Paste Values Only" option from right-clicking menu.

Result

Now you will get a shuffled and editable list as shown below.

Randomize a list in Excel with Kutools in 2 clicks

If you are tired of using formulas and intend to use the help of an add-in, Kutools for Excel’s Sort/Select Range Randomly feature can help you perform a random sort much easier with more options. Please do as follows:

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

First, select the list you will shuffle. And then select "Kutools" > "Range" > "Sort/Select Range Randomly". In the pop-up "Sort/Select Range Randomly" dialog, select "Entire rows", and then click on "Ok".

Select the Entire rows option in the dialog to shuffle the list

Note:

Randomize a list with VBA

If you prefer to randomize a list with VBA method, please do as follows.

Note: You cannot undo after running a VBA macro. Therefore, make a copy of the list somewhere else in case you need the original data in the future.

Step 1: Select the list you will shuffle

The list to be shuffled is selected

Step 2: Copy VBA code to Module window

1. Press "Ctrl" + "F11" to open the VBA editor, and then click "Insert" > "Module" to open a Module code window.

Click Insert > Module in the VBA editor

2. Copy and paste the VBA code below to the opened Module window.

Sub RandomSort()
'Update by ExtendOffice
Dim xRg As Range
Dim xNum, xF, xI As Integer
Dim xWSh, xAWSh As Worksheet
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xAWSh = Application.ActiveSheet
Set xRg = ActiveWindow.RangeSelection
Set xWSh = Worksheets.Add
xNum = xRg.Count
For xF = xNum To 1 Step -1
    xI = WorksheetFunction.RandBetween(1, xF)
    xWSh.Range("A1").Value = xRg.Item(xI)
    xRg.Item(xI) = xRg.Item(xF)
    xRg.Item(xF) = xWSh.Range("A1")
Next
xWSh.Delete
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Step 3: Run the VBA code

In the code window, press "F5" or click the Run button button to run this code.

The run button in the code window

Result

The list is shuffled

Note: If you want to randomize the list again, repeat the above step 3.