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? ›
- 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.
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.
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.
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.
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? ›
- 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.
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.
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? ›- True Random Sampling.
- Systematic Sampling.
- Stratified Sampling.
- Quota Sampling.
- Cluster Sampling.
- Area Sampling.
- Choosing the Right Sampling Technique Your Market Research.
- Simple Random.
- Convenience.
- Systematic.
- Cluster.
- Stratified.
What are the 4 good sampling methods? ›
- 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.
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? ›- Convenience or haphazard sampling. ...
- Volunteer sampling. ...
- Judgement sampling. ...
- Quota sampling. ...
- Snowball or network sampling. ...
- Crowdsourcing. ...
- Web panels. ...
- Advantages and disadvantages of non-probability 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.
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).
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? ›- 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.
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? ›- 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.
...
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.
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.