How to select a random sample in Excel

When you have 10,000 contacts but only have resources to reach 500, random selection in Excel is your best tool to ensure your subset is representative of the whole group. By letting probability pick your participants, you eliminate personal bias and ensure your results aren't skewed toward a specific demographic or behavior.

This guide walks through quick Excel setups that use a few formulas and the same good habits survey teams rely on when they choose who to invite, plus how to select a random sample in Excel without extra manual work.

Random sampling in Excel is a way to select a subset of rows using Excel functions so every record has an equal chance of being chosen. It applies the same principles as probability sampling to create an unbiased group you can analyze or contact.

Excel handles this by shuffling your full list, returning the exact number of rows you need, and letting you lock the final output so it won’t change when formulas recalculate. This keeps your sample fair, stable, and aligned with the way survey teams build representative samples.

Random sampling matters because it’s what allows a small group in Excel to represent your full population fairly. It’s the same principle behind probability sampling: every row in your spreadsheet has the same chance of being selected, which keeps your follow-up survey or analysis aligned with the broader group you care about.

In survey terms:

A simple random sample outperforms convenience picks because it avoids patterns that sneak in when you choose based on what’s easiest, like the last 20 orders or the first names alphabetically. Those shortcuts can overrepresent certain people (new customers, early respondents, a specific segment) and skew the findings.

Random selection helps minimize bias so your final dataset is more likely to accurately reflect the diversity of opinions, demographics, and behaviors in the population.

You’re doing this to get trustworthy results, not just a shuffled list. With the basics in place, the next step is seeing how Excel turns those principles into a clean, unbiased sample.

To keep things simple, the guide splits into two paths: newer versions and older versions of Excel. Both paths get you to the same place—a clean, fair list of contacts—so you can choose the Excel path that matches your version.

You can easily export survey responses to Excel with SurveyMonkey Connect, pick the lane that matches your version of Excel, and apply the steps to that exported list.

Modern Excel versions make random selection in Excel much simpler. Dynamic arrays use one formula to shuffle your list and return an exact number of rows without helper columns or manual resorting. This is the main random sampling pattern in Excel when you want a random selection from a list that’s easy to reuse.

You’ll combine three functions:

If your contact table runs from A2:D10000 and you want a random sample of 500 rows, the formula would be:

=TAKE(SORTBY(A2:D10000, RANDARRAY(ROWS(A2:D10000))), 500)

How it works:

This is a straightforward pattern for how to select a random sample in Excel when you want to keep multiple columns together.

If you only need a single field, such as email addresses in A2:A10000, you can use the same structure on that column:

=TAKE(SORTBY(A2:A10000, RANDARRAY(ROWS(A2:A10000))), 500)

The logic is the same: RANDARRAY generates random numbers, SORTBY shuffles the list, and TAKE returns the top 500 records. This covers how to randomly select from a list in Excel when that list is just one column.

Dynamic arrays are volatile, which means RANDARRAY recalculates every time Excel recalculates. A formula edit or refresh somewhere else in the workbook can silently reshuffle your random sample.

Once the sample looks right:

That turns the output into a fixed list, so your sample won’t change the next time Excel recalculates. It’s the last step for a clean Excel random selection from a list that you can use for outreach.

If your contacts came from SurveyMonkey Connect, this method means you’re sampling actual respondents, not just spreadsheet rows, which keeps the Excel step aligned with how the survey data was collected.

Older Excel versions handle random selection with a helper column instead of dynamic arrays. This lane is a simple way to do random sampling in Excel when you don’t have RANDARRAY or SORTBY, and it still gives you a fair shuffle of rows.

You’ll use the RAND() function, which returns a different value between 0 and 1 every time the worksheet recalculates. Because RAND() is volatile, you’ll finish by pasting values to lock your sample.

Start with a table of your full sampling frame:

Add a new column to the right of your table and name it something like Random_number.

In the first data row of Random_number (for example, E2), enter:

=RAND()

Fill that formula down so every row in the table has its own random value. This helper column will drive the shuffle.

Sorting on this column reorders all rows based on their random values. 

Decide how many rows you need in your sample (for example, 500). After sorting:

Because RAND() is volatile, this “paste values to lock” step keeps the sampled rows fixed so your sample won’t change the next time Excel recalculates. In legacy setups, this helper-column pattern is the main random sampling formula in Excel: insert a Random_number column, fill =RAND(), sort by that column, then take the first n rows and paste values to lock.

Before you move on, double-check:

If your data is a CSV or XLS file exported from SurveyMonkey Connect, this lane lets you keep using those files in older Excel environments, while still drawing a fair random sample from your respondent list.

Random sampling in Excel usually assumes that each record appears only once in your sample, especially when each row represents a unique person or account. Methods that shuffle the whole table—like the RANDARRAY + SORTBY lane or the legacy RAND() + sort lane—naturally avoid duplicates, because each row appears once in the original list and once in the shuffled list.

Duplicates are more likely when a formula calls individual rows by position instead of shuffling the whole table. For example, patterns that use RANDBETWEEN to pick row numbers can request the same row more than once. In those cases, a quick duplicate check helps confirm that each ID appears only once in the sample.

If each row has a unique identifier (such as an email address or respondent ID), you can check for repeats in two ways:

Volatile functions like RAND, RANDARRAY, and RANDBETWEEN can also cause “re-rolls.” They recalculate whenever Excel recalculates, which means a sample that looked settled can quietly change after a formula edit or a refresh elsewhere in the workbook. 

Once your random sampling from a list in Excel looks right, copy the sampled area and Paste values into a new sheet or range so the rows stay anchored even while the rest of the workbook updates.

When you’re sampling emails exported from SurveyMonkey, unique respondent IDs and a quick duplicate check help prevent sending multiple invitations to the same person, while still keeping the sample fair.

Before you start any random selection in Excel, it helps to know how many responses you actually need. Instead of guessing or using a one-size-fits-all rule, you can use our Sample Size Calculator to set a target that fits your study.

A statistically sound sample size depends on:

You don’t need a special rule for a list of 10,000 contacts. The right “n” changes based on your confidence level and margin of error. The calculator will usually show that you can get reliable results from a much smaller group than your full list.

Once you’ve picked a statistically sound target, you can move back into Excel, use that number as your sample size, and apply the modern or legacy lane to select exactly that many rows.

Sometimes you just want a quick random pick in Excel without setting up a full shuffle. Two common options are an INDEX + RANDBETWEEN formula and the Analysis ToolPak Sampling feature. These can work as a fast random selector in Excel, but they have some limits.

If you only need one random item from a list, you can use INDEX with RANDBETWEEN. For a list in A2:A101, this formula chooses one row at random:

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

How it works:

This pattern is helpful when you want a single random pick from a list or need to show how to randomly select in Excel for a quick decision.

Trouble starts when you fill that same formula down to get multiple rows. Each copy of RANDBETWEEN is independent, so two cells can request the same row index. That means:

If you need a true sample without repeats, you have two safer options:

In other words, INDEX + RANDBETWEEN is fine for a quick random sample generator in Excel when you don’t mind the chance of repeats, but it needs extra checks for survey samples.

Some Excel versions include a built-in Sampling tool in the Analysis ToolPak, which can act as a basic way to randomize data in Excel.

To turn it on:

From there, you can:

The ToolPak Sampling dialog is useful for quick, ad-hoc experiments, like testing a few rows from a training file or creating a small demo subset. For ongoing survey programs, formulas usually work better because they:

You can treat the ToolPak as a convenient random selector in Excel for occasional use, then rely on the formula-based lanes when you need repeatable, documented sampling for survey work.

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

Random sampling in Excel is much easier once you match your setup to the right method. Modern Excel uses RANDARRAY, SORTBY, and TAKE to return an exact number of rows with one formula, while legacy workbooks rely on a helper RAND() column and a sort. In both lanes, quick duplicate checks and pasting values to lock the results help keep your sample fair and stable.

From there, the workflow is simple: export survey responses to Excel, use the method that fits your version of Excel, and send your next survey to that sampled list. Choose a statistically sound sample size based on your goals, then use templates and integrations to turn that sample into a repeatable research program.

NPS, Net Promoter & Net Promoter Score are registered trademarks of Satmetrix Systems, Inc., Bain & Company and Fred Reichheld.