Selecting random data points from your spreadsheet is crucial for statistical analysis. We will review two methods to accomplish this. The first uses traditional spreadsheet functions, and the second uses the Random Sampler add-on for Google Sheets.
Contents
Define the Starting Population
We will use a list of grocery store transactions as our starting population. In this context, the word population refers to the data you want to sample. The table contains four columns and 101 rows. Row 1 contains the headers and rows 2 through 101 contain the data.
Get a copy of the Google Sheet with this sample data to save as your own spreadsheet in Google Drive.
Method 1: Sampling with the “Randomize Range” Option
This method utilizes the built-in power of Google Sheets. There are just a few steps, so let’s jump in. Here’s how it works:
Identify Your Population: Decide what data you want to sample. In this case, it will be the one hundred rows of grocery store transactions. Highlight this range except for the header row. The range is A2:D101 in the example sheet.
Choose Randomize Range: Click on Data, then choose Randomize range.
Google Sheets randomly rearranges the rows in the range.
Use the Top x Rows: Now select as many rows as you want from the top of the range.
Copy and paste these rows to use them as your random sample.
This technique is simple and should work on any data set. However, it has its limitations. It leaves the data in a different order and only offers one type of sampling. Let’s look at an add-on next that will leave your data intact and offer other sampling methods.
Method 2: Random Sampler Add-on
For a quicker approach, consider using the Random Sampler add-on. Start Google Sheets then follow these steps:
Install the Add-on: Go to Extensions > Add-ons > Get Add-ons. Search for “Random Sampler” and install it.
Launch the Add-on: Click on the Random Sampler icon in the side panel. You may need to expand your Side panel if you don’t see it.
Define Parameters: A sidebar box will appear. Choose the data range (A1:A101 in our example), check the Has Header box, and select Simple for a statistically random sample. Click Next.
Customize Output: specify the desired sample size (five). You can display the samples on a New worksheet, Add a column with an indicator, or Highlight them in place.
Generate Sample: Click Sample. The add-on will populate a new sheet with your random sample data.
Choosing the Right Method
- Formulas offer more control over the sampling process.
- The add-on provides a user-friendly, one-click approach for basic random sampling.
No matter which method you choose, Google Sheets empowers you to create a random sample for an unbiased look at your data.
Video Tutorial
Watch the video tutorial below about taking a simple random sample.
Other Sampling Techniques
Stratified – Divide a population into groups then sample
Systematic – Select every nth item from a population