How to select random sample in Excel

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:
Selecting a random name in Excel

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:

  1. Enter the Rand formula in B2, and copy it down the column:
    =RAND()
  2. 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)
  3. 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:
Random selection in Excel 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 into the row_num argument of the INDEX function. 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.EQ + COUNTIF instead of RANK. 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)

Formula to get a random sample in Excel without duplicates

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.

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.

  1. Insert a new column to the right or to the left of your table (column D in this example).
  2. In the first cell of the inserted column, excluding the column headers, enter the RAND formula: =RAND()
  3. 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.
  4. 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.
    Sort the entire rows in random order.

  5. Finally, select the required number of rows for your sample, copy them and paste to wherever you like.
    Select random rows in Excel.

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 our Ultimate Suite installed in your Excel, here's what you do:

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

For example, this is how we can select 5 random rows from our sample data set:
Selecting a number or percentage of random rows in Excel.

And you will get a random selection in a second:
Random selection in Excel without duplicates

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, feel free to download a 14-day trial version of the Ultimate Suite for Excel. If the tool meets your needs and you decide to get a license, don't miss the 15% off coupon code that we provide especially for our blog readers: AB14-BlogSpo

I thank you for reading and hope to see you on our blog next week!

You may also be interested in:

32 Responses to "How to select random sample in Excel"

  1. sunilkumar says:

    I need your help

  2. Jose says:

    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!

  3. Job says:

    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.

    • Oken says:

      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.

  4. Gabriel says:

    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

  5. vikas says:

    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?

  6. Patrick says:

    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.

  7. ROY THOMAS says:

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

  8. joojep says:

    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

    • joojep says:

      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

  9. Plymouth Sid says:

    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)

  10. JULIE HASH says:

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

  11. Julie says:

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

  12. Rehan says:

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

  13. Nikko Weging says:

    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?

    • sandeep says:

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

  14. Magnus Holmer says:

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

    //Magnus

  15. Claudia says:

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

  16. Richard Lee says:

    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.

  17. Bhupendra Singh says:

    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

    • Aksana (Ablebits.com Team) says:

      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.

  18. Venkat says:

    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

    • Aksana (Ablebits.com Team) says:

      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.

  19. jeeson joshy says:

    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 .

  20. Celia says:

    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

  21. Celia says:

    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

  22. Madhu says:

    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.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
60+ professional tools for Excel
60+ professional tools for Excel
2019–2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard