Comments on: How to select random sample in Excel

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. The easiest way to achieve this is to do random selection in Excel. Continue reading →

Comments page 2. Total comments: 89

  1. 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 :)

    1. 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.

  2. This is the formula I'm using to get a random selection
    =INDEX($B$2:$B194,RANDBETWEEN(1,COUNTA($B$2:$B$194)),1)

    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.

  3. 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!

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

      1. 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.

        1. 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.

  4. 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 .

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

  5. 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.

  6. 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?

    1. Hi Amy,

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

  7. 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.

  8. 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)

  9. 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.

  10. 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.

  11. 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.

  12. 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

  13. 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

  14. 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 .

    1. 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.

  15. 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

    1. 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.

  16. 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

    1. 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.

  17. 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.

  18. 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?)

  19. Excellent article, saw it today, used it right away without issues.
    Very appreciated!
    Thanks!

    //Magnus

  20. 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?

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

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

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

    1. 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.

    2. 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.

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

  25. 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)

  26. 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

    1. 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

  27. Which Excel book would you recommend on
    How to use excel to randomize multiple choice questions
    Step by Step.
    Thanks.

  28. 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.

  29. 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?

  30. 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

  31. 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.

    1. 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.

  32. 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!

  33. I need your help

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)