In statistics, we often take samples from a population and use the data from the sample to draw conclusions about the population as a whole.
One commonly used sampling method is stratified random sampling, in which a population is split into groups and a certain number of members from each group are randomly selected to be included in the sample.
The following step-by-step example shows how to perform stratified random sampling in Excel.
Step 1: Enter the Data
First, let’s enter the following dataset into Excel:
Next, we’ll perform stratified random sampling in which we randomly select two players from each basketball team to be included in the final sample.
Step 2: Enter Random Values for Each Row
Next, let’s create a new column titled Random and type in =RAND() for the first value:
This generates a random value between 0 and 1.
Next, hover over the bottom right corner of the cell until a tiny cross ( + ) appears and double click it to paste the =RAND() formula to all remaining cells in the column.
Unfortunately, each time we hit Enter the random cell values will change. To prevent this, copy every value in column E then right click and choose Paste Values into the same column so that the random values will no longer change.
Step 3: Sort Data Values
Next, highlight all of the data. Then click the Data tab along the top ribbon. Then click the Sort button within the Sort & Filter group.
In the new window that appears, sort first by Team from A to Z, then sort by Random from Smallest to Largest.
Once you click OK, the data will be sorted accordingly.
Step 4: Select the Final Sample
The final sample will simply be the first two rows from each team:
The final sample will include the following player ID’s from each team:
- Team A: 3, 5
- Team B: 9, 6
- Team C: 15, 11
Our stratified random sampling is complete because we have now chosen two players from each team.
Additional Resources
The following tutorials explain how to select other types of samples from a population using Excel:
How to Select a Random Sample in Excel
How to Perform Systematic Sampling in Excel
How to Perform Cluster Sampling in Excel