Do you want to select a random sample from your Excel data?

A random sample is a selection of records chosen from a larger population in such a way that each record has an equal chance of being chosen.

The goal is to ensure that the sample reflects the population as accurately as possible. If any one group is over-sampled or under-sampled, then it can skew the results and lead to inaccurate conclusions.

Random sampling is essential for statistical work because it helps to eliminate any bias.

This post will show you how to select random samples with and without duplicates. Get your copy of the example workbook used in this post to follow along!

## Select a Random Sample with the RAND Function

This method is the most manual but if you only need one sample, then it’s the easiest to set up and use on a one-off basis.

For this, you will need to add a helper column to your data that contains random numbers.

This can be used to sort the data in random order and the top rows can be taken as your random sample.

Here is a list of data for height and weight measurements. Suppose you want to **get a random sample of 5 observations** from this.

Here is what you need to do.

`= RAND ( )`

- Add a column to the data with the above formula. Copy and paste the formula down the entire column.

The **RAND** function will generate a random decimal number between 0 and 1 in the column. Now you can sort the data based on this random column. Sorting based on a random number will create a random ordering.

- Select your entire range including the new column of random numbers.
- Go to the
**Data**tab. - Select the
**Filter**command. This will add a sort and filter toggle to each of the column headings in your dataset. You can use these to sort your data.

💡 **Tip**: You can also use the `Ctrl` + `Shift` + `L` keyboard shortcut to add the sort and filter toggles to your data.

- Click on the
**sort and filter toggle**in the column of random numbers. This will reveal sorting options for that column - Select the
**Sort Smallest to Largest**option from the menu.

You can also select the **Sort Largest to Smallest** option, it doesn’t really matter as either way it will result in a random order for your data.

The data will appear in a random order and you can copy and paste the top 5 rows of your data and take this as your random sampling.

If you need another random sample, just repeat the process.

When you sort the data again, this will cause the **RAND** function to recalculate and generate new random numbers so you will get a new random order.

## Select a Random Sample with Data Analysis Add-in

There is a statistical analysis add-in available to all versions of Excel that has a sampling feature.

You can enable the data analysis toolpak add-in to use this random sampling.

But be aware, this will only allow you to sample data from a single column of numeric data. It also returns duplicate values in the sample and there is no option to sample without repeating values.

Here’s how to use the **Data Analysis Toolpak** add-in to generate random samples.

- Go to the
**Data**tab. - Click on the
**Data Analysis**button in the**Analysis**section of the ribbon. This will only be available when the add-in is installed.

This will open the **Data Analysis** menu.

- Select the
**Sampling**option from the**Analysis Tools**. - Press the
**Ok**button.

This opens the **Sampling** menu where you can select from a few input and output options.

- Select the
**Input Range**to sample values from. This needs to be a single column of numeric data and you can either include or exclude the column heading label from the selected range. - Check the
**Labels**option if the**Input Range**selected included the column heading label, otherwise leave this unchecked. - Select
**Random**for the**Sampling Method**. - Enter the
**Number of Samples**into the input box. This example will return 5 values in the random sample. - Choose where you want to output the random sample in the Output options section. This example will use the
**Output Range**option and place the results in cell**F3**. - Press the
**OK**button.

When you press **OK**, the sampled values are added to your desired location.

## Select a Random Sample using an Array Formula

A very interesting approach to getting a sample is using a dynamic array formula to return a random.

Dynamic arrays allow you to return more than one value from a single formula, so you could use them to return multiple random rows of your data.

`=FILTER( SORTBY( B3:D17, RANDARRAY(ROWS(B3:D17)) ), SEQUENCE(ROWS(B3:D17)) <= 5)`

The above formula will return 5 random rows **without duplicates** from the range **B3:D17**.

The **ROWS** function determines how many rows are in the source data. In this example, range **B3:D17** has 15 rows.

The **RANDARRAY** function then creates a column of random values between 0 and 1 with the same number of rows as the range **B3:D17**.

The **SORTBY** function then sorts the data range **B3:D17** based on the column generated from the **RANDARRAY** function. Since this is a column of random values, the **SORTBY** function will return a randomly sorted range.

The **FILTER** function is then used on the randomly sorted range to return the top 5 rows as the random sample.

This is achieved by filtering based on the sequence from 1 to the number of rows in the data range.

The filter condition `SEQUENCE(ROWS(B3:D17)) <= 5`

results in a column where the first 5 values are **TRUE**. This cause the **FILTER** function to return the first 5 rows of the randomly sorted range.

This will get you a random sample and you will be able to easily generate new random samples by pressing the `F9` key. This will cause the **RANDARRAY** function to recalculate and new random rows will be returned.

## Select a Random Sample using an Array Formula [with Duplicates]

The previous formula method returns a random sample without any repeated rows, but you might want to allow them.

This is also possible with a dynamic array approach.

`=INDEX( B3:D17, RANDARRAY( 5, 1, 1, ROWS(B3:D17), TRUE ), SEQUENCE( 1, COLUMNS(B3:D17) ))`

The above formula will return a sample **with duplicates** of 5 rows from the range **B3:D17**.

The **ROWS** function is also used here to get the number of rows in the data.

The **RANDARRAY** function is then used to generate 5 rows of random integer values ranging from 1 to the number of rows in the. This is the list of row numbers that will be included in the random sample.

The **RANDARRAY** function allows for duplicate values to be returned, so this will result in the random sample potentially having duplicates.

The **SEQUENCE** function will be used to ensure all the columns of the source data are returned by the **INDEX** function. This generates an array such as `{1,2,3}`

in this example because there are 3 columns in the source data.

The **INDEX** function then returns the given set of rows generated with the **RANDARRAY** function.

This sample can be updated by pressing `F9` on your keyboard to recalculate the formula.

## Select a Random Sample with Power Query

Power Query is a tool baked into Excel meant for importing and transforming your data into the format you need.

This method will use Power Query to get the random samples from the data **with duplicates**.

Suppose your data is in an Excel table, then here are the steps to follow.

- Select a cell inside your table.
- Go to the
**Data**tab. - Select the
**From Table/Range**query command found in the**Get and Transform Data**section of the ribbon.

This will open the Power Query editor.

- Go to the
**Add Column**tab. - Select the
**Custom Column**option in the**General**section.

- Give the new column a name such as
**Random**in the**New column name**field.

`= List.Random(1)`

- Enter the above M code formula in the
**Custom column formula**input. - Press the
**OK**button.

This creates a list with one random value in each row.

- Click on the
**Extract**toggle found in the column heading of the**Random**column. - Select the
**Extract Values**option.

- Select
**None**from the delimiter list in the**Extract values from list**popup menu. Since each list only contains one value, a separating delimiter isn’t needed. - Press the
**OK**button.

This will get a single random value into each row of the column.

- Click on the
**sort and filter**toggle in the Random column. - Select the
**Sort Ascending**option from the menu.

You can also select the **Sort Descending** option and the result will be the same. The order of the data will be randomized.

Now you can filter on the top few rows to get the required sample size.

- Go to the
**Home**tab. - Click on the
**Keep Rows**command. - Select the
**Keep Top Rows**option from the menu.

- Enter the required sample size in the
**Keep Top Rows**dialog box. This example will use 5. - Press the
**OK**button.

Now you can clean up the data by removing the **Random** column. Its job is done.

- Right-click on the
**Random**column heading. - Select
**Remove**from the options.

You can then load the data back into your Excel workbook. Go to the **Home** tab and click on the **Close and Load** command for options to load the data back into a table.

You can then generate another random sample by going to the **Data** tab and selecting the **Refresh** option.

## Select a Random Sample with Power Query [without Duplicates]

This method will use Power Query to get the random samples from the data **without duplicates**.

You will need to load the source data from your table into the Power Query editor as before.

Once the data is in the power query editor, you can follow these steps to get the random samples without repeats.

The first thing to do is add an index column that identifies the row number of the source data.

- Go to the
**Add Column**tab. - Click on the
**Index Column**options menu. - Select
**From 1**.

This creates a column of whole numbers starting at 1 in the first row of the data. This will be used later to join another query that contains a random selection of row numbers.

Now you will need to create a new blank query to randomly select row numbers for the sample.

