*This tutorial will teach you a few quick ways to randomly select names, numbers or any other data. You will also learn how to get a random sample without duplicates and how to randomly select a specified number or percentage of cells, rows or columns in a mouse click.*

Whether you do market research for a new product launch or evaluating the results of your marketing campaign, it is important that you use an unbiased sample of data for your analysis. And the easiest way to achieve this is to get random selection in Excel.

## What is random sample?

Before discussing sampling techniques, let's provide a bit of background information about random selection and when you might want to use it.

In probability theory and statistics, a **random sample** is a subset of data selected from a larger data set, aka *population*. Each element of a random sample is chosen entirely by chance and has an equal probability of being selected. Why would you need one? Basically, to get a non-biased representation of the total population.

For example, you want to conduct a little survey among your customers. Obviously, it would be unwise to send out a questionnaire to each single person in your multi-thousand database. So, whom do your survey? Will that be 100 newest customers, or the first 100 customers listed alphabetically, or 100 people with the shortest names? None of these approaches fit your needs because they are innately biased. To get an impartial sample where everyone carries an equal opportunity of being chosen, do a random selection by using one of the methods described below.

## Excel random selection with formulas

There's no built-in function to randomly pick cells in Excel, but you can use one of the functions to generate random numbers as a workaround. These probably cannot be called simple intuitive formulas, but they do work.

### How to select a random value from a list

Supposing you have a list of names in cells A2:A10 and you want to randomly select one name from the list. This can be done by using one of the following formulas:

`=INDEX($A$2:$A$10,RANDBETWEEN(1,COUNTA($A$2:$A$10)),1)`

or

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

That's it! Your random name picker for Excel is all set up and ready to serve:

Note. Please be aware that RANDBETWEEN is a **volatile** function, meaning it will recalculate with every change you make to the worksheet. As the result, your random selection will also change. To prevent this from happening, you can copy the extracted name and paste it as value to another cell (*Paste Special *> *Values*). For the detailed instructions, please see How to replace formulas with values.

Naturally, these formulas can not only pick random names, but also select random numbers, dates, or any other random cells.

#### How these formulas work

In a nutshell, you use the INDEX function to extract a value from the list based on a random row number returned by RANDBETWEEN.

More specifically, the RANDBETWEEN function generates a random integer between the two values you specify. For the lower value, you supply the number 1. For the upper value, you use either COUNTA or ROWS to get the total row count. As the result, RANDBETWEEN returns a random number between 1 and the total count of rows in your dataset. This number goes to the *row_num* argument of the INDEX function telling it which row to pick. For the *column_num* argument, we use 1 since we want to extract a value from the first column.

Note. This method works well for selecting **one random cell** from a list. If your sample is supposed to include several cells, the above formula may return several occurrences of the same value because the RANDBETWEEN function is not duplicate-free. It is especially the case when you are picking a relatively big sample from a relatively small list. The next example shows how to do random selection in Excel without duplicates.

### How to randomly select in Excel without duplicates

There are a few ways to select random data without duplicates in Excel. Generally, you'd use the RAND function to assign a random number to each cell, and then you pick a few cells by using an Index Rank formula.

With the list of names in cells A2:A16, please follow these steps to extract a few random names:

- Enter the Rand formula in B2, and copy it down the column:

`=RAND()`

- Put the below formula in C2 to extract a random value from column A:
`=INDEX($A$2:$A$16, RANK(B2,$B$2:$B$16), 1)`

- Copy the above formula to as many cells as many random values you want to pick. In our case, we copy the formula to four more cells (C2:C6).

That's it! Five random names are extracted without duplicates:

#### How this formula works

Like in the previous example, you use the INDEX function to extract a value from column A based on a random row coordinate. In this case, it takes two different functions to get it:

