How to select a random sample in Excel

We get a lot of questions about random samples and how to create them (which tells us a thing or two about our customers and how survey-smart they are). We have a fast, easy trick for you to generate a random sample right in Excel, but first, let’s provide some background about random sampling and why you should use it when you survey.

Random sampling in Excel is a form of probability sampling used to get an unbiased population sample.

You may want to survey your customers to understand how you can better meet their needs. Your whole customer base is a population. It would be nearly impossible to survey every single customer, right? 

Your survey's sampling frame is the group of customers from whom you can contact for the survey. The people who actually fill it out are called your sample. You want to ensure that your sample is randomly selected (hence, a random sample) to guarantee that everyone in your sampling frame has an equal chance of being selected.

A simple random sample is superior to convenience sampling because it ensures your small sample group will represent the total population. Convenience sampling refers to the selection of a sample using a method that is convenient, such as the last 20 people who ordered from you, the last 20 customers listed alphabetically, etc.

All these methods are inherently biased. If you sample the last 20 customers, for example, they may be your newest customers who are only familiar with your most recent products or website design. By generating a random sample, you minimize the bias of picking a convenience sample from your sampling frame.

Random sampling is crucial when analyzing survey data to ensure that the results accurately represent the entire population. By randomly selecting participants, you reduce the risk of bias and increase the likelihood that your sample accurately reflects the broader group's diversity in terms of opinions, demographics, and behaviors.

Now that you understand sampling basics, you'll need tools to create your random sample. Let's look at how to efficiently generate a random sample using Excel's built-in functions.

Creating a random sample in Excel isn’t as complicated as you might think, especially with Connect.

Connect is a hub of business apps and integrations built into SurveyMonkey, allowing you to automate workflows and share data with your team. Using Connect Quick Actions, you can automatically export your survey data into an Excel spreadsheet—manual data entry not required. 

Once you’ve exported your data with Connect, the random number function in Excel, =RAND(), makes generating random numbers for sampling easy. With this method, you can randomly select data from a list in Excel for surveys, market research, or experiments, quickly and simply, ensuring an unbiased sample.

First, enter your sampling frame—the customers you have contact info for—into your spreadsheet. Once you have your sampling frame (potential survey respondents) in Excel, you can easily select a random sample.

Next, add a new column within the spreadsheet and name it Random_number. In the first cell underneath your heading row, type “= RAND().” Press “Enter,” and a random number will appear in the cell. Then, copy and paste the first cell into the other cells in this column.

Once each row contains a random number, sort the records by the Random_number column.

Choose your sample by starting from the top. If you want to sample 500, select the first 500 emails. Those will be the random 500 out of your total sampling frame. 

There are a couple of alternatives to using the =RAND() function, including using RANDBETWEEN() and randomizing an existing list. 

This function generates random integers between two specified values. This is useful when selecting a random sample from specific row numbers or indexed items. For instance, let’s say you have a list of 100 names in cells A2:A101. 

In a new column (say B2), enter:

=INDEX($A$2:$A$101, RANDBETWEEN(1,100))

Copy B2 down for as many random samples as you want (e.g., 10 rows = 10 random names). 

To randomize an existing list and shuffle your entire dataset, go to a new column next to your data (e.g., column B). Then, enter =RAND() and copy it down. Select the entire dataset, including the new column, and sort the data by the RAND() column in ascending or descending order. Then, take the top rows as your random sample. 

Random samples make it easy to get feedback from a large population for any of the following:

Now, you understand how to select a random sample in Excel. The random sampling techniques discussed here are well-suited for data exported from SurveyMonkey. With SurveyMonkey Connect, you can easily export survey responses to Excel for analysis. Integrate your SurveyMonkey account with Excel to unlock advanced analysis capabilities and elevate your research.

Try it today.