I have a Google spreadsheet document with over 1000 data cells in one column. I want to select 5 random samples from this data set, and copy these to a new column. And repeat this process, n number of times. How do I do this?

  • Do you want sampling with replacement or without replacement? That is, can the previously selected values be selected again?
    – user79865
    Commented Dec 24, 2015 at 8:15
  • @Normal Human I think I've figured out with replacement, using VLOOKUP and adding a column for indexes. Then I used RANDBETWEEN to pick search key to be fed into VLOOKUP. If you can give a process for sampling without replacement, I'd appreciate it. Thanks!
    – kchak
    Commented Dec 24, 2015 at 8:27
  • You don't need a column for indices: just use random OFFSET from the top of the list. Without replacement... I'll have to think about it.
    – user79865
    Commented Dec 24, 2015 at 8:33
  • Also, a problem with using randbetween or other random spreadsheet functions is that your samples will be drawn again with every edit to the script. This won't help if you want to analyze the sample.
    – user79865
    Commented Dec 24, 2015 at 8:42
  • Yes, I did observe RANDBETWEEN running every time I made a change to the spreadsheet. I'll have a go at randomizer.org
    – kchak
    Commented Dec 25, 2015 at 4:56

1 Answer 1


To implement sampling without replacement with only spreadsheet functions is not easy. Sampling with replacement is easy enough with randbetween, but if you use it, the samples will change with every edit to the sheet (unless you "stabilize" them by copy-pasting values only, but this is still awkward).

I suggest using an existing solution, such as randomizer.org. It provides a CSV file with random integers in a given range, with or without replacement. The CSV file can be easily imported into Google Sheets: it looks like the table below.

If Randomizer is the name of the sheet with these numbers, and your dataset is in column A of another sheet, then

=offset(A1, Randomizer!A5, 0)

will give the corresponding random element of your list. Copy-paste this command around a 5-by-n range to create the samples.

Note: offset 0 corresponds to the top cell of the data range, so the range of numbers provided to randomizer should begin with 0.

| Research Randomizer Results:  |       |       |
| 20 Sets of 5  Numbers Per Set |       |       |
| Range: From 0 to 999 -- No    |       |       |
| Set 1                         | Set 2 | Set 3 |
| 221                           | 67    | 779   |
| 667                           | 575   | 966   |
| 898                           | 897   | 933   |
| 131                           | 244   | 559   |
| 739                           | 745   | 901   |
  • Thanks. I did see the selected data points changing with every change to the spreadsheet. I didn't think much about it. I'll have a go at randomizer.org
    – kchak
    Commented Dec 25, 2015 at 6:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.