Create a Column of Random Numbers

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.

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.

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