This tutorial presents several methods to insert a column of random numbers into your spreadsheet. Feel free to make a copy of the spreadsheet used in this tutorial to follow along.
Contents
Producing One Column of Decimals
First, let’s start with a column on its own, and we will make it five rows tall.
=RANDARRAY(5)
The RANDARRAY function is the most important in this tutorial. It allows the spreadsheet to write multiple random numbers from one formula. In this case, the formula is only in cell A1
. However, RANDARRAY fills cells A1
through A5
all from the formula in A1
.
If the random decimals are sufficient for your use, you may have finished your formula at this point. However, if you want a more complete solution, let’s make the function control the number of rows.
⚠️ Keep in mind that RANDARRAY is a volatile function. This volatility means that its output changes every time you load the spreadsheet or change the data. If you want the random numbers to stay the same, you need to copy them and then paste them as values.
Matching the Number of Rows in an Existing Table
Now, let’s say you have an existing table and want to insert a random number in every row.
The function needs to count the number of rows to make this work. To perform the count automatically, we add the COUNTA function, which counts the values in the given cell range and returns 6
in this example. Since COUNTA returned a 6
, the RANDARRAY uses this value as its input and returns 6
rows of random numbers.
=RANDARRAY(COUNTA(B2:B7))
Now, we have random decimals for every row. Let’s turn those decimals into integers.
Skip the functions and use the menudriven Random Sampler Google Sheets addon. This userfriendly tool creates random samples without formulas. Choose from simple, systematic, or stratified sampling techniques.
Changing the Random Decimals to Integers
If you want the random numbers to be integers instead of decimals, we need to add a few more functions to the formula.
First, we’ll multiply the result of RANDARRAY(COUNTA(B2:B7))
by 100
to move the random numbers’ decimal points to the right two places.
After multiplying, we have numbers like 35.42382 instead of .3542382. The random number is large enough now, but we must remove the decimals. We can remove the decimals with the INT function, which rounds a number down to the nearest integer. However, we now have another problem. INT does not deal with arrays.
Therefore, you must precede INT with the ARRAYFORMULA function to allow INT to output an array. Don’t worry if that doesn’t make sense. You can copy and paste!
=ARRAYFORMULA(INT(RANDARRAY(COUNTA(B2:B7))*100))
Now, we have a formula that creates six random decimals with the correct number of rows to match your data.
Video Tutorial
Related Tutorials

How to Use Table References in Google Sheets
Learn how to power up your formulas with table references in Google Sheets.

Google Sheets Tables: A Comprehensive Guide
Learn how to use Google Sheets tables to work with your structured data.

Taking a Systematic Sample in Google Sheets – Two Methods
Learn two methods to take a systematic sample.