# How can I easily fill a column in Google Sheets with random values from a list?

I need to create some data for a simulation. So I wanted to take a spreadsheet and, for each column, fill it with random data falling within a domain. A uniform distribution is good enough.

For a binary variable, I am currently doing following steps:

• create a formula like `if(rand()>0.5, "black", "white")` in a new column
• fill a column with it, careful to only do it for the amount of rows I want (not just select the whole column and copy into it)
• copy the results and do `paste special -> values` in the original column

But if I have a variable with seven possible values, I can't think of anything better than seven nested if statements.

Are there better ways?

## 2 Answers

I think this is what you want. A spreadsheet the generates a column of rand() numbers. Then look up the rand() number and return another value.

In the following instruction be sure to keep the \$s for absolute referencing.

1. In cell E2 enter the upper limit of the random numbers (I used 7).
2. In cells C2 to C8 enter the numbers 1 to 7 (since the upper value is 7).
3. In cells D2 to D8 enter the values you want to return. In this case I used names.
4. In cell A2 enter the formula: `=int(RAND()*\$E\$2)+1` (where cell E2 holds the upper limit of the random numbers).
5. Copy this formula down as far as needed.
6. In cell B2 enter the formula: `=vlookup(A2,\$C\$2:\$D\$8,2)` (where cells C2 to D8 hold the substitution values). If the random number generated is 1 then this returns the name Abe.
7. Copy this formula down as far as needed. With a little script you can do that automatically. Here's how.

## Code

``````function myFill(rows, columns, range) {
var output1 = [], l = range.length;
for(var i=0; i<rows; i++) {
var output2 = [];
for(var j=0; j<columns; j++) {
output2.push(range[Math.floor(Math.random() * l)]);
}
output1.push(output2);
}
return output1;
}
``````

## Screenshot ## Example

I've created an example file for you: Create Random Output
Add the script under Tools>Script editor and press the save button.