- Go to the
**Home**tab of the Power Query editor. - Click on
**New Source**. - Choose the
**Other Sources**option. - Choose
**Blank Query**from the submenu options.

This creates a new one where you can build a random selection of row numbers.

`={1..5}`

- Add the above formula into the formula bar of the blank query and press the
`Enter`key. This will create a sequential list of numbers starting at 1 and going up to 5.

Replace the 5 with whatever number of items you want in your sample. Because the sample is with replacement, this number can be higher than the actual number of rows in the source data.

When you create the list, the power query editor will display a **List Tools Transform** tab.

- Click on the
**To Table**command found in the**List Tools Transform**tab.

- Select
**None**for the delimiter in the**To Table**menu. - Press the
**OK**button.

- Go to the
**Add Column**tab. - Select the
**Custom Column**option.

- Give the new column a name such as
**Random**in the**New column name**input.

`Number.RandomBetween(0.5,15.5)`

- Add the above formula to the
**Custom column formula**input. - Press the
**OK**button.

This will create a random number between 0.5 and 15.5 for each row. These are going to be rounded to the nearest integer and that will result in whole numbers ranging from 1 to 15. This range is based on the total number of rows in the source data.

The initial range of 0.5 to 15.5 is needed so that each row number has an equal chance of appearing.

- Select the column of random numbers.
- Go to the
**Transform**tab. - Click on the
**Rounding**option. - Select
**Round**from the menu.

- Enter
**0**for the number of**Decimal Places**. - Press the
**OK**button.

Now you have a random selection of row numbers with duplicates.

This can be merged with the original data source.

- Go to the
**Home**tab. - Select the
**Merge Queries**command.

- Select the
**Random**row number column and the Index column to base the merge on. - Select the original data source with the added index column.
- Select the
**Index**column. - Select
**Left Outer**for the Join Kind. This will return all the rows from the random row query and matching rows from the source data. - Press the
**OK**button.

This creates a column of **Tables**. Each table should contain a single row corresponding to the value in the Random column.

- Click on the
**Expand**toggle in the**Data**column. - Uncheck the
**Use original column name**as prefix option. - Press the
**OK**button.

You can now clean this query and get rid of the columns you don’t need.

- Hold the
`Ctrl`key and select each column to remove. **Right-click**on the column heading.- Select
**Remove Columns**from the options.

Now you can load the results to Excel. Go to the **Home** tab and select **Close and Load** and load the results to a **Table**.

You will be able to generate a new sample at any time by going to the **Data** tab and clicking on the **Refresh** command.

## Conclusions

Random sampling is an important technique in statistics. It’s no doubt you will come across this need if you are doing any statistical analysis with Excel.

Basic helper column techniques and add-ins can be used easily for one-off cases.

But dynamic array formulas or Power Query solutions will be better suited when you require multiple samples from your data.

Did you ever need to randomly select a sample from your data in Excel? How did you get this done? Let me know in the comments below!

## FAQs

### How do I pick 6 random numbers in Excel? ›

**Here are the steps to generate random numbers in Excel without repetition:**

- Select the cells in which you want to get the random numbers.
- In the active cell, enter =RAND()
- Hold the Control key and Press Enter.
- Select all the cell (where you have the result of the RAND function) and convert it to values.

**How do I pick 5 random numbers in Excel? ›**

Click on the cell where you'd like to generate your random number. Enter the formula **=RANDBETWEEN([LowerLimit],[UpperLimit])**. For example, if you'd like to generate a random number between one and 10, you may enter =RANDBETWEEN(1,10). Press the "Enter" key.

**What is the easiest way to select data in Excel? ›**

**Select one or more cells**

**Or use the keyboard to navigate to it and select it**. To select a range, select a cell, then with the left mouse button pressed, drag over the other cells. Or use the Shift + arrow keys to select the range. To select non-adjacent cells and cell ranges, hold Ctrl and select the cells.

**How random is Excel random function? ›**

Excel RAND function - generate random real numbers

**It returns a random decimal number (real number) between 0 and 1**. RAND() is a volatile function, meaning that a new random number is generated every time the worksheet is calculated.

**How do I select a random name from a list in Excel? ›**