- The RAND formula populates column B with random numbers.
- The RANK function returns the rank a random number in the same row. For example, RANK(B2,$B$2:$B$16) in cell C2 gets the rank of the number in B2. When copied to C3, the relative reference B2 changes to B3 and returns the rank of the number in B3, and so on.
- The number returned by RANK is fed to the
*row_num*argument of the INDEX function, so it picks the value from that row. In the*column_num*argument, you supply 1 because you want to extract a value from the first column.

**A word of caution!** As shown in the screenshot above, our Excel random selection contains only unique values. But theoretically, there is a slim chance of duplicates appearing in your sample. Here's why: on a very large dataset, RAND might generate duplicate random numbers, and RANK will return the same rank for those numbers. Personally, I've never got any duplicates during my tests, but in theory, such probability does exist.

If you are looking for a bulletproof formula to get a random selection with only unique values, then use RANK + COUNTIF or RANK.EQ + COUNTIF combination instead of just RANK. For the detailed explanation for the logic, please see Unique ranking in Excel.

The complete formula is a bit cumbersome, but 100% duplicate-free:

`=INDEX($A$2:$A$16, RANK.EQ(B2, $B$2:$B$16) + COUNTIF($B$2:B2, B2) - 1, 1)`

Notes:

- Like RANDBETWEEN, the Excel RAND function also generates new random numbers with each recalculation of your worksheet, causing the random selection to change. To keep your sample unchanged, copy it and paste somewhere else as values (
*Paste**Special*>*Values*). - If the same name (number, date, or any other value) appears more than once in your original data set, a random sample might also contain several occurrences of the same value.

More ways to get a random selection with no repeats in Excel 365 - 2010 are described here: How to get random sample in Excel without duplicates.

### How to select random rows in Excel

In case your worksheet contains more than one column of data, you can select a random sample in this way: assign a random number to each row, sort those numbers, and select the required number of rows. The detailed steps follow below.

- Insert a new column to the right or to the left of your table (column D in this example).
- In the first cell of the inserted column, excluding the column headers, enter the RAND formula:
`=RAND()`

- Double-click the fill handle to copy the formula down the column. As the result, you will have a random number assigned to each row.
- Sort the random numbers
**largest to smallest**(sorting in ascending order would move the column headers at the bottom of the table, so be sure to sort descending). For this, head over to the*Data*tab >*Sort & Filter*group, and click the ZA button. Excel will automatically expand the selection and sort the entire rows in random order.If you are not quite satisfied with how your table has been randomized, hit the sort button again to resort it. For the detailed instructions, please see How to randomly sort in Excel.

- Finally, select the required number of rows for your sample, copy them and paste to wherever you like.

To have a closer look at the formulas discussed in this tutorial, you are welcome to download our sample workbook to Excel Random Selection.

## How to randomly select in Excel with Randomize tool

Now that you know a handful of formulas to get a random sample in Excel, let's see how you can achieve the same result in a mouse click.

With Random Generator for Excel included in our Ultimate Suite, here's what you do:

- 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.
- Click the
**Select**button. Done!

For example, this is how we can select 5 random rows from our sample data set:

And you will get a random selection in a second:

Now, you can press Ctrl + C to copy your random sample, and then press Ctrl + V to paste it to location in the same or another sheet.

If you'd like to test the Randomize tool in your worksheets, just grab a trial version of Ultimate Suite below. In case your are using Google spreadsheets, you may find our Random Generator for Google Sheets useful.

## Available downloads

Selecting random sample - formula examples (.xlsx file)

Ultimate Suite - trial version (.exe file)

## 88 comments

I have used the following formula, which gives me an error.

I am trying to pull random from another worksheet named Key.

=INDEX(Key!$B$28:$B$69,RANK($C$28:$C$69),1)

Also If I am able to make this work& I need it to pull randomly month without repeating, is there something I should add to this? Is there a way to make it start over when all selections have been randomly selected?

Thank you for your help.

Hi! Please take a look at this article: How to get random sample in Excel without duplicates. I hope it’ll be helpful.

How to select element from a list with certain frequency? If I have a list A=(45,12,6,2,1) with frequency F=(2%, 8%, 20%, 35%, 35%) respectively. I want to fill a 100 rows column with the element of the list A respecting the frequency F. How to do it? Thanks in advance

Hi!

I'm really sorry, looks like this is not possible with the standard Excel options.

how to record the output of generated random number

The fastest way is to replace formulas with calculated values. You will find step-by-step instructions in the above linked tutorial.

Hi! How are you am looking for a formular that will pick numbers rondomly in any cell in each row in each column and sum the picked number to give a given set sum. E.g.

A. B. C

2.34. 2.45. 2.56

2.56. 2.78. 2.58

2.45. 2.76. 3.89

I am looking for the formular that will randomly pick the data above which will give me this total 9.01 though this total may differ according to my needs

Hi!

Add to the INDEX formula you see in this article a random column selection.

=INDEX(A2:C15,RANDBETWEEN(1,COUNTA($A$2:$A$15)), RANDBETWEEN(1,COUNTA($A$2:$C$2)))

You can learn more about INDEX in this article: Excel INDEX function with formula examples.

I have a result of a survey, the first column is "ID of each household" and the second column is "number of person in each household". For example, if we have one household with 4 people answering the survey, we would have 4 rows, the first column is the same in all 4 cases and the second columns are 1, 2, 3 and 4. And there are many data in this way in 1 spreadsheet, but I tend to select just 1 person randomly from each household. And I want to do it for all the households.

So helpful! Thank you!

I need to pick a random selection of sports teams each week. However, the requirement is that these teams can't be playing each other on that week. Do you have a solution that meets these requirements?

For example, in any given week, if the games are as follows:

Team A vs Team B

Team C vs Team D

Team E vs Team F

Team G vs Team H

Team I vs Team J

Then a valid random selection of three teams would be:

Team C

Team F

Team H

But an invalid random selection of three teams would be:

Team A

Team B

Team J

Hello,How can I choose two or more values from a list of 10 numbers at random?

Hi,

I hope you have studied the recommendations in the tutorial above. It contains answers to your question. Pay attention to the following paragraph of the article above — How to randomly select in Excel without duplicates

If you need 2 values, use cells C2 and C3.

I hope this will help.

Hi,

i want to get a random sample from a papulation in a column, and if it could the number of this sample be calculated based on the size of the papulation.

thanks.

Hi!

Try using the recommendations from the section above: How to randomly select in Excel without duplicates. To select random values from a column, you can also use the Randomize Tool.

Hello,

I have a list of about 12,000 clients and would like to do a study on a few of them without biased selection. How do I get the random sample and determine the best sample size in Excel. if the selected random numbers would be 'highlighted'. I have tried using RAND() but not helping out as I would like. thanks so much for the help.

Hello!

You did not mention what problem you have with the RAND function.

But, we have a ready-made solution for your task.

I'd recommend you to have a look at our Ablebits Tools - Randomize.

It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

Hi, Thanks for the reply.

RAND keeps recalculating that by the time I paste values, the figures have already changed. am not able to know what percentile to take to avoid bias or have minimal error. I would a function that would help say for 500 clients you can sample out maybe 149, one with 1000 one can sample 253. like define a range for me and thereafter be able to Vlookup those particular IDs. thanks again

I want to assign a list randmoly based on the data of another cell.

on cell has 1 and 0 for yes and no while the other on is a list of

Hello,

I see that the sorted list of random numbers in your screenshot for the option of how to select random rows in a spreadsheet is running into the same problem as I am: numbers are not sorting correctly. I don't understand how Excel is sorting the numbers. Can you help?

Stefan

Hello!

Please note that after sorting, you see completely different numbers. Excel sorts the old numbers first. The RAND function then immediately generates new ones instead. This example uses sorting to cause the RAND function to generate new random numbers.

If you need a correct sort, replace the formulas in the cells with numbers using Paste Special.

Hello!

I need to remove data from random cells in a column. How can I do that?

Hey!

Thanks for the great info.

If I want the selection to be done only is part of the list, how can I modify the formula to specify the different group of cells to look at? (when I just add the different groups, I get a VALOR error)

Thanks!

Hello!

Please describe your problem in more detail. What formula did you want to change? What result did you want to get? It’ll help me understand it better and find a solution for you. Thank you.

I have a list of 50000 coupons and have to alot spefic number of coupons to unique ids, What fromula can i use to map those id's against the spefific no of coupons. For example, qwerty is eligible for 5 coupons and asdfg is eligible for 7 coupons, then how do i map qwerty against 5 coupons and asdfg against 7 coupons.

I have my data organised into rows and want to select one cell randomly from each row. Is there a straightforward way of doing this?

Same problem for me

Hi! To extract from a range of values use the INDEX function .

Suppose you have a range in cells A1:D10, and you want to randomly select one value of each row. Insert a new column to the right of your table (let’s say column E). In the first cell of the inserted column, enter the formula:

=INDEX($A$1:$D$10, ROW(A1), RANDBETWEEN(1, COUNTA(A1:D1)))

The ROW function returns the row number in the range. The RANDBETWEEN function randomly selects the column number.

Double-click the fill handle to copy the formula down the column. Each row will now have a random number assigned to it.

Hi, what if I want to get only 5 random non-duplicate names from A column?

Hello Garry!

If I understand your task correctly, read carefully the section above Random selection without duplicates. If after that there will be specific questions - ask.

Hi Garry,

Just want to let you know that we have published a new tutorial that describes a few easy solutions to randomly select in Excel without duplicates, including the use of the new Excel dynamic array functions: How to get random sample in Excel without duplicates

Hi

could you help me with the formula for the below data

In sheet one, I have some data (example below)

1 - Apple

1 - Orange

2 - banana

2 - Peach

If 1 is input, then a random value from 1 (Apple or Orange) Should be returned. If 2 is input, then a value from 2 list (either banana or peach ) should be returned. If 3 is input, it should return a error.

Hello!

If I understand your task correctly, the following formula should work for you:

=IF(F2=1,INDEX($B$1:$B$2,RANDBETWEEN(1,COUNTA($B$1:$B$2)),1), IF(F2=2,INDEX($B$3:$B$4,RANDBETWEEN(1,COUNTA($B$3:$B$4)),1),"ERROR"))

Hi, thank you for your help.

In case of dynamic values, what should I do i.e the number of values under 1 will keep increasing, say now I have Apple and orange. tomorrow it might be Apple, Orange and Grapes under 1. SO if a match of 1 is found, then it should return a random value(Apple/Orange/grapes). Please help.

Hello!

If I understand your task correctly, the following formula should work for you:

=IF(F3 < 3,INDEX($B$2:$B$21,SMALL(IF($A$2:$A$21=$F$3,ROW($A$2:$A$21)-1,""), ROUNDUP(COUNTIF($A$2:$A$21,$F$3)*RAND(),0))),"ERROR")

The formula selects values from column B for which, by condition, 1 or 2 is written in column A. One of these values is randomly displayed.

The condition is written in cell F3

I hope this will help

hi there

I have a list of 11 names and I need a formula to pick a name randomly from the list of 11 on an ongoing basis to allocate caseload to people without bias. I want to avoid duplicates if possible.

Thank you so much :)

Hello Sharon!

I hope you have studied the recommendations in the above tutorial. Pay attention to the section "Random selection without duplicates". Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. In that case I will try to help you.

This is the formula I'm using to get a random selection

=INDEX($B$2:$B194,RANDBETWEEN(1,COUNTA($B$2:$B$194)),1)

Hello Janet!

Thank you for your reply.

To stop automatic workbook calculation, please try one more method. Please go to File – Options – Formulas – Calculation Options, choose the Manual option and uncheck the box next to "Recalculate workbook before saving". After you do this, the formulas will be recalculated by clicking F9.

To choose random values, you may try to use the following formula:

=INDEX( IFERROR( INDEX($B$2:$B$194, SMALL(IF($C$2:$C$194<>1, ROW(INDIRECT("1:"&ROWS($C$2:$C$194)))), ROW(INDIRECT("1:"&ROWS($C$2:$C$194))))),""), RANDBETWEEN(1,COUNTIF($C$2:$C$194,"<>1")),1)

Suppose, you mark the values that have already been used with number “1” in column C, so they won’t be selected again.

I hope these recommendations will be helpful for you.

Is there a way to stop it from changing the random selection every time anything is edited in the workbook? The formulas work great and I am able to get my random sample, but every time any cell is changed the selection changes. I just want it to generate a list and not change once I have the list. Thanks!

Glad I read comments, I have the exact same question.

Hello Janet! Hello Tim!

If you are using the formula for random selection and can't make the result stay unchanged, there are three possible solutions:

1 - Replace the formulas with values. Just copy the cell(s) with your randomly generated values (CTRL+C), then use Paste Special (CTRL+ALT+V) to insert values.

2 - Disable the automatic calculation in your Excel. Go to File -> Options -> Formulas -> Calculation Options and switch from Automatic to Manual option there.

Note! In this case, you'll need to hit Shift+F9 to recalculate the formulas in any workbook you're working with.

3 - Disable the automatic calculation for the particular sheet. Just press ALT+F11, look through the properties of the sheet you're working with, find EnableCalculation parameter there and set False for it. Thus, all the worksheets will be recalculated automatically while the one you applied these changes for will be updated only when you use the Shift+F9 shortcut.

I hope this will help you. If however, this is not exactly what you meant, please specify and I'll try to find the right solution for you. Thank you.

Thank you! That stopped it from re-selecting each time I work in another cell, but for some reason, the SHIFT+F9 does not work to make a selection. It now returns a "0". The cells I'm trying to select from contain text only. I have a list of local businesses, and I need to choose one each week, till I have gone through all of them. I don't want to choose one twice, before I have chosen all of them. I set one column to place a check mark by once they are chosen, but it recalculates every time I put in my checkmark, and move out of that cell. Also, hopefully I will be adding businesses to my list, in the future. I don't want it to recalculate every time I add one.

Hello Svetlana Cheusheva

I can simply say that i fell in love with your work because i was looking for improve my skills and i found your explanation while searching in google and you provided it some working file too pretty impressive for learns like me and i still found lots of your articles and am definitely go through them you are good at sharing your knowledge thanks for doing that.

Have a wonderful day .

Thank you very much for your feedback, Srinivas! I am glad to hear you've found our tutorials helpful.

I have a list of 8 people to assign to three different jobs where I have ensure that each person gets randomly assign a partner and each person does all three jobs. How would I get a random list of where each of the people are assign a random partner and job task evenly. As an example Joe would Be assigned job 1 with a different person then next week assign either job 2 or 3 with a possible different partner.

Ok...this may come across as a really silly question. The Randomizer picked the % of names I wanted, but now when I try to copy/paste, it's pasting the entire column, not just the selected names. What am I missing here?

Hi Amy,

Right after selecting the desired percentage of names, press Ctrl + C. This should copy only the selected cells.

I need to select random documents based on certain criteria . For eg. there are 10 documents of party x in every month. Now I want to select 2 documents from each month for party x.

Hi guys,

i am wondering what would be the most efficient way to get the data in my case.

I'm working on reports containing 3000-4000 rows. Every single row is containing the data about the order processed by one of the ~ 80 employees. For our quality assurance we would like to analyze 10 orders per employee per month. Ideally would be if the the formula can select 50% of category X and 50% of category Y ( from column D) and then from the product categorization (column E) one A example, one B example and so on...

Any ideas? Can it be done with using formula or do i need to create VBA for that?

Thanks :)

Below some information about the range

A1:A3000 - Order ID

B1:B3000 - Processed by ( agent name)

C1:C3000 - Order summary

D1:D3000 - Order category ( X,Y)

E1:E3000 - Product categorization (1 out of 10 options A,B,C,D,E,F,G,H,I,J)

hi,

I have a list of 8 people and game where two people get paired together against two other people, like volleyball; 2 v 2. How would I get a random list of games where each of the people play with each of the other people one time? For example, Joe would play 7 games; each of the 7 games he would play with a different person. The same goes for the other 7 people. Each of them would play 7 games; each game having a different partner.

Thank you so much for this. We had a giveaway for our podcast and needed a quick way to pick a random person from our sign up list on excel.

Hi,

I liked the way you select for the random value form a row.

Now I would like to extend the example to select a random value within a given range in column values.

For example, a column 21 has the following values in cells,

13, 62, 18,2,12,45,9,16 --> then the selection should a random value from them.

I would like to experiment with Coulumns in the formula.

Hi Svetlana,

This was very helpful. I used the following formula to obtain random numbers from a long list:

=INDEX($A$2:$A$16, RANK.EQ(B2, $B$2:$B$16) + COUNTIF($B$2:B2, B2) - 1, 1)

How can I cite the procedure in a manuscript I am writing?

Thanks!

Celia

Hi Svetlana,

This was very helpful. I used the following formula to obtain random numbers from a long list:

=INDEX($A$2:$A$16, RANK.EQ(B2, $B$2:$B$16) + COUNTIF($B$2:B2, B2) - 1, 1)

How can I cite the procedure in a manuscript I am writing?

Thanks!

Celia

hi Svetlana,

i would like to ask you that how to select entire column from a huge data sheet . ANY quick or short key for it. kindly reply to this message .

Hi Jeeson,

To select an entire column, simply click on its letter.

To select only the cells with data in a column, click on the topmost cell and press Ctrl + Shift + down arrow.

Hi,

I am trying to generate random names from a list using random numbers for 5 teams.

when I am trying to generate, there was repetition of same names in all the teams.

how to remove these repetition ?

Thanks

Hi Venkat,

If by coincidence some of the team members have identical names and you want to avoid any misunderstanding, it may be a good idea to put an index number into each cell where a player’s name is typed. It can easily be done with the help of an extra column containing numbers (from 1 to 10 if we are discussing a team of ten, for example) and the Ablebits ‘Merge Columns into One’ tool. Then I would advise you to opt for ‘Random Generator’ from the Ablebits ‘Randomize’ drop-down menu and make a ‘Custom list’ consisting of names accompanied by index numbers. Please note that the tool needs some empty cells reserved by you to complete the process. There won’t be any duplicates in the selection results if you click a small empty box next to ‘Unique values’ in the ‘Custom list’ menu marking it with a tick.

Hi,

I'm trying to create a randomly generated participant list from the database. I would like to select the few names based on the cities and gender. Do you have any idea how i would do that?

Thanks

Hi,

The selection process may be based on applying Ablebits’ ‘Random Generator’ and filtering. If you are working with a properly organized Excel table which has built-in filters, let them do that part of your task for you. If not, you can use Ablebits’ ‘Filter’ instead.

Svetlana, Thank you for the clear instruction to this random sampling of excel function. I also download the trial version of Ablebits Ultimate Suite and enjoy learning more of add-ins features. By the way, I have one more question to your help. If the sample list with individual probability value specified (overall sum up totally to be 100%) , how to get the random samples that are consistent with the probability distribution? Thanks in advance.

hey, thank you for your tutorials. I just noticed that (using the =RAND() function) the tutorial on this page tells you to sort in DESCENDING order. However, your related tutorial on the =RAND() function tells you to sort in ASCENDING order. Fyi. (Btw, if you make an extra column, give it a header –e.g., "randomized numbers"– and overlay a filter, then it doesn't really matter whether you choose ascending or descending, does it?)

Excellent article, saw it today, used it right away without issues.

Very appreciated!

Thanks!

//Magnus

Hello,

I'm trying to create a randomly generated shopping list from a list of ingredients and the only limiting factor is the overall cost. Do you have any idea how i would do that?

Yes Nikko, I have made an excel sheet with macros built, where you have the limiting factor of samples for starting and ending numbers,

how to generate random number from a Range of column, where the range has numbers and empty cells as well. - Excel

This will not work. I am trying to pick a word from a list. This formula just comes up with NAME?

Hi, Julie,

the NAME error usually means that you accidentally misspelled the name of the function.

Please make sure that everything in your formula is spelled correctly. If it is, please let me know what formula you used.

I'll try to help.

Hi Julie,

To make things easier, you can download our sample workbook with all the formulas discussed in this tutorial and try them on your list.

This was super helpful - thank you for the well written 'how to.'

Sorry, I clicke Send too soon.

Here is the link to the on-line MS help re: "LARGE"

https://support.office.com/en-us/article/large-function-3af0af19-1190-42bb-bb8b-01672ec00a64?NS=EXCEL&Version=90&SysLcid=1033&UiLcid=1033&AppVer=ZXL900&HelpId=xlmain11.chm60373&ui=en-US&rs=en-US&ad=US

Thank you all for this a very good steer indeed.

For some reason I cannot get the "LARGE" function to work where the "n" of its arguments is 9 or more . . . .

Odd Eh!

(Office 385 by the way)

here's how i did on getting random without duplicates:

on b2 type your population or numbers 1,2,3,4,5,etc

on A1 type =rand() drag crosshair down to the number of selection next to b2 down.

on c1 type formula: =LARGE($A$1:$A$9,ROW(A1)) doubleclick crosshair

on D1 type =VLOOKUP(C1,$A$1:$B$9,2,0) doubleclick croshair

press delete on any blank cell to generate new random group

b2 column is your numbers you want ei. bingo numbers 1,2,3,...15

you assign random to each number =rand() on A1 downward for each number

on c1 column type formula to get the largest random number using formula =LARGE($A$1:$A$15,ROW(A1)) and double click crosshair to copy downward

vlookup top five numbers

press delete to generate new top five numbers

Which Excel book would you recommend on

How to use excel to randomize multiple choice questions

Step by Step.

Thanks.

Thanks! This was super helpful, I used it to make a custom Bingo game from a list of phrases.

It was a bit tricky getting it into a grid form without duplicates, but since there were only 5 columns I only had to make some minor manual adjustments.

Hi,

How can we select one random sample from raw data for specific person to another sheet from one sheet where we have multiple sample for each person?

Do you know how to extract a random list of numbers from a list of numbers (2nd column), these numbers belong to different products, it is possible to this extraction of numbers per style?

Thank you

Hi,

Used your formula with the rank.eq and count if. It doesn't work on a large data set. Instead of picking random numbers it takes the first x amount of numbers and puts them in a random order.

Hi,

I have not tried the RANK.EQ + COUNTIF() although I had suspected it might not work because of the set up. However, if you multiply two RAND() functions, you get less likelihood of duplication in a large list.

Like the first example, I have a list of document numbers (700) that I need to randomly select 80 samples from. Can the same formula be applied? When I do the Randbetween, it only selects the first 80, and although they are in random order, it's the first 80 of the 700 and I need it to be 80 throughout. Is it possible to do a random 80 that cover the range I'm looking for? Thanks in advance!

Hi Jose,

Please see the How to randomly select in Excel without duplicates example. Copy the INDEX RANK formula in 80 cells, and you will get a random selection of 80 numbers from your list.

I need your help