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
  • 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
• A few clicks to select random sample with a handy tool

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.

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.

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.
   

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.
   

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.

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.

Related Articles