The RANDARRAY function in Google Sheets generates an array of random numbers between 0 and 1. The size of the array output is determined by the row and column arguments.
Here’s a RANDARRAY formula that generates an array of random numbers between 0 and 1, across 10 rows and 3 columns:
which gives an output:
🔗 The RANDARRAY template is available at the bottom of this article.
RANDARRAY Function Syntax
It takes zero, one, or two arguments.
RANDARRAY(5,5) are all valid formulas.
The first argument is the number of rows to return. This is optional. If only one argument is supplied to the function, it determines the row count.
The second argument is the number of columns to return. This is an optional argument. If omitted, it defaults to a value of 1.
RANDARRAY Function Examples
Firstly, RANDARRAY without any arguments generates a random number in a single cell, equivalent to the RAND function:
RANDARRAY with a single argument generates random numbers in a column, e.g. this formula generates 5 random numbers in a vertical orientation:
To generate a single row of random numbers, set the first argument to 1:
RANDARRRAY with two arguments generates random numbers across rows and columns, e.g. this formula generates random numbers across 10 rows and 5 columns:
RANDARRAY Function Notes
RANDARRAY is a volatile function, which means it recalculates every time your spreadsheet changes.
If you have a lot of RANDARRAY functions in your Sheet (or a lot of downstream formulas that reference a RANDARRAY function), then this can lead to slow Google Sheets because it will keep recalculating all those formulas with every change.
RANDARRAY can also be set to update hourly or even every minute.
Go to File > Settings > Calculation
Select the hour or minute options under the Recalculation dropdown menu:
To generate a single random number, use the RAND function.
To generate a random integer (whole number) between two values, use the RANDBETWEEN function.
Generate Random Whole Numbers With RANDARRAY
Use this array formula to generate an array of whole numbers between 1 and 10, across 10 rows and 5 columns:
Similarly, to get values between 1 and 1000, use this formula:
Finally, to get an array of random whole numbers between two values, e.g. 250 and 700, use this formula:
How To Sort A List Randomly Using A RANDARRAY Formula
RANDARRAY is useful for sorting lists randomly.
You assign each item in the list a random number between 0 and 1 and then sort those numbers in ascending order, which will randomize the order of the items.
For example, suppose there are 10 names in column A.
This formula combines the SORT function and RANDARRAY function to randomize the order:
The output is:
RANDARRAY Function Template
Feel free to make a copy: File > Make a copy…
If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you right-click the link and open it in an Incognito window you’ll be able to see it.
It’s part of the Math family of functions in Google Sheets. You can read about it in the Google Documentation.