How to Perform Stratified Sampling in Excel

This guide will explain how to perform stratified sampling in Excel

Excel is an excellent tool for performing different statistical tasks, such as inputting a population and obtaining a sample based on a population. So a sample is a group of people, objects, or items extracted from a larger population for measurement. 

And a sample is essentially a representative of the larger population to ensure that we can generalize the findings or draw conclusions from the sample to describe or represent the population as a whole. 

Furthermore, there are many different ways to calculate or get a sample. And these are usually called sampling methods. In this case, we will be focusing on stratified sampling. 

So this type of sampling method splits the population into groups. Then, we can randomly select members from each group to be part of the sample. Additionally, stratified sampling ensures that members from each group in the population are represented in the sample. 

Thus, we can generally draw fair conclusions to describe the population. Since Excel contains many functions and tools, this makes performing stratified sampling in Excel simple and easy. 

In this case, we will utilize the RAND function to input a random value for each data value. Then, we will use the sort and filter tools to organize the data set and easily select members for the sample. 

Let’s take an example wherein we must perform stratified sampling in Excel. 

Suppose you have a population list containing the section of the students and each student’s late and absences. So the students are grouped by section. Since we want a sample that properly represents the entire population and has enough members from each group, we will perform stratified sampling. 

And Excel makes the entire process easier with the help of built-in functions and tools. 

Before we continue, let’s first explain how to use the RAND function in Excel.

 

 

The Anatomy of the RAND Function

The syntax or the way we write the RAND function is as follows:

=RAND()

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we begin any function in Excel.
  • RAND() refers to our RAND function. And this function will return a random number greater than or equal to 0 and less than 1 evenly distributed. Additionally, this changes every recalculation. 

Note: The value from the RAND function changes every time we hit the Enter key. 

Great! Now let’s move on and discuss a real example of performing stratified sampling in Excel.

 

 

A Real Example of Performing Stratified Sampling in Excel

Let’s say we have a data set containing the student ID, the section of the student, and the number of times each student has been absent and late. So our initial data set would look like this:

Initial data set

 

Since we want to ensure that the students from each section are properly represented in the sample, we will use stratified sampling. In this case, we will have a sample size of 6, meaning we will select two students from each section or group.

Firstly, we need to enter random values for each data value in a new column. So we will be using the RAND function to do this. And this function will generate a random value between 0 and 1. However, an issue with this function is that it changes value every time we press the Enter key. 

To prevent the value from changing, we can simply copy the entire column and use the paste values option to place the values in the same column. Thus, the random values will not change anymore. 

Secondly, we will sort the data values using the sort and filter tools in the Data tab. Essentially, we will sort the section in alphabetical order. Then, we will also sort the column containing the random values from smallest to largest or in ascending order. 

Lastly, we can select the first rows from each section to be part of our sample. Since we want a sample size of 6, we will select two rows from each section. And we can copy and paste those rows into a new data set to represent our sample. 

So our final data set would look like this:

Final data set

 

You can make your own copy of the spreadsheet above using the link attached below. 

Amazing! Now let’s learn the process of how to perform stratified sampling in Excel. 

 

 

How to Perform Stratified Sampling in Excel

In this section, we will explain the step-by-step process of how to perform stratified sampling in Excel. Furthermore, each step contains detailed instructions and pictures to guide you along the way.

1. Firstly, we need to create a new column to input the random values. Then, we will generate random values for each data value in the data set. To do this, we will use the RAND function. So type in the formula “=RAND()”. Lastly, we will press the Enter key to return the result.

Stratified Sampling in Excel

 

2. Secondly, we will drag down the Fill Handle tool to copy the formula and get a random value for each data value.

Drag fill handle

 

3. Thirdly, we need to prevent the random values from changing. To do this, we can simply copy the entire column by pressing Ctrl + C. Then, we will right-click and select Paste Values in the same column.

Copy and paste

 

4. Next, we need to sort out the data values. Firstly, we will head over to the Data tab and select Custom Sort under the Sort & Filter group.

Stratified Sampling in Excel

 

5. Afterward, we will sort two columns. So we will first sort the Section column in alphabetical order. To do this, we can simply select Sort Ascending in the dropdown menu of Order.

Next, we will select Add on the top left to sort another column. So we will sort the Random column, which we can choose in the dropdown menu beside Then by. Afterward, we will sort it in ascending order so choose Sort Ascending in the dropdown menu. 

Next, make sure to check My data has headers to not include the headers in the sort. Lastly, we will click OK to apply the changes.

Stratified Sampling in Excel

 

6. Since we want a sample size of n = 6, we can select two rows from each section. Then, we can copy and paste those rows into a new data set to represent our sample.

Stratified Sampling in Excel

 

7. And tada! We have performed stratified sampling in Excel.

Stratified Sampling in Excel

 

And that’s pretty much it! We have discussed how to perform stratified sampling in Excel. Now you can easily and quickly use this method whenever you need to obtain a sample from a larger population using the stratified sampling method. 

Are you interested in learning more about what Excel can do? You can now use the RAND function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like