How to Select Random Sample in Excel (4 Methods)

Get FREE Advanced Excel Exercises with Solutions!

There are several ways to Select Random Sample in Excel. Among them, we will show you 4 simple, easy, and effective methods to select random samples in Excel.


4 Methods to Select Random Sample in Excel

The following table shows Name and Salary columns. We will use 4 methods to select random name and salary from this table. Here, we used Excel 365. You can use any available Excel version.

How to Select Random Sample in Excel


Method-1: Using Data Analysis Toolbar to Select Random Sample

In this method, we can select random values from numeric values only using the Data Analysis toolbar. Here, we will select 5 random salaries in the Random 5 Salary column.

➤To begin with, we will go to the Data tab in the ribbon, and we will select Data Analysis tool.

How to Select Random Sample in Excel

A Data Analysis window will appear.

➤ We will select Sampling as Analysis Tools.

➤ Click on OK.

Now, a Sampling window will appear.

➤ We will select the Input Range from C3 to C12, in the Number of Sample box, we will type 5

➤ In the Output Range we will select cells from E3 to E7, and we will click OK.

Finally, we can see 5 random salaries in the Random 5 Salary column.

Read More: Random Selection Based on Criteria in Excel 


Method-2: Using RAND Function

In this case, we will use the RAND function to select the top 5 random rows.

➤ First of all, we will type the following formula in cell D3.

=RAND()

Here, the RAND function returns in cell D3 with a random number.

➤ After that, we will press ENTER.

We can see a random number in cell D3.

➤ We will now drag down the formula with the Fill Handle tool.

Now, we can see random numbers in the Random Number column.

How to Select Random Sample in Excel

Now, we want only values in the Random Number column.

➤ To do so, we will select from cell D3 to D12, and we right click on the mouse.

➤ After that, we click on Copy.

Now, we will paste the copied number into the same cells.

➤ We will select from cell D3 to D12, and we will right-click on the mouse.

➤ After that, we will click on Paste Special and then click on Paste Values.

Now, we can see in the Random Number column that there is no formula in the formula bar.

Therefore, there are only values in the Random Number column.

➤ Now, we will select our entire dataset, and we will go to the Home tab.

➤ After that, we will select Editing>Sort & Filter>Custom Sort.

Now, a Sort window will appear.

➤ We will select Sort by as Random Number, and Order as Largest to Smallest.

➤ Then click OK.

Finally, we can see the sorted Name and Salary according to the largest to smallest Random Number.

➤ Now, we will select the top 5 Name and Salary, and we will right click on the mouse, and we will select Copy.

➤ Now, we paste the top 5 Name and Salary in columns F and G.

How to Select Random Sample in Excel

Read More: How to Randomly Select Rows in Excel 


Method-3: Using INDEX, RANDBETWEEN and ROWS Functions

In this method, we want to select a single random name.

We will use the  INDEX, RANDBETWEEN, and ROWS functions.

➤ First of all, we will type the following function in cell E3.

=INDEX($B$3:$B$17,RANDBETWEEN(1,ROWS($B$3:$B$17)))

Here,

  • ROWS($B$3:$B$17)→ Returns number of rows between cells B3 to B17.
  • RANDBETWEEN(1,ROWS($B$3:$B$17))→ Returns a random number between 1 and number of rows.
  • INDEX($B$3:$B$12, RANK(D3,$D$3:$D$12), 1)→ The number returned by RANDBETWEEN is fed to the row_num argument of the INDEX function, so it picks the value from that row. In the column_num argument, we supply 1 because we want to extract a value from the first column.

➤ Now, press ENTER.

How to Select Random Sample in Excel

We can see a random name Robb in our Single Random Name column.

How to Select Random Sample in Excel

Read More: How to Generate a Random String from a List in Excel 


Method-4: Select Random Sample without Duplicates

In this method, we can select random samples without duplicates. In the following table, we want Random Number and Random Name. Here, we will use the RAND, INDEX, and RANK functions.

How to Select Random Sample in Excel

➤ First of all, we will type the following formula in cell D3.

=RAND()

➤ Press ENTER.

How to Select Random Sample in Excel

We can see a random number in cell D3.

➤ We can copy the formula with the Fill Handle tool.

Now, we can see random numbers in the Random Number column.

➤ Now, we will type the following formula in cell E3.

=INDEX($B$3:$B$12, RANK(D3,$D$3:$D$12), 1)

Here,

  • RAND()→Returns column D with random numbers.
  • RANK(D3,$D$3:$D$12)→ Returns the rank of a random number in the same row. For example, RANK(D3,$D$3:$D$12) in cell E3 gets the rank of the number in D3. When copied to D4, the relative reference D3 changes to D4 and returns the rank of the number in D4, and so on.
  • INDEX($B$3:$B$12, RANK(D3,$D$3:$D$12), 1)→The number returned by RANK is fed to the row_num argument of the INDEX function, so it picks the value from that row. In the column_num argument, we supply 1 because we want to extract a value from the first column.

➤ Press ENTER.

Now, we can see a random name Ruth in cell E3.

➤ We can drag down the formula with the Fill Handle tool.

Finally, we can see 5 random names in the Random Name column without duplicates.

How to Select Random Sample in Excel

Read More: How to Make a Random Selection from List Without Repetition in Excel


Download Workbook


Conclusion

Here, we tried to show you some easy and effective methods that will help you to select random sample in Excel. We hope you will find this article helpful. If you have any queries or suggestions, please feel free to know us in the comment section.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
Afia Kona
Afia Kona

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo