How to Perform Stratified Sampling in Excel (Step-by-Step)


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

Leave a Reply

Your email address will not be published.