One way is to **use the RAND function**. The RAND function will return a random number between 0 and 1. You can use this function to generate a list of random numbers, and then use those numbers to select a random name from a list. Another way to select random names in Excel is to use the RANDBETWEEN function.

**What are 5 random sampling techniques? ›**

There are five types of sampling: **Random, Systematic, Convenience, Cluster, and Stratified**. Random sampling is analogous to putting everyone's name into a hat and drawing out several names. Each element in the population has an equal chance of occuring.

**What are the 4 types of random sampling? ›**

There are four primary, random (probability) sampling methods – **simple random sampling, systematic sampling, stratified sampling, and cluster sampling**.

**What is the best random sampling method? ›**

**Simple random sampling**: One of the best probability sampling techniques that helps in saving time and resources is the Simple Random Sampling method. It is a reliable method of obtaining information where every single member of a population is chosen randomly, merely by chance.

**How do I select a random sample in Excel without duplicates? ›**

Select random rows in Excel without duplicates

Only works in Excel 365 and Excel 2021 that support dynamic arrays. To select random rows with no repeats, build a formula in this way: INDEX(SORTBY(data, RANDARRAY(ROWS(data))), SEQUENCE(n), {1,2,…}) Where n is the sample size and {1,2,…} are column numbers to extract.

**How do I randomly select multiple winners in Excel? ›**

To pull random winners in a drawing using Excel:

In the empty column to the left of your data, assign a random number to each student by using the Random Number formula: “=RAND()” Drag the formula down the column to create random numbers for each participant.

### How do I generate a random number in Excel without repeating? ›

**Generate Random Number List With No Duplicates in Excel**

- Select cell B3 and click on it.
- Insert the formula: =RANDBETWEEN(10,30)
- Press enter.
- Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

**Which method is the most common way used to select the multiple cells? ›**

The most commonly used way to select multiple cells is to **click and drag**. You need to click on a cell and drag it over the spreadsheet. Press on a cell.

**How do I select 10 random cells in Excel? ›**

By placing the RAND() function in a column co-located with your data, you will assign a random number to each row in your data-set or range. Once that is done, all you have to do is sort your data by the RAND() column and then select however many rows you need. It's that simple!

**Is there a formula for random? ›**

If we wish to generate a random number between two numbers, we can use the formula: **RAND() * (b – a) + a**, where a is the smallest number and b is the largest number that we wish to generate a random number for.

**How do you analyze random data in Excel? ›**

Head to the Data tab and click “Data Analysis” in the Analysis section of the ribbon. When the window appears, select “Random Number Generation” and click “OK.” Starting at the top of the window, enter the number of columns you want to fill using the Number of Variables box.

**How do you select random values in a list? ›**

Picking a Random Item/Items

In order to get a random item from a List instance, you need to **generate a random index number and then fetch an item by this generated index number using List.** get() method. The key point here is to remember that you mustn't use an index that exceeds your List's size.

**How can I randomly select an item from a list? ›**

**Using random.** **randrange()** to select random value from a list. random. randrange() method is used to generate a random number in a given range, we can specify the range to be 0 to the length of the list, and get the index, and then the corresponding value.

**What is the best random name picker? ›**

**Class Tools' Random Name Picker** is unquestionably one of the greatest random name pickers available. It is simple to use. To begin spinning the wheel, type in the students' names, hit submit, and then click on the wheel. When the chosen name is presented, the program asks if you want to keep or remove it.

**What are six sampling methods? ›**

**6 Techniques for Market Research Sampling**

- True Random Sampling.
- Systematic Sampling.
- Stratified Sampling.
- Quota Sampling.
- Cluster Sampling.
- Area Sampling.
- Choosing the Right Sampling Technique Your Market Research.

**What are the 5 main types of sampling? ›**

**Five Basic Sampling Methods**

- Simple Random.
- Convenience.
- Systematic.
- Cluster.
- Stratified.

### What are the 4 good sampling methods? ›

**There are four main types of probability sample.**

- Simple random sampling. In a simple random sample, every member of the population has an equal chance of being selected. ...
- Systematic sampling. ...
- Stratified sampling. ...
- Cluster sampling.

**How many types of random are there? ›**

There are **two types** of random variables, i.e. discrete and continuous random variables.

