This guide will explain **how to perform stratified sampling in Excel**.

##### Table of Contents

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:

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:

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.

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

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.

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.

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.

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.

7. And tada! We have performed 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.