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
Video Tutorial
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, 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.
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, 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 6
random decimals with the correct number of rows to match your data!
Related Tutorials

Conditionally Count Unique Values in Google Sheets
Three techniques to count unique values based on specified criteria.

COUNTUNIQUEIFS Function – Google Sheets
Count unique values based on multiple criteria.

COUNTBLANK Function – Google Sheets
Count the blank cells in a range.