How to Select a Random Sample in Excel (4 Methods)

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

Steps:

  • Go to the Data tab in the ribbon and select the Data Analysis tool.

How to Select Random Sample in Excel

A Data Analysis window will appear.

  • Select Sampling as Analysis Tools.
  • Click on OK.

A Sampling window will appear.

  • Select the Input Range from C3 to C12, in the Number of Sample box, we will type 5
  • In the Output Range, select cells from E3 to E7 and click OK.

We can see 5 random salaries in the Random 5 Salary column.


Method 2 – Using the  RAND Function

Steps:

➤ Enter the following formula in cell D3:

=RAND()

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

  • Press ENTER.

We can see a random number in cell D3.

  • Drag down the formula with the Fill Handle tool.

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.

  • Select from cell D3 to D12.
  • Click on Copy.

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

  • Select from cell D3 to D12, and right-click.
  • Click on Paste Special and click on Paste Values.

We can see in the Random Number column that there is no formula in the formula bar.

Tere are only values in the Random Number column.

  • Select our entire dataset, and go to the Home tab.
  • Select Editing>Sort & Filter>Custom Sort.

A Sort window will appear.

  • Select Sort by as Random Number, and Order as Largest to Smallest.
  • Click OK.

We can see the sorted Name and Salary according to the largest to smallest Random Number.

  • Select the top 5 Name and Salary, and right click on the mouse.
  • Select Copy.

  • Paste the top 5 Name and Salary in columns F and G.

How to Select Random Sample in Excel


Method 3 – Using INDEX, RANDBETWEEN and ROWS Functions

Steps:

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

 

  • 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: Random Selection Based on Criteria in Excel


Method 4 – Select Random Sample without Duplicates

Steps:

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

  • Copy the formula with the Fill Handle tool.

We can see random numbers in the Random Number column.

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

We can see a random name Ruth in cell E3.

  • Drag down the formula with the Fill Handle tool.

We can see 5 random names in the Random Name column without duplicates.

How to Select Random Sample in Excel

Read More: Random Selection from List with No Duplicates in Excel


Download the Workbook


Random Selection in Excel: Knowledge Hub


<< Go Back to Randomize in Excel Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Afia Kona
Afia Kona

Afia Aziz Kona, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo