How to Use the RANDARRAY Function in Google Sheets

The RANDARRAY function in Google Sheets is the array-based version of the popular RAND function. Both are volatile functions and are categorized under the Math function family in Google Sheets.

While the RAND function generates a single random number between 0 and 1, the RANDARRAY function outputs a dynamic array of random numbers—filling multiple rows and columns with values between 0 and 1.

Unlike in Microsoft Excel, where RANDARRAY allows specifying minimum and maximum values (like RANDBETWEEN), the RANDARRAY function in Google Sheets only supports the row and column arguments. More on that below.

Table of Contents

What Is a Volatile Function?

Volatile functions recalculate every time the spreadsheet is edited or reopened. This means the results of the RANDARRAY function will change with each action taken in the sheet—even if unrelated to the formula.

Syntax of RANDARRAY in Google Sheets

RANDARRAY(rows, columns)

rows – The number of rows to return.
columns – The number of columns to return.

Example:

=RANDARRAY(5, 3)

This formula generates a 5-row by 3-column array of random numbers between 0 and 1.

Example showing how to use the RANDARRAY function in Google Sheets to generate random numbers in multiple rows and columns

Practical Uses of the RANDARRAY Function in Google Sheets

The RANDARRAY function in Google Sheets is useful in scenarios that require randomization. Here are a couple of real-world examples:

1. Randomize a Data Range

Imagine you have a list of names and ages in cells A2:B11. You can shuffle this range randomly using:

={"Name", "Age"; SORT(A2:B11, RANDARRAY(10, 1), TRUE)}

Using the RANDARRAY function in Google Sheets to randomize employee data

Explanation:

RANDARRAY(10, 1) creates a helper column with 10 random numbers (for each row in A2:B11).
This random column is used to sort the original data.
SORT then reorders the rows based on these values.
The header row is manually added using array notation.

2. Extract ‘n’ Random Rows from a Range

You can combine RANDARRAY, SORT, and ARRAY_CONSTRAIN to extract a specific number of random rows:

=ARRAY_CONSTRAIN({"Name","Age"; SORT(A2:B11, RANDARRAY(10, 1), TRUE)}, 6, 2)

This formula returns 5 random rows plus the header (6 rows total, 2 columns wide).

Comparison: RANDARRAY vs RAND vs RANDBETWEEN in Google Sheets

Function	Description
`RAND()`	Returns a single random decimal between 0 and 1.
`RANDARRAY()`	Returns an array of random decimals between 0 and 1.
`RANDBETWEEN()`	Returns a random integer between specified low and high values.

Although RANDBETWEEN can’t generate arrays directly, you can simulate it using ARRAYFORMULA and SEQUENCE:

=ARRAYFORMULA(RANDBETWEEN(1, ROW(A1:A5)))
=ARRAYFORMULA(RANDBETWEEN(1, SEQUENCE(5)))

Note: These can result in duplicate values, making them less ideal for tasks like shuffling or sampling without repetition.

A Note About Excel

In Excel, the RANDARRAY function allows specifying minimum and maximum values, and whether you want decimals or integers:

=RANDARRAY(10, 1, 1, 100)

This would generate 10 random numbers between 1 and 100 in a single column—but this feature is not supported in Google Sheets.