Sample Selections

For many decades, Excel has shipped with an add-in called the Analysis ToolPak. But, by default it is disabled. When you enable it, you get access to a bunch of cool stuff … including the ability to have Excel make sample selections. I recently received a question from Jo (thanks Jo!) asking about how to do sample selections in Excel. So, in this post, we’ll talk about one option: Sampling with Analysis ToolPak.

Objective

Before we get too far, let’s confirm our objective. Let’s say we have a population of some sort in a workbook. Perhaps you want to sample a population of accounts, customers, invoices, transactions, sales, suppliers, participants, instructors, locations, and so on. For simplicity, I just created a list of IDs that could represent the data you are working with (I just show the first several rows below, but in practice, your list probably contains hundreds or thousands of rows):

From this list, you want Excel to pick a few samples that you can then do something with. Perhaps you want to test each, perhaps you want to invite each to complete a survey, and so on.

The Analysis ToolPak supports two types of sampling: Random and Periodic. A Random sample will retrieve random items from the list and a Periodic sample will retrieve every Nth item (every 3rd, every 5th, etc.).

So, let’s get to it.

Enabling the Add-In

The first thing we need to do is enable the Analysis ToolPak Add-in. To do so, just use File > Options to open the Excel Options dialog, shown below:

Click Add-ins on the left, and then you see Manage: Excel Adds-ins … click Go. This will open the Adds-ins dialog shown below:

Pick Analysis ToolPak from the list and click OK.

Once the add-in is installed, we can begin to use it.

Using the Add-In

Now, you can get access to the Analysis ToolPak features by clicking the Data > Data Analysis command:

When you do, you see the Data Analysis dialog as shown below:

We are trying to pick some samples, so we’ll select the Sampling option and click OK.

This reveals the Sampling dialog:

• First identify the Input Range (our population of IDs).
• Next we pick the Sampling Method (Random will select random samples, which may include the same item multiple times, and Periodic is every Nth item in the list).
• Finally, we specify the desired Output option.

Click OK and bam:

The selected sample items are returned to the specified range, worksheet, or workbook.

More

• There are many more Data Analysis capabilities provided in the AnalysisToolPak, so be sure to look around as they may be helpful.
• Power Query also offers the ability to grab periodic samples (Every Nth item), by using the Remove Rows > Remove Alternate Rows to define the period/pattern. So, if you are comfortable with Power Query this is another option.
• Depending on Excel version, we can also use one of the Excel functions that generates random numbers, such as RANDBETWEEN, RAND, or RANDARRAY. These numbers could potentially be used to represent sample selections.
• If you have any other sampling techniques or uses for the Analysis ToolPak, please share by posting a comment below … thanks!

Sample File: Sampling.xlsx