Statistics How To

Excel Sampling: How to find a Sample in Excel

Statistics Videos > How to use Excel Sampling to find a Sample

Watch the video or read the steps below:

How to use Excel Sampling to find a Sample

If you have a set of data and you know your sample size, you can use Excel’s Data Analysis toolpak to select either a periodic sample or a random sample. A random sample is just that — randomly selected from your data set. A periodic sample (also called a systematic sample) is where Excel chooses the nth data item to include in your sample. For example, if you wanted to choose every 5th number from the following list: 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, Excel would return 8 and 13 (the 5th and 10th numbers in order).

If you don’t know what sample size you need, calculate it before using the Data Analysis tool (How to find a sample size in statistics). The Data Analysis tool can help you extract a sample, but it can’t help you decide on the size. Why? There are many “human” factors that go into selecting a sample size including budget, prior research (you can use a sample size from previous research) and tables constructed from previous research.

How to use Excel Sampling to find a Sample: Steps

Step 1: Enter your data items into Excel. You can enter your data into rows or columns. Ensure the rows and columns are even; for example, enter data into column A to cell 12 and column B to cell 12.

Step 2: Click “Data” and then click “Data Analysis.” If you don’t see Data Analysis on your toolbar, load the Data Analysis Toolpak.

Step 3: Click “Sampling” and then click “OK.”

excel sampling.

Data entered into a worksheet for Excel sampling: the rows and columns are even.

Step 4: Click in the Input Range box and then select your entire data set.

Step 5: Click either “Periodic Sampling” or “Random Sampling.” If you choose periodic, enter the nth number (i.e. every 5) and if you choose random sampling, enter the sample size.

Step 6: Choose an output range. For example, click the “New Worksheet” button and Excel will return the sample in a new worksheet.

Step 7: Click “OK.”

That’s it!

Check out our YouTube channel for more Excel tips and help!

Comments are now closed for this post. Need help or want to post a correction? Please post a comment on our Facebook page and I'll do my best to help!
Excel Sampling: How to find a Sample in Excel was last modified: October 12th, 2017 by Andale

2 thoughts on “Excel Sampling: How to find a Sample in Excel

  1. Andale Post author

    Lets say you have a list of names in column A:
    John
    Jim
    Sue
    etc.
    etc.
    and you wanted to choose items in this list at random. Enter
    =rand() in B1 and copy down. Sort both columns by column B. This will shuffle the
    items in column A. Pick the first five.

    To get a different sample, repeat!