**What is random sampling and its types? ›**

Random sampling is **a method of choosing a sample of observations from a population to make assumptions about the population**. It is also called probability sampling. The counterpart of this sampling is Non-probability sampling or Non-random sampling.

**What are the six types of non-probability sampling? ›**

**The commonly used non-probability sampling methods include the following.**

- Convenience or haphazard sampling. ...
- Volunteer sampling. ...
- Judgement sampling. ...
- Quota sampling. ...
- Snowball or network sampling. ...
- Crowdsourcing. ...
- Web panels. ...
- Advantages and disadvantages of non-probability sampling.

**What is the most common and the easiest method in random sampling? ›**

**Simple Random Sampling**

As you'd guess by the name, this is the most common approach to random sampling. Simple random sampling means simply to put every member of the population into one big group, and then choosing who or what to include at random.

**How do I avoid duplicates in select query? ›**

The **SQL DISTINCT keyword**, which we have already discussed is used in conjunction with the SELECT statement to eliminate all the duplicate records and by fetching only the unique records.

**How do I select without duplicates? ›**

If you want the query to return only unique rows, **use the keyword DISTINCT after SELECT** . DISTINCT can be used to fetch unique rows from one or more columns. You need to list the columns after the DISTINCT keyword.

**How do I randomize multiple choice answers in Excel? ›**

Input the formula

**In the first cell of the column you inserted, type in the RANDBETWEEN formula**, which is a way to select a range of numbers to use in the randomizing process rather than any number. If you want random numbers between one and 15, the formula is "=RANDBETWEEN(1,15).

**How do you pick a random winner with multiple entries? ›**

A quick way to pick random winners for social media giveaways is to **use random number generators**. All you need to do is allocate every entry a number, use a random number generator to select a number, and match that number up with an entry to determine a winner.

**How do I auto generate random numbers in Excel? ›**

If you want to use RAND to generate a random number but don't want the numbers to change every time the cell is calculated, you can **enter =RAND() in the formula bar, and then press F9 to change the formula to a random number**.

### Is it possible to generate a sequence of numbers in Excel automatically? ›

Unlike other Microsoft 365 programs, **Excel does not provide a button to number data automatically**. But, you can easily add sequential numbers to rows of data by dragging the fill handle to fill a column with a series of numbers or by using the ROW function.

**How do I make Randbetween not repeat? ›**

As both RAND and RANDBETWEEN recalculate with every change on the worksheet, your list of random numbers will be continuously changing. To prevent this from happening, **use Paste Special > Values to convert formulas to values** as explained in How to stop random numbers from recalculating. Delete duplicates.

**How do I randomly select participants in Excel? ›**

**How to randomly select in Excel with Randomize tool**

- Select any cell in your table.
- Go to the Ablebits Tools tab > Utilities group, and click Randomize > Select Randomly:
- On the add-in's pane, choose what to select: random rows, random columns or random cells.
- Specify the number or percentage for the desired sample size.

**How do I randomly select a random group in Excel? ›**

**The function RAND () is Excel's random number generator**. To use it, in Column C, type in the following = RAND() in each cell adjacent to every name. Or you can type this function in the top row (row 2) and simply copy and paste to the entire column, or click and drag.

**How do you select random cells in sheets? ›**

**How to Select a Random Sample in Google Sheets**

- Step 1: Create a Dataset. First, we'll enter the values of a dataset into a single column: ...
- Step 2: Create a List of Random Values. Next, type =RAND() into cell B2. ...
- Step 3: Copy & Paste the Random Values. ...
- Step 4: Sort by the Random Values. ...
- Step 5: Select the Random Sample.

**How do I randomly select a word in Excel? ›**

...

**To apply the formula, we need to follow these steps:**

- Select cell B3 and click on it.
- Insert the formula: =CHOOSE(RANDBETWEEN(1, 5), "Mike","John","Lilly","Robert","Jennifer")
- Press enter.

**Can you randomly pick a cell in Excel? ›**

Randomly select data in your Excel file. **Use Select Randomly to get a random selection from a set of your Excel data range**. It's also possible to specify the percentage and number of cells, rows, or columns you want to get from the entire selection.