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.

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.

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.

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.

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

Method 4 – Select Random Sample without Duplicates

Steps:

Enter the following formula in cell D3.

=RAND()

➤ Press ENTER.

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.