How to Select Random Cells in Excel (5 Ways)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes you may need to select some random cells and show them in your Excel workbook. If you are searching for a way to select random cells in Excel, then you have landed in the right place. I will show you how to select random cells in Excel in this article.

Let’s say, we have got a dataset of Names of the Salesman of an organization and their respective amount of Sales over a certain period of time.

Dataset for Selecting Random Cells in Excel

We want to select some random cells from this list of data. For this purpose, we will use different functions and features of Excel.

In this section, you will find 5 suitable and effective ways to select random cells in Excel with proper illustrations. I will demonstrate them one by one here. Let’s check them now!


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

For our present set of data, we will show the process of selecting random cells in Excel. We will use RAND, INDEX, RANK.EQ functions for this purpose. In order to do so, proceed with the following steps.

Steps:

  • First of all, create two new columns with the heading Random Value and Random Cells.

Functions to Select Random Cells in Excel

  • Then, type the following formula in a cell under the Random Value column.

=RAND()

RAND Function to Select Random Cells in Excel

  • Now, press ENTER, and the cell will show a random value for the function.
  • Here, drag the Fill Handle tool down the cells.

  • Hence, the cells will Autofill the formula.

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

Paste Values to Select Random Cells in Excel

  • Then, apply the following formula to a cell under the Random Cells column to show a randomly selected cell.

=INDEX($B$5:$B$12,RANK.EQ(C5,$C$5:$C$12),1)

Here,

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

Apply Formula to Select Random Cells in Excel

Formula Breakdown

RANK.EQ(C5,$C$5:$C$12) gives the rank of the cell value of C5 (i.e. 0.75337963) in the range $C$5:$C$12. So, it returns 5.

INDEX($B$5:$B$12,RANK.EQ(C5,$C$5:$C$12),1) returns the value at the intersection of Row 5 and Column 1. So, the output is Stuart.

  • Now, drag the formula down and you will be able to select the random cells.

Select Random Cells in Excel

Read More: Select All Cells with Data in Excel 


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

For the same set of data, we will now select some random cells by using 4 relevant functions. They are UNIQUE, RANDARRAY, INDEX, RANK.EQ functions. You get to know the process by following the steps below.

Steps:

  • Firstly, type the following formula to get a random value.

=UNIQUE(RANDARRAY(8,1,1,8)

Here,

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

  • Then, press ENTER, and all the cells will show corresponding random values for the Salesman Column.

UNIQUE,RANDARRAY to select Random Cells in Excel

  • Now, copy the cells and paste the values only to convert the formula into value.

  • After that, apply the following formula to get the randomly selected cell.

=INDEX($B$5:$B$12,RANK.EQ(C5,$C$5:$C$12),1)

Here,

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

Formula Breakdown

RANK.EQ(C5,$C$5:$C$12) gives the rank of the cell value of C5 (i.e. 0.75337963) in the range $C$5:$C$12. So, it returns 4.

INDEX($B$5:$B$12,RANK.EQ(C5,$C$5:$C$12),1) returns the value at the intersection of Row 4 and Column 1. So, the output is Hopper.

  • Here, drag the formula down to get the random cells.

Drag Formula to select Random Cells in Excel

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


3. Applying RAND, INDEX, RANK.EQ, COUNTIF Functions

We will now use a combination of the RAND, INDEX, RANK.EQ, and COUNTIF functions to select random cells in Excel. To demonstrate this method, follow the steps below.

Steps:

  • First of all, proceed like Method 1 to get the Random Values with the RAND function.

  • Now, apply the following formula to get a randomly selected cell.

=INDEX($B$5:$B$12,RANK.EQ(C5,$C$5:$C$12)+COUNTIF($C$5:C5,C5)-1,1)

Here,

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

Apply Formula to Select Random Cells in Excel

Formula Breakdown

RANK.EQ(C5,$C$5:$C$12) gives the rank of the cell value of C5 (i.e. 0.75337963) in the range $C$5:$C$12. So, it returns 2.

COUNTIF($C$5:C5,C5) returns the number of cells with the value of C5. So, it gives 1.

2+1-1=2

INDEX($B$5:$B$12,RANK.EQ(C5,$C$5:$C$12)+COUNTIF($C$5:C5,C5)-1,1) returns the value at the intersection of Row 2 and Column 1. So, the output is Adam.

  • Here, drag the formula to the next cells to get the output.

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


4. Use of INDEX, SORTBY, RANDARRAY, ROWS, SEQUENCE Functions to Choose Random Cells

Now, we will use a combination of INDEX, SORTBY, RANDARRAY, ROWS, and SEQUENCE functions to select random cells in Excel.

So, let’s start the process like the one below.

Steps:

  • First of all, type the following formula to a selected cell.

=INDEX(SORTBY(B5:B12,RANDARRAY(ROWS(B5:B12))),SEQUENCE(5))

Here,

  • B5:B12=  Range of the Salesman

Combination of Functions to Select Random Cells in Excel

Formula Breakdown

ROWS(B5:B12) gives the number of rows in the mentioned range= 8.

RANDARRAY(ROWS(B5:B12)) results in random 9 numbers.\

SEQUENCE(5) returns a range of the serial numbers (1 to 5).

Finally, INDEX(SORTBY(B5:B12,RANDARRAY(ROWS(B5:B12))),SEQUENCE(5)) returns 5 cell values.

  • Then, 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 


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.

In order to apply this procedure, proceed in the steps below.

Steps:

  • Firstly, right-click on the sheet name and select View Code from the options.

  • Then, a window for entering the Code will appear here. Enter the Code here. You can use the following.

Code:

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

VBA code to select Random Cell Values

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

Result of VBA

Read More: How to Select Highlighted Cells in Excel 


Download Practice Workbook

You can download the practice book from the link below.


Conclusion

I have tried to show you some methods to select random cells in Excel in this article. Thanks for reading this article! I hope this article has shed some light on your way of selecting random cells in an Excel workbook. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box. This will help me enrich my upcoming articles. Have a great day!


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Hasan
Rafiul Hasan

Rafiul Hasan, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering & Technology, contributes significantly to the ExcelDemy project with almost 1.6 years of dedicated work. Currently an Excel and VBA Content Developer, he has a passion for problem-solving. Authoring over 100 articles for ExcelDemy showcases expertise in Microsoft Office Suites and Data Analysis. In addition to content development, Rafiul actively engages with the ExcelDemy forum, offering valuable solutions to user queries and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo