How to use the RANDARRAY function

Author: Oscar Cronquist Article last updated on January 26, 2023

RANDARRAY function

The RANDARRAY function returns a table of random numbers across rows and columns. It is located in the Math and Trigonometry category and is only available to Excel 365 subscribers.

What's on this page

RANDARRAY Function Syntax
RANDARRAY Function Arguments
RANDARRAY Function example
What is a spilled array formula?
Why does the RANDARRAY function return a #SPILL! error?
The RANDARRAY function returns a #NAME! error?
How to create a list of unique distinct numbers sorted in random order
How to shuffle characters in a text string randomly
How to create a random text string
Get Excel file

Webpages containing the RANDARRAY function

Sort in random order

1. RANDARRAY Function Syntax

RANDARRAY([rows], [columns], [min], [max], [whole_number])

2. RANDARRAY Function Arguments

Arguments	Text
[rows]	Optional. Number of rows., 1 is the default value.
[columns]	Optional. Number of columns, 1 is the default value.
[min]	Optional. The smallest number.
[max]	Optional. The largest number.
[whole_number]	Optional. TRUE - whole number. FALSE - decimal number.

3. RANDARRAY Function example

RANDARRAY function
Formula in cell B3:

=RANDARRAY(D9,D10,D11,D12,D13)

The formula demonstrated in the image above returns a table of four rows and three columns populated with random numbers from 1 to 100. The formula returns new values every time you press F9.

4. What is a spilled array formula?

randarray function spilled array formula

Excel 365 automatically expands the output range based on the number of values in the array, without requiring the user to enter the formula as an array formula.

This new behavior of Excel is called spilled array formula and is something only dynamic array formulas can do. Dynamic array formulas are only available to Excel 365 subscribers.

5. Why does the RANDARRAY function return a #SPILL! error?

randarray function spill error

#SPILL! error is returned by the RANDARRAY function if the required cell range is populated by any other value. You have two options:

Remove value leaving the cell blank.
Enter the dynamic formula in another cell that has empty adjacent cells.

6. Why does the RANDARRAY function return a #NAME! error?

RANDARRAY function name error

If a cell returns #NAME! error you have either misspelled the function name or you are using an older incompatible Excel version.

The image above shows that I misspelled the RANDARRAY function in the formula bar, cell F3 displays #NAME! error.

Only Excel 365 subscription version supports the new dynamic array formula like the RANDARRAY function, older Excel versions like Excel 2019, 2016, 2013, 2010, 2007, and earlier versions do not support the RANDARRAY function.

Here is how to find out your Excel version: Get your Excel version

7. How to create a list of unique distinct numbers sorted in random order

RANDARRAY function unique distinct numbers in random order

The image above shows a formula in cell B3 that returns numbers from one to ten in random order.

Formula in cell D3:

=SORTBY(SEQUENCE(10),RANDARRAY(10))

Explaining formula in cell D3

Step 1 - Create an array of random no between 0 (zero) and 1

RANDARRAY(10)

returns

{0.00631985584666483; 0.846522787602261; 0.817086790647678; 0.0907043159731944; 0.831202119264657; 0.218031433770172; 0.505530171355597; 0.80026556794421; 0.853884408869484; 0.487721931499081}

Step 2 - Create an array of numbers between 1 and 10

The SEQUENCE function returns a sequence of numbers. SEQUENCE(rows, [columns], [start], [step])

SEQUENCE(10)

returns

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10}

Step 3 - Sort sequence based on random numbers

The SORTBY function sorts the sequence of numbers based on the array of random numbers. SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

SORTBY(SEQUENCE(10),RANDARRAY(10))

becomes

SORTBY({1; 2; 3; 4; 5; 6; 7; 8; 9; 10}, {0.00631985584666483; 0.846522787602261; 0.817086790647678; 0.0907043159731944; 0.831202119264657; 0.218031433770172; 0.505530171355597; 0.80026556794421; 0.853884408869484; 0.487721931499081})

and returns

{8; 5; 3; 7; 10; 9; 1; 2; 6; 4}.

8. How to shuffle characters in a text string randomly

RANDARRAY function random alphabet

The image above shows a formula in cell B6 that returns carachters in random order based on the original text string in cell B3.

Formula in cell D3:

=TEXTJOIN(, , SORTBY(MID(B3, SEQUENCE(LEN(B3)), 1), RANDARRAY(LEN(B3))))

Explaining formula in cell D3

Step 1 - Count rows in cell range

The LEN function counts the number of characters in a string.

LEN(B3)

Step 2 - Create sequence based on the number of characters in B3

The SEQUENCE function returns a sequence of numbers. SEQUENCE(rows, [columns], [start], [step])

SEQUENCE(LEN(B3))

becomes

SEQUENCE(26)

and returns

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26}

Step 3 - Split text string into characters

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract. MID(text, start_num, num_chars)

MID(B3, SEQUENCE(LEN(B3)), 1)

becomes

MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26}, 1)

and returns

{"A"; "B"; "C"; "D"; "E"; "F"; "G"; "H"; "I"; "J"; "K"; "L"; "M"; "N"; "O"; "P"; "Q"; "R"; "S"; "T"; "U"; "V"; "W"; "X"; "Y"; "Z"}

Step 4 - Create an array of random numbers

RANDARRAY(LEN(B3))

becomes

RANDARRAY(26)

and returns an array of 26 random decimal numbers.

Step 5 - Sort array of characters based on random numbers

The SORTBY function sorts the characters based on the array of random numbers. SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

SORTBY(MID(B3, SEQUENCE(LEN(B3)), 1), RANDARRAY(LEN(B3)))

becomes

SORTBY({"A"; "B"; "C"; "D"; "E"; "F"; "G"; "H"; "I"; "J"; "K"; "L"; "M"; "N"; "O"; "P"; "Q"; "R"; "S"; "T"; "U"; "V"; "W"; "X"; "Y"; "Z"}, RANDARRAY(LEN(B3)))

and returns

{"T"; "H"; "L"; "R"; "F"; "X"; "O"; "V"; "K"; "S"; "G"; "D"; "B"; "Q"; "U"; "J"; "P"; "C"; "I"; "E"; "Z"; "Y"; "A"; "W"; "M"; "N"}

Step 6 - Join characters in array

The TEXTJOIN function concatenates the characters and returns a text string.

TEXTJOIN(, , SORTBY(MID(B3, SEQUENCE(LEN(B3)), 1), RANDARRAY(LEN(B3))))

becomes

TEXTJOIN(, , {"T"; "H"; "L"; "R"; "F"; "X"; "O"; "V"; "K"; "S"; "G"; "D"; "B"; "Q"; "U"; "J"; "P"; "C"; "I"; "E"; "Z"; "Y"; "A"; "W"; "M"; "N"})

and returns THLRFXOVKSGDBQUJPCIEZYAWMN.

9. How to create a random text string

RANDARRAY function random text string

The image above shows a formula that creates characters from A to Z with a total string length of 5 to 15 characters. Press F9 to recalculate the formula.

Formula in cell B6:

=TEXTJOIN(, , CHAR(RANDARRAY(RANDBETWEEN(5, 15), , 65, 90,TRUE)))

Explaining formula in cell D3

Step 1 - Create a random number between 5 and 15

The RANDBETWEEN function returns a random whole number between the numbers you specify. RANDBETWEEN(bottom, top)

RANDBETWEEN(5, 15)

For some reason I don't know the RANDARRAY function can' be used in the first argument in another RANDARRAY function, in other words, a nested RANDARRAY function does not work.

Step 2 - Create an array of whole numbers between

The RANDARRAY function returns an array of whole numbers between 65 and 90. The size of the array is determined by the output from the RANDBETWEEN function.

RANDARRAY(RANDBETWEEN(5, 15), , 65, 90,TRUE)

returns

{89; 69; 73; 66; 72; 89; 72; 89; 70; 81; 85; 65; 76; 68}

Step 3 - Convert numbers to letters

The CHAR function converts a number to the corresponding character.

CHAR(RANDARRAY(RANDBETWEEN(5, 15), , 65, 90,TRUE))

becomes

CHAR({89; 69; 73; 66; 72; 89; 72; 89; 70; 81; 85; 65; 76; 68})

and returns

{"Y";"E";"I";"B";"H";"Y";"H";"Y";"F";"Q";"U";"A";"L";"D"}

Step 6 - Join characters in array

The TEXTJOIN function concatenates the characters and returns a text string.

TEXTJOIN(, , CHAR(RANDARRAY(RANDBETWEEN(5, 15), , 65, 90,TRUE)))

becomes

TEXTJOIN(, , {"Y";"E";"I";"B";"H";"Y";"H";"Y";"F";"Q";"U";"A";"L";"D"})

and returns YEIBHYHYFQUALD.

Get the Excel file

SEQUENCE-function.xlsx

'RANDARRAY' function examples

The following article has a formula that contains the RANDARRAY function.

Working with classic ciphers in Excel
What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]

More examples

Functions in this article

RANDARRAY SORTBY SEQUENCE TEXTJOIN MID LEN CHAR RANDBETWEEN TRUE

Functions in 'Math and trigonometry' category

The RANDARRAY function function is one of many functions in the 'Math and trigonometry' category.

ABS function

Converts negative numbers to positive numbers.

ACOS function

Calculates the arccosine, or inverse cosine, of a number.

ACOSH function

Calculates the inverse hyperbolic cosine of a number.

ACOT function

Calculates the inverse cotangent of a number.

ACOTH function

Calculates the inverse hyperbolic cotangent of a number.

AGGREGATE function

Perform different specific functions to a list or database.

ASIN function

Calculates the arcsine of a number.

ASINH function

Calculates the inverse hyperbolic sine of a number.

ATAN function

Calculates the arctangent of a number.

ATAN2 function

Calculates the arctangent of an angle using specific x- and y-coordinates.

ATANH function

Calculates the inverse hyperbolic tangent of a number.

BASE function

Converts a number into a text representation with a given radix (base).

CEILING function

Rounds a number up to its nearest multiple.

COMBIN function

Returns the number of combinations for a specific number of elements out of a larger group.

COMBINA function

Calculates the number of combinations for a given number of elements from a larger group of elements.

COS function

Calculates the cosine of an angle.

COSH function

Calculates the hyperbolic cosine of a number.

COT function

Calculates the cotangent of an angle specified in radians.

COTH function

Calculates the hyperbolic cotangent of a hyperbolic angle.

CSC function

Calculates the cosecant of an angle (radians).

DECIMAL function

Converts a text representation of a number in a given base into a decimal number.

DEGREES function

Calculates degrees from radians.

EVEN function

Rounds a number up to the nearest even whole number.

EXP function

Returns e raised to the power of a number, e equals 2.71828182845904.

FACT function

Returns the factorial of a number.

FACTDOUBLE function

Returns the double factorial of a number.

FIXED function

Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.

FLOOR.MATH function

Rounds a number down to the nearest integer or to the nearest multiple of significance.

FLOOR.PRECISE function

Rounds a number down to the nearest integer or nearest multiple of significance.

GCD function

Calculates the greatest common divisor that divides all given arguments without a remainder.

INT function

Removes the decimal part from positive numbers and returns the whole number (integer) except negative values are rounded down to the nearest integer.

LCM function

Calculates the least common multiple. The least common multiple is the smallest positive integer that is a multiple of all integer arguments. Use the LCM function to find fractions with different denominators.

LET function

Lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

LN function

Calculates the natural logarithm of a number. Natural logarithms are based on the constant e.

LOG function

Calculates the logarithm of a number to a specific base.

LOG10 function

Calculates the logarithm of a number using the base 10.

MINVERSE function

Calculates the inverse matrix for a given array.

MMULT function

Calculates the matrix product of two arrays, an array as the same number of rows as array1 and columns as array2.

MOD function

Returns the remainder after a number is divided by divisor.

MROUND function

Calculates a number rounded to a given multiple.

MULTINOMIAL function

Calculates the ratio of the factorial of a sum of values to the product of factorials.

MUNIT function

Calculates the identity matrix for a given dimension

PI function

Returns the number pi (¶).

POWER function

Calculates a number raised to a power.

PRODUCT function

Returns the product of the numbers given in the argument.

QUOTIENT function

Returns the integer portion of a division.

RADIANS function

Converts degrees to radians.

RAND function

Calculates a random real number greater than or equal to 0 and less than 1.

RANDARRAY function

Creates an array of random numbers

RANDBETWEEN function

Returns a random whole number between the numbers you specify.

ROUND function

Rounds a number based on the number of digits you specify.

ROUNDDOWN function

Rounds a number down based on the number of digits to which you want to round the number.

ROUNDUP function

Calculates a number rounded up based on the number of digits to which you want to round the number.

SEC function

Calculates the secant of an angle.

SECH function

Calculates the hyperbolic secant of an angle.

SEQUENCE function

Creates a list of sequential numbers.

SERIESSUM function

Calculates the sum of a power series based on a formula.

SIGN function

Returns the sign of a number. 1 for a positiv number, 0 (zero) for a 0 (zero) and -1 for a negative number.

SIN function

Calculates the sine of an angle.

SINH function

Calculates the hyperbolic sine of a number.

SQRT function

Calculates the positive square root.

SUBTOTAL function

Returns a subtotal from a list or database, you can choose from a variety of arguments that determine what you want the function to do.

SUM function

Allows you to add numerical values, the function returns the sum in the cell it is entered in. The SUM function is cleverly designed to ignore text and boolean values, adding only numbers.

SUMIF function

Sums numerical values based on a condition.

SUMIFS function

Adds numbers based on criteria.

SUMPRODUCT function

Calculates the product of corresponding values and then returns the sum of each multiplication.

SUMSQ function

Calculates the sum of the squares of the arguments.

SUMX2MY2 function

Calculates the sum of the difference of squares of corresponding values in two arrays.

SUMX2PY2 function

Calculates the sum of the sum of squares of corresponding values in two arrays.

SUMXMY2 function

Calculates the sum of squares of differences of corresponding values in two arrays.

TAN function

Calculates the tangent of an angle.

TANH function

Calculates the hyperbolic tangent of a number.

TRUNC function

Removes the fractional part of the number to an integer.

Excel function categories

Array manipulation

Excel functions that let you resize, combine, and shape arrays.

Compatibility

Functions for backward compatibility with earlier Excel versions. Compatibility functions are replaced with newer functions with improved accuracy. Use the new functions if compatibility isn't required.

Database

Perform basic operations to a database-like structure.

Date and Time

Functions that let you perform calculations to Excel date and time values.

Engineering

Let's you manipulate binary numbers, convert values between different numeral systems, and calculate imaginary numbers.

Financial

Calculate present value, interest, accumulated interest, principal, accumulated principal, depreciation, payment, price, growth, yield for securities, and other financial calculations.

Information

Functions that let you get information from a cell, formatting, formula, worksheet, workbook, filepath, and other entitites.

Logical

Functions that let you return and manipulate logical values, and also control formula calculations based on logical expressions.

Lookup and reference

These functions let you sort, lookup, get external data like stock quotes, filter values based a condition or criteria, and get the relative position of a given value in a specific cell range. They also let you calculate row, column, and other properties of cell references.

Math and trigonometry

You will find functions in this category that calculates random values, round numerical values, create sequential numbers, trigonometry, and more.

Statistical

Calculate distributions, binomial distributions, exponential distribution, probabilities, variance, covariance, confidence interval, frequency, geometric mean, standard deviation, average, median, and other statistical metrics.

Text

Functions that let you manipulate text values, substitute strings, find string in value, extract a substring in a string, convert characters to ANSI code among other functions.

Web

Get data from the internet, extract data from an XML string and more.

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form