How to Do a Random Sample in Excel: RAND, RANDBETWEEN and Sort

To do a random sample in Excel, number every unit in your list, put a RAND() value beside each row, sort the list on that column and keep the first n rows. That draws a simple random sample without replacement. RANDBETWEEN is quicker for a single pick but can return the same number twice, so it draws with replacement unless you check for repeats.

The part most tutorials skip is what makes the result a defensible sample rather than a shuffled spreadsheet: a complete list to draw from, a record of what was drawn, and values frozen so the selection does not change when the workbook recalculates. This guide covers all three. The Excel behaviour below follows Microsoft Support’s function pages, checked on 2026-10-29.

What it is used for in research

Random sampling gives every unit in the list the same chance of selection, which is the property that lets you generalise from the sample to the list. Typical uses are picking participants from a roster, choosing records for a chart audit or selecting survey respondents from a mailing list. For the theory, see the CASRAI guide to simple random sampling. If you need every k-th unit instead, see systematic sampling, which also uses a random starting point.

Excel can only sample from the list you give it. If that list misses part of your target population, no formula fixes it; see sampling frame and coverage error. Excel also cannot decide how many units you need; that is a design question covered in what is a good sample size for a quantitative study.

Install or access (official source only)

Nothing needs installing beyond Excel itself. The functions used here, RAND and RANDBETWEEN, are built in. The one-formula method uses RANDARRAY and SORTBY. Microsoft lists SORTBY as available in Excel for Microsoft 365, Excel 2024 and Excel 2021, so it is not available in older versions. In those versions use the helper-column method below.

Step-by-step: draw a simple random sample

Method 1: RAND helper column and sort (without replacement)

1. Put your complete list in one sheet, one unit per row, with a header row. This list is your sampling frame. Check it is complete and has no duplicate units before you start.
2. In the first empty column beside the list, type =RAND() in the first data row and fill it down to the last row. RAND takes no arguments and returns a random real number from 0 up to but not including 1. Each row now has its own random number.
3. Freeze the numbers before sorting. Microsoft’s guidance for keeping a RAND result fixed is to enter the formula in the formula bar and press F9, which replaces the formula with its value. Do this for each cell, or copy the column and paste it back as values. If you skip this step, the numbers change whenever the worksheet recalculates, including each time you sort, and your selection moves.
4. Click one cell in the random-number column. On the Data tab, in the Sort & Filter group, choose Sort Smallest to Largest.
5. Take the first n rows, where n is your planned sample size. Copy them to a new sheet and keep the original sheet unchanged.

Every unit has an equal chance of landing in the top n, and a unit cannot appear twice, so this is sampling without replacement. That is the design most research protocols mean by a simple random sample.

Method 2: RANDBETWEEN to pick row numbers

1. Add a column that numbers the units 1 to N, where N is the size of your list.
2. In another cell enter =RANDBETWEEN(1,N), replacing N with your count (for example =RANDBETWEEN(1,500)). The syntax is RANDBETWEEN(bottom, top): bottom is the smallest integer it will return and top is the largest.
3. Fill the formula down for as many picks as you need, then freeze the results as in step 3 above.
4. Look up each number in your numbered list.

Each call is independent, so the same number can come up more than once. If you need distinct units, check the picks for repeats and redraw any duplicates, or use Method 1, which cannot repeat a row.

Method 3: one formula in current Excel

If you have a version with dynamic arrays, a single formula returns a shuffled copy of your list. With 100 units in A2:A101:

=SORTBY(A2:A101,RANDARRAY(100))

SORTBY(array, by_array1, [sort_order1], ...) sorts one range by another, and the sorting range must be a single row or column of the same size as the array. RANDARRAY supplies one random decimal per row, and the result spills into the cells below. The first n spilled rows are your sample. RANDARRAY also takes optional minimum, maximum and whole-number arguments, and its minimum must be less than its maximum or it returns a #VALUE! error.

Output and how to read it

Whichever method you use, the output is a list of selected units. What you report matters as much as the list:

Item to record	Why
The sampling frame (source and date)	Defines the population the sample can speak for
The number of units in the frame (N) and the sample size (n)	Lets a reader judge precision
The method (RAND and sort, or RANDBETWEEN) and the Excel version	Shows whether repeats were possible
The date of the draw and a saved copy of the frozen values	Excel gives no seed to re-run, so the saved values are the only way to reproduce the selection

A related point that is often confused: random sampling chooses who is in the study, while random assignment allocates those participants to groups. Excel can do either, but they answer different design questions; see random assignment vs random sampling.

Troubleshooting

Why does my sample keep changing?

Both RAND and RANDBETWEEN return a new value every time the worksheet is calculated, and editing any cell or pressing F9 triggers a recalculation. Freeze the values as described in Method 1, step 3.

Why do I get the same unit twice?

You are using RANDBETWEEN, which can repeat. Switch to the RAND-and-sort method or remove and redraw the duplicates.

Why does SORTBY return #VALUE!?

Microsoft documents #VALUE! when a sort-order argument is not 1 or -1, and the sorting range must be a single row or column matching the size of the array. Check that the RANDARRAY row count equals the number of rows in your range.

SORTBY is not recognised

The function is available in Excel for Microsoft 365, Excel 2024 and Excel 2021 only. Use Method 1 in older versions.

Free alternatives

Tool	How it draws random values	Source
Google Sheets	RAND() returns a number from 0 inclusive to 1 exclusive, so the same RAND-and-sort method works	Google Docs Editors Help, RAND
LibreOffice Calc	RAND() returns a number between 0 and 1 and recalculates each time; to keep values fixed, copy and use Paste Special with only Numbers marked, or use Sheet menu, Fill Cells, Fill Random Numbers	LibreOffice Help, RAND
R	sample(x, size, replace = FALSE) draws without replacement by default; replace = TRUE samples with replacement	R documentation, sample

FAQ

How do I do a random sample in Excel?

Number your list, add a =RAND() column, freeze it, sort on it with Data tab, then Sort Smallest to Largest and take the first n rows. In Excel 365, 2024 or 2021 you can instead use =SORTBY(range, RANDARRAY(rows)) and take the first n rows of the result.

Is RANDBETWEEN or RAND better for sampling?

For a sample of distinct units, RAND with a sort is safer because a row cannot be chosen twice. RANDBETWEEN suits single picks or sampling with replacement.

Can I make the same random sample again?

Not by re-running the formulas, because Excel recalculates to new values. Save the frozen values, or the selected rows, with the date of the draw.

Sources

• Microsoft Support, RAND function (checked 2026-10-29)
• Microsoft Support, RANDBETWEEN function (checked 2026-10-29)
• Microsoft Support, RANDARRAY function (checked 2026-10-29)
• Microsoft Support, SORTBY function (checked 2026-10-29)
• Microsoft Support, Sort data in a range or table (checked 2026-10-29)
• Google Docs Editors Help, RAND (checked 2026-10-29)
• LibreOffice Help, RAND (checked 2026-10-29)
• R Documentation, sample (checked 2026-10-29)