## 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
1. 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()``
2. 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
1. Select both the data range and the helper column, go to the Data tab, click on Sort.
2. In the Sort dialog box, you need to:
1. Sort by your helper column ("Helper column" in our example).
2. Sort on cell values.
3. Select the sort order you need.
4. 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.

1. 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)``
2. 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
1. 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()``
2. 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
1. 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)``
2. 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
1. 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.
##### 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:

### The Best Office Productivity Tools

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

 🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions… Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ... Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup... Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List... Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Columns to Select Same & Different Cells ... Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ... Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

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.
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Will this provide weighted results if there are multiple copies of a name on the list? I am looking for something that provides more chances the more your name is on the list.
This comment was minimized by the moderator on the site
Hi Pat Meyer,
You may need to attach a screenshot or a sample file to describe the problem you encountered more clearly. Sorry for the inconvenience.
This comment was minimized by the moderator on the site
the problem with this is that it needs a helper column as long as the data column, even if only pulling a few values. (i tried it, and it only pulled from the cells that were aligned with the helper column). not good for me since my data is 10000 cells. but i found a much easier way that doesnt require a helper column.
This comment was minimized by the moderator on the site
You found a much easier way? Then tell us.
This comment was minimized by the moderator on the site
Is there a way for it to pick randoms without repeats of names?
This comment was minimized by the moderator on the site
Hi Justin,Sorry for the inconvenience. We have updated the post with adding a new part "pick randoms without duplicates". Please have a try.
This comment was minimized by the moderator on the site
As far as I can tell, this formula allows duplicates if you drag the formula down in column B.
There are no comments posted here yet