# How to select a random sample in Excel

You can watch a video tutorial here.

Excel can be used to select a random sample from a set of data. Random sampling is a method used often in statistical analysis. Suppose you have 200 students that take a class and you are interested in knowing their feedback about the class. You decide to interview the students but interviewing 200 students is too big a task. You decide instead to interview only a sample of 30 students with the confidence that this will give you a good idea of the opinion of the entire class. The problem now arises of how to select the 30 students. This is where random sampling can be used to pick 30 students at random from the list. In Excel, there are 2 ways in which this can be done:

- Using the
**Data Analysis**tool - Using the RAND() function: this returns a random number
- Syntax: RAND()

## Option 1 – Use the **Data Analysis** tool

*Note: If the **Data Analysis** button is present under **Data > Analyze, ** then skip steps 1 to 3*

### Step 1 – Open the **Excel Options** window

- Go to
**File > Options**

### Step 2 – Manage the Add-ins

- Go to
**Add-ins** - Select Excel Add-ins from the
**Manage**drop-down - Click
**Go**

### Step 3 – Load the **Analysis ToolPak **add-in

- Select
**Analysis ToolPak** - Click
**OK**

### Step 4 – Open the **Data Analysis** box

- Go to
**Data > Analyze** - Click on
**Data Analysis**

### Step 5 – Open the **Sampling **box

- Select
**Sampling** - Click
**OK**

### Step 6 – Set the parameters

- Set the parameters as follows:
- Input range: the range of the ‘No.’ column
- Sampling Method: random
- Number of Samples: 30

- Output range: a location on the same sheet

*Note: This method accepts only numeric data so the ‘No.’ column is used*

- Click
**OK**

### Step 7 – Create a formula for the names

- A list of 30 random numbers is extracted from the list
- Select the cell next to the first number in the list
- Type the formula using cell references:

=VLOOKUP(Sample, $range of the list of names$,2,FALSE)

*Note: The Vlookup formula looks for a value in a list and returns a value from the same row. The range is made constant so that the formula can be copied.*

## Step 8 – Copy the formula

- Using the fill handle from the first cell, drag the formula to the remaining cells

OR

- Select the cell with the formula and press
**Ctrl+C**or choose**Copy**from the context menu (right-click) - Select the rest of the cells in the column and press
**Ctrl+V**or choose**Paste**from the context menu (right-click)

- The list of random samples is created

## Option 2 – Use the RAND() function

### Step 1 – Create the formula

- Select the cell next to the first name
- Enter the function and press
**Enter**:

=RAND()

### Step 2 – Copy the formula

- A random number will be generated
- Using the fill handle from the first cell, drag the formula to the remaining cells

OR

- Select the cell with the formula and press
**Ctrl+C**or choose**Copy**from the context menu (right-click) - Select the rest of the cells in the column and press
**Ctrl+V**or choose**Paste**from the context menu (right-click)

### Step 3 – Open the **Sort** box

- Select the ‘Name’ column and the column with the random numbers
- Go to
**Data > Sort & Filter** - Click
**Sort**

### Step 4 – Set the parameters

- Select the column with the random numbers from the list for
**Sort by** - Click
**OK**

### Step 5 – Select the random sample

- The first 30 records serve as the random sample