Sometimes you may need to select some random cells and show them in your Excel workbook. If you are in 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.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice book from the link below.

## 5 Suitable Ways to Select Random Cells in Excel

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.

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. Select Random Cells Using RAND, INDEX, RANK.EQ Functions

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:**

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

`=RAND()`

- 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.

- 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

**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.

**Read More:** **How to Select Multiple Cells in Excel (7 Quick Ways)**

### 2. Using 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.

- 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.

**Read More:** **How to Select a Range of Cells in Excel Formula (4 Methods)**

### 3. Using RAND, INDEX, RANK.EQ, COUNTIF Functions

We will now use a combination of the **RAND**, **INDEX**, **RANK.EQ**, **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

**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 Specific Rows in Excel Formula (4 Easy Ways)**

**Similar Readings**

**How to Select Data in Excel for Graph (5 Quick Ways)****How Do I Quickly Select Thousands of Rows in Excel (2 Ways)****[Solved!] CTRL+END Shortcut Key Goes Too Far in Excel (6 Fixes)****Excel VBA to Protect Sheet but Allow to Select Locked Cells (2 Examples)****How to Select Multiple Cells in Excel without Mouse (9 Easy Methods)**

### 4. Use of INDEX, SORTBY, RANDARRAY, ROWS, SEQUENCE Functions

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

**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 Row in Excel If Cell Contains Specific Data (4 Ways)**

### 5. Select Random Cells Using 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 like 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
```

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

**Read More:** **How to Select Only Filtered Cells in Excel Formula (5 Quick Ways)**

## 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**

**How to Select Cells in Excel Without Dragging (7 Ideal Examples)****How to Select Highlighted Cells in Excel (4 Easy Techniques)****[Fixed!] Selected Cells Not Highlighted in Excel (8 Solutions)****How to Select Large Data in Excel Without Dragging (5 Easy Ways)****Select & Delete Blank Cells in Excel (3 Quick Ways)****How to Go to the End of Excel Sheet (2 Quick Methods)**