How to Select Random Cells in Excel (5 Ways)

Consider a dataset of Salesmen of an organization and their respective Sales over a certain period of time. We want to select some random cells from this list.

Method 1 – Combining RAND, INDEX, and RANK.EQ Functions to Select Random Cells in Excel

Steps:

• Create two new columns with the headings Random Value and Random Cells.

• Use the following formula in the first cell under the Random Value column.

• Press Enter, and the cell will show a random value for the function.
• Drag the Fill Handle tool down the column.

• Excel will Autofill the formula.

• Copy the cells and use the Paste Special option (i.e. Paste Values) to paste the values only.

• Apply the following formula to the first cell under the Random Cells column to show a randomly selected cell.

• $B$5:$B$12=  Range of the Salesman
• $C$5:$C$12= Range of the Random Value
• C5= Random value

• Drag the formula down and you will be able to select the random cells.

Read More: Select All Cells with Data in Excel 

Method 2 – Selecting Random Cells with UNIQUE, RANDARRAY, INDEX, and RANK.EQ Functions 

Steps:

• Use the following formula to get a random value.

Here,

• 8= Total number of Rows
• 1= Total number of Columns
• 1= Minimum number
• 8= Maximum number

• Press Enter, and all the cells will show corresponding random values for the Salesman Column.

• Copy the cells and paste the values only to convert the formulas into values.

• Apply the following formula to get the randomly selected cell:

Here,

• $B$5:$B$12=  Range of the Salesman
• $C$5:$C$12= Range of the Random Value
• C5= Random value

• Drag the formula down to get the random cells.

Read More: Select All Cells with Data in a Column in Excel

Method 3 – Applying RAND, INDEX, RANK.EQ, and COUNTIF Functions

Steps:

• Follow Method 1 to get the Random Values with the RAND function.

• Apply the following formula to get a randomly selected cell.

Here,

• $B$5:$B$12=  Range of the Salesman
• $C$5:$C$12= Range of the Random Value
• C5= Random value

• Drag the formula to the next cells to get the output.

Read More: How to Select Cells with Certain Value in Excel

Method 4 – Use INDEX, SORTBY, RANDARRAY, ROWS, and SEQUENCE Functions to Choose Random Cells

Steps:

• Use the following formula to get a selected cell.

Here,

• B5:B12=  Range of the Salesman

• Press Enter and you will get the output for all cells you want (i.e. 5).

Read More: How to Select Blank Cells in Excel and Delete 

Method 5 – Select Random Cells Using Excel VBA Code

For the same set of data, we will now select a random cell from the given list using a VBA code. The newly created cell (i.e. E5) under the Random Cell column will return the selected random cell.

Steps:

• Right-click on the sheet name and select View Code from the options.

• A window for entering the code will appear here.
• Enter the following Code:

Code:

Sub Select1Random_Name()
Dim xCell As Long
xCell = [RandBetween(5,12)]
Cells(5, 5) = Cells(xCell, 2)
End Sub

• The output will be shown at cell(5,5) which means cell E5.

Read More: How to Select Highlighted Cells in Excel 

Download the Practice Workbook

Related Articles

• How to Select Only Filtered Cells in Excel Formula 
• [Fixed!] Selected Cells Not Highlighted in Excel
• Selecting Non-Adjacent or Non-Contiguous Cells in Excel

<< Go Back to Select Cells | Excel Cells | Learn Excel