How to Randomly Select from a List in Excel (5 Useful Ways)

Random or unbiased selection from a list of names, numbers, or dates is useful for surveys, data analysis, market research, etc. Excel doesn’t have any built-in feature or direct formula to select one or multiple values from a list.
Therefore, we need to combine several functions to randomize a list.
The most effective approach to randomly choose one or more cells from a list is to combine the INDEX, RANDBETWEEN, and COUNTA functions.

In our dataset, we have columns for student IDs, names, department, and other details. Our goal is to select one or multiple IDs randomly from column A. We’ll put the randomly selected data in column F.

In the Data Analysis Toolbar, you can use the Sampling option and select one or more numeric values (such as phone numbers and IDS) randomly in a selected output range. Keep in mind that this method only works for numeric values. Here are the steps:

➤ Click on the Data tab and select Data Analysis from the Analysis group.

➤ As the Data Analysis dialog box opens, scroll down and select Sampling from the Analysis Tools options. Press Ok.

➤ Now, a new Sampling window will open. In the Input Range box, manually type or highlight the input range from where you want to select randomly.
➤ From the Sampling Method group, select Random and enter a number in the Number of Samples box to define how many random samples you want to extract.
➤ In the Output options, choose Output Range and manually type or highlight the cells to put the extracted random samples.

➤ Finally, click Ok and Excel will return the random values.

In Excel, the COUNTA function counts the number of non-blank cells in a range. While the RANDBETWEEN function assigns one random number from your specified range, the INDEX function returns one value from the range, based on that random number.
However, as you drag the formula down, it might return duplicate values. Here’s how to combine them to pick only one random value:

➤ Choose a cell where you want to put the first random value and enter any of the following formulas:

For a Fixed Number of Rows

If you know how many rows consist the list, insert the following formula:

➤ Replace the list range $A2:$A10 and number of rows according to your dataset. Click Ok.

For a List with Non-Blank Cells

Use the following formula if you don’t know the number of rows and your list doesn’t have any blank cells:

➤ Change the list range as required and press Enter.

For a List with Blank and Non-Blank Cells

When you don’t know the number of rows and the cells are a mix of blank and non-blank cells, use the formula given below:

➤ Replace the list range if needed and press Ok.

➤ If you want more than one random value, you can drag the formula down using the fill handle (+ sign on the bottom corner of the cell).

If you use Excel 365, the easiest way to select randomly is by applying the TAKE, SORTBY, RANDARRAY, and COUNTA functions. First, the COUNTA function counts the number of non-empty cells in a range. After that, the RANDARRAY function generates an array of random numbers for those cells.
While the SORTBY function sorts the array based on the values in one or more corresponding arrays, the TAKE function returns a specified number of rows or columns from the start or end of an array. Here’s how to use them in a formula to return random values without duplicates:

➤ In a cell where you want to first random value, insert the following formula:

➤ This formula returns 3 random IDs from column A. Change the list range A2:A10 and number of returned values 3 according to your dataset.
➤ Press Enter.

With the RAND function, you can assign random numbers to each row of your selection. The RANDBETWEEN function does the same, except that it only assigns integers instead of fractions. While using this method, we’ll assign a random number to each row. After that, we’ll apply a formula to extract random values in a different location.
For this, we’ll use the RANK function to return the rank of a number within a list. Later on, the INDEX function will return the value for that specific position. Let’s get to the process:

➤ In a helper column cell, insert any of the following formulas:

Or,

➤ Replace the number of rows as necessary. Click Enter and drag the formula down.

➤ Now, in a different cell (cell F2) where you want to put the first random value, enter this formula:

➤ Here, A$2:$A$10 is the range from where you’re choosing randomly. E2 is the first cell of the helper column and $E$2:$E$10 is the entire helper column range. Change the values as needed.
➤ Now, Excel will return the first randomly selected value. You can drag the formula down to get more.

➤ If you want to delete the helper column, copy the output values with the  CTRL  +  C  shortcut. Now, right-click on the same values and select Paste Special >> Values >> Ok.

For larger datasets, it’s better to use a custom VBA specifically coded to return random values. It allows you to choose as many values you want. Here’s how:

➤ To add the Developer tab in the top ribbon, go to Files >> More >> Options.

➤ Now, choose Customize Ribbon and click on the Developer box. Press Ok.

➤ Open the Developer tab from the top ribbon and select the Visual Basic option.

➤ In the VBA Editor, click on Insert and choose Module.

➤ Paste the following code in the module box:

Sub PickRandomValues()
    Dim rngInput As Range
    Dim rngOutputStart As Range
    Dim inputValues() As Variant
    Dim outputCount As Long
    Dim pickedIndexes As Collection
    Dim i As Long, index As Long
    ' Ask user to select the input range
    On Error Resume Next
    Set rngInput = Application.InputBox("Select the range of values to pick from:", "Input Range", Type:=8)
    On Error GoTo 0
    If rngInput Is Nothing Then Exit Sub
    ' Ask user how many values to pick
    outputCount = Application.InputBox("How many random values do you want to pick?", "Number of Values", Type:=1)
    If outputCount <= 0 Then Exit Sub
    ' Validate the number of picks
    If outputCount > rngInput.Cells.Count Then
        MsgBox "You cannot pick more values than available in the list.", vbExclamation
        Exit Sub
    End If
    ' Ask user where to output the results
    On Error Resume Next
    Set rngOutputStart = Application.InputBox("Select the first cell for output:", "Output Cell", Type:=8)
    On Error GoTo 0
    If rngOutputStart Is Nothing Then Exit Sub
    ' Store values in array
    inputValues = rngInput.Value
    ' Initialize collection to store unique random indexes
    Set pickedIndexes = New Collection
    Randomize
    Do While pickedIndexes.Count < outputCount
        index = Int((rngInput.Cells.Count) ➤ Rnd + 1)
        On Error Resume Next
        pickedIndexes.Add index, CStr(index)
        On Error GoTo 0
    Loop
    ' Output selected values
    For i = 1 To pickedIndexes.Count
        rngOutputStart.Cells(i, 1).Value = rngInput.Cells(pickedIndexes(i), 1).Value
    Next i
    MsgBox outputCount & " random value(s) picked successfully!", vbInformation
End Sub

➤ Click on the Run tab >> Run Sub/UserForm. You can also press  F5  instead.

➤ As Excel prompts you, go back to the Excel tab and select the input cell range or list from where you want to choose random values. Click Ok.

➤ Now, in the Number of Values box, define how many random values you want and press Ok.

➤ Finally, choose an output cell for the returned value(s) selected randomly.

➤ As you click Ok, Excel will show the result like this:

Frequently Asked Questions

How to randomly select cells based on criteria in Excel?

Let’s say we’re selecting 3 random employees from the Sales department. To select randomly based on this criteria, enter the following formula in a blank cell:
=INDEX(A:A, AGGREGATE(15,6,ROW(A2:A10)/(B2:B10=”Sales”), RANDBETWEEN(1,COUNTIF(B2:B10,”Sales”))))
If the criteria Sales is found in the range B2:B10, Excel will randomly return a value from the corresponding cells of column A (A2:A10 range).

How to randomly group a list in Excel?

If we have 20 names in A2:A21 and we want to make 4 random groups, first, we’ll create a helper column (column B) and enter this formula:
=RAND()
Press Enter and drag down. Now, select your entire data range and sort them by the helper column (smallest to largest). Finally, use a different column (column C) to assign groups using this formula:
=MOD(ROW()-2, 4)+1
As you press Enter and drag down, this formula gives group numbers 1 to 4 repeatedly.

How to rank randomly in Excel?

Create a helper column (column B) and insert this formula:
=RAND()
Click Enter and drag down. In a new column (column C), enter this formula:
=RANK.EQ(B2, $B$2:$B$10, 1)
It ranks all random numbers.

Concluding Words

As explained, there are several ways to select randomly from a list in Excel. Choose one based on your Excel version and your dataset’s requirements. However, keep in mind that all random functions recalculate with every worksheet change. If you want to freeze the results, copy them and use Paste Special >> Values.