## Make random sample selection in Excel (full guide)

Ever found yourself overwhelmed by too much data in Excel and just wanted to pick out a few items at random for analysis? It's like trying to taste-test candies from a huge jar! This guide will help you with simple steps and formulas to select a random sample, whether it's values, rows, or even picking non-repeating items from a list. Plus, for those who want a super fast method, we've got a cool tool for you. Join us and make Excel easy and fun!

### Select random sample with formulas

In this section, we've gathered various formulas to assist you in selecting different types of random samples based on your needs. For instance, you can choose rows at random from a data range or pick random values from a list, either with or without duplicates. Moreover, if you're using Excel versions 365 or 2021, you'll be introduced to new functions that can help you easily select random values from a list.

#### Select random values/rows with the RAND function

Assuming that you have a data range A1:D53 as shown in the following screenshot, to randomly select values from one of the columns or randomly select rows from the entire data range, you can try the following.

**Note**: The method provided in this section will directly change the order of your original data, so it's advisable to create a backup of your data.

##### Step 1: Adding a helper column

- First, you need to add a helper column to your data range. In this case, I select the cell E1 (the cell adjacent to the header cell in the last column of the data range), enter the column header, and then enter the below formula in cell E2 and press
**Enter**to get the result.**Tip**: The RAND function will generate a random number between 0 and 1.`=RAND()`

- Select that formula cell. Then double click the
**Fill Handle**(the green square in the lower right corner of the cell) to fill this formula into the rest of cells in the helper column.

##### Step 2: Sorting the helper column

- Select both the data range and the helper column, go to the
**Data**tab, click on**Sort**. - In the
**Sort**dialog box, you need to:**Sort by**your helper column ("Helper column" in our example).**Sort on**cell values.- Select the sort
**order**you need. - Click the
**OK**button. See screenshot.

Now the entire data range has been sorted by the helper column.

##### Step 3: Copying and pasting the random rows or values to get results

After sorting, the rows in your original data range will be in a random order. You can now simply select the top n rows, where n is the number of random rows you wish to select. Then press **Ctrl** +** C** to copy the selected rows and paste them wherever you want.

**Tip**: If you just want to randomly select values from one of the columns, simply select the top n cells in that column.

**Notes**:

- To refresh the random values, press the
**F9**key. - Every time you refresh the worksheet, such as adding new data, modifying cells, removing data, etc., the formula results will automatically change.
- If you don't need the helper column anymore, you can delete it.
- If you're looking for an even simpler approach, consider trying the "
**Select Range Randomly"**feature of**Kutools for Excel**. With just a few clicks, it allows you to easily select random cells, rows, or even columns from a specified range. Click here to start your 30-day free trial of Kutools for Excel.

#### Select random values from a list with the RANDBETWEEN function

The above method requires you to manually select and copy the number of rows or values from the data range after sorting. If you want to automatically generate a specified number of random values from a list, the method in this section can help you get it done.

- In this case, I need to generate 7 random values from the range B2:B53. I select a blank cell D2, enter the following formula and press
**Enter**to get the first random value from column B.`=INDEX($B2:$B53,RANDBETWEEN(1,COUNTA($B2:$B53)),1)`

- Then select this formula cell and drag it’s
**Fill Handle**down until the rest of the 6 random values are generated.

**Notes**:

- In the formula,
**$B2:$B53**is the range from which you want to pick random sample. - To refresh the random values, press the
**F9**key. - If there are duplicates in the list, duplicate values may appear in the results.
- Every time you refresh the worksheet, such as adding new data, modifying cells, removing data, etc., the random results will automatically change.

#### Select random values from a list without duplicates

The above method may cause duplicate random values in the results. Take the same example as above, to get random values from a list without duplicates, you can try the method in this section.

##### Step 1: Adding a helper column

- Firstly, you need to create a helper column next to the column from which you want to pick random sample. In this case, I select cell C2 (the cell adjacent to the second cell of column B), enter the below formula and press
**Enter**.**Tip**: The RAND function will generate a random number between the 0 and 1.`=RAND()`

- Select that formula cell. Then double click the
**Fill Handle**(the green square in the lower right corner of the cell) to fill this formula for the rest of cells in the helper column.

##### Step 2: Get random values from a list without duplicates

- Select a cell adjacent to the first result cell of the helper column, enter the below formula and press
**Enter**to get the first random value.`=INDEX($B$2:$B$53, RANK.EQ(C2, $C$2:$C$53) + COUNTIF($C$2:C53, C2) - 1, 1)`

- Then select this formula cell and drag it’s
**Fill Handle**down to get a random number of values.

**Notes**:

- In the formula,
**$B2:$B53**is the column list from which you want to pick random sample. And**$C2:$C53**is the helper column range. - To refresh the random values, press the
**F9**key. - The result will not contain duplicate values.
- Every time you refresh the worksheet, such as adding new data, modifying cells, removing data, etc., the random results will automatically change.

#### Select random values from a list in Excel 365/2021

If you are using Excel 365 or 2021, you can apply the new functions “**SORTBY**” and “**RANDARRAY**” to easily generate a random sample in Excel.

##### Step 1: Adding a helper column

- First, you need to add a helper column to your data range. In this case, I select cell C2 (the cell adjacent to the second cell of the column from which you want to pick random values), enter the below formula and press
**Enter**to get the results.`=SORTBY(B2:B53,RANDARRAY(COUNTA(B2:B53)))`

**Notes**- In the formula,
**B2:B53**is the list from which you want to pick random sample. - If you are using Excel 365, a list of random values will be generated automatically after pressing the
**Enter**key. - If you are using Excel 2021, after getting the first random value, select the formula cell and drag the fill handle down to get the desired number of random values.
- To refresh the random values, press the
**F9**key. - Every time you refresh the worksheet, such as adding new data, modifying cells, removing data, etc., the random results will automatically change.

- In the formula,

##### Step 2: Copy and paste the random values to get results

In the helper column, you can now simply select the top n cells, where n is the number of random values you wish to select. Then press **Ctrl** + **C** to copy the selected values, right click a blank cell, and select **Values** from the **Paste Options** section in the context menu.

**Notes**:

- To automatically generate a specified number of random values or rows from a specified range, enter a number representing the number of random values or rows to be generated in a cell (C2 in this example), and then apply one of the following formula.
**Generate random values from a list:**`=INDEX(SORTBY(B2:B53, RANDARRAY(ROWS(B2:B53))), SEQUENCE(C2))`

As you can see, each time you change the number of samples, a corresponding number of random values are automatically generated.**Generate random rows from a range:**To automatically generate a specified number of random rows from a specified range, apply this formula.`=INDEX(SORTBY(A2:B53, RANDARRAY(ROWS(A2:B53))), SEQUENCE(C2), {1,2,3})`

**Tip**: The array {1,2,3} at the end of the formula needs to match the number you specified in C2. If you want to generate 3 random samples, you not only need to enter the number 3 in the cell C2 but also must specify the array as {1,2,3}. To generate 4 random samples, enter the number 4 in the cell and specify the array as {1,2,3,4}.

### A few clicks to select random sample with a handy tool

The above methods require you to remember and use formulas, which is painful for some Excel users. Here, I would like to recommend you the **Select Range Randomly** feature of **Kutools for Excel**. With this feature, you can easily select random samples with just a few clicks It can randomly select not only values and rows, but also columns.

After installing Kutools for Excel, click **Kutools** > **Select** > **Select Range Randomly**, then you need to configure as follows.

- Select a column or a range from which you want to pick random values, rows, or columns.
- In the
**Sort/Select Range Randomly**dialog box, specify the number of random values to select. - Select an option in the
**Select Type**section. - Click
**OK**.

**Result**

I specified the number **5** in the "**No. of cells to select**" section and chose the "**Select random rows**" option in the "**Selection type**" section. As a result, 5 rows of data will be randomly selected in the specified range. You can then copy and paste these selected rows wherever you want.

**Notes**:

- To use this feature, you should install
**Kutools for Excel**first, please click to download and have a 30-day free trial now. - To learn more about this feature, please visit this page: Randomly select cells, rows or columns in a range of cells

### Related Articles

**Randomize a list in Excel**

In this tutorial, we will talk about how to easily randomize, or shuffle a list in Excel in few steps.

**Excel random data: generate random numbers, texts, dates, times**

In this article, we will introduce different methods for generating random numbers, text strings, dates and times in normal Excel and Excel 365.

**Quickly generate random time**

In this tutorial, we will talk about how to generate random time in Excel.

**Generate random weekday or weekend date**

This tutorial will show you how to generate random weekday or weekend dates in Excel.

**The Best Office Productivity Tools**

#### Kutools for Excel - Helps You To Stand Out From Crowd

**Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...**

#### Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.

**Comments**(7)

### Table of contents

- Select random sample with formulas
- Select random values/rows
- Select random values from a list
- Select random values from a list without duplicates
- Select random values from a list in Excel 365/2021
- Select random sample with Kutools
- Related Articles
- The Best Office Productivity Tools
- Comments