RANDARRAY function

The RANDARRAY function returns an array of random numbers between 0 and 1.

In the following example, we created an array that's 5 rows tall by 3 columns wide. RANDARRAY is different from the RAND function in that RAND does not return an array, so RAND would need to be copied to the entire range.

RANDARRAY function with a 5 x 3 array: =RANDARRAY(5,3)

Note: September 24, 2018: The RANDARRAY function is one of several beta features, and currently only available to a portion of Office Insiders at this time. We'll continue to optimize these features over the next several months. When they're ready, we'll release them to all Office Insiders, and Office 365 subscribers.

=RANDARRAY([rows],[columns])

Argument

Description

[rows]

Optional

The number of rows to be returned

[columns]

Optional

The number of columns to be returned

Notes: 

  • If you don't input a row or column argument, RANDARRAY will return a single value between 0 and 1.

  • An array can be thought of as a row of values, a column of values, or a combination of rows and columns of values. In the example above, the array for our RANDARRAY formula is range D1:F5.

  • The RANDARRAY function will return an array, which will spill if it's the final result of a formula. This means that Excel will dynamically create the appropriate sized array range when you press ENTER. If your supporting data is in an Excel table, then the array will automatically resize as you add or remove data from your array range if you're using structured references. For more details, see this article on spilled array behavior.

  • Excel has limited support for dynamic arrays between workbooks, and this scenario is only supported when both workbooks are open. If you close the source workbook, any linked dynamic array formulas will return a #REF! error when they are refreshed.

  • You can download a workbook with RANDARRAY function examples.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

FILTER function

SEQUENCE function

SINGLE function

SORT function

SORTBY function

UNIQUE function

#SPILL! errors in Excel

Dynamic arrays and spilled array behavior

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×