Randomize a list in Excel (Easy step-by-step tutorial)
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 few steps.
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 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()
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.
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.
3. In the pop-up Sort Warning dialog, select Expand the selection, and then click on Sort.
Result
Now, you are all set! The list is randomized as shown below.
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)))
- #SPILL errors are returned when the spill range (B2:B8 in this case) for the formula isn't 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 so as follows:
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.
- To try out the Sort/Select Range Randomly feature, you should have Kutools for Excel installed on your computer. If you do not have Kutools installed, click here to download and install. The professional Excel add-in offers a 30-day free trial with no limitations.
- To undo the action, press Ctrl + Z.
- To shuffle your data in other ways, take a look at this tutorial: Quickly sort or select cells, rows and columns randomly in Excel.
Randomize a list with VBA
If you prefer to randomize a list with VBA method, please do as follows.
Step 1: Select the list you will shuffle
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.
2. Copy and paste the VBA code below to the opened Module window.
VBA code: Random sort a list
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 SubIn the code window, press F5 or click the 
button to run this code.
Result
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
