How to create a random sample in Excel in 6 steps

We get a lot of questions around here 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 a bit of background about random sampling and why you should use it when you survey.

You may be asking what a random sample is and why you would need one. Let’s suppose, for example, that you want to survey your customers to understand how you’re meeting their needs and how you could do better.

Your whole customer base is a population. You’re obviously not going to survey every single person in this population. Maybe you don’t have contact information for all of them. Maybe they won’t all reply to your survey.

Whatever the reason, the group of customers that you have the ability to contact with your survey is your sampling frame. And the people who you actually contact with your survey and who actually fill it out are your sample. You want to make sure your sample is randomly selected (hence, a random sample) to ensure that everyone in your sampling frame has an equal chance of being selected.

You don’t want to just select a “convenience sample”, such as the last 20 people who ordered from you or the last 20 customers when they’re listed alphabetically, etc.

All of 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 will minimise the bias of picking a convenience sample from your sampling frame.

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

Although it may sound daunting, you certainly don’t need to be a statistician or mathematician to do this. All you need is an Excel spreadsheet!

The random number function in Excel (=RAND()) makes it easy to generate random numbers for sampling. Using this method, you can randomly select data from a list in Excel for surveys, research or experiments and thus quickly and easily obtain an unbiased sample.

Just put your sampling frame (the customers who you have contact information for) into your spreadsheet. Once you have your sampling frame (potential survey respondents) in Excel, you can easily select a random sample of them.

For example, if you have 3,000 customers and you would like to select a random sample of 500 to receive a customer satisfaction survey, follow these steps:

For more information, check out our blog post about how to find your statistically valid survey sample size. The use of random samples makes it easy to get feedback from a large population for any of the following:

And, once you’ve found your random sample, learn how to create a survey analysis report.