|
Home > Formulas > Math Trig >RANDARRAY RANDARRAY Function Examples
|

The Excel RANDARRAY function is available in Excel 365, and other versions that support Spill functions.
In this short video, I show a simple RANDARRAY formula, to create a list of 30 random numbers. Next, I make better formulas, that make it easy to quickly adjust the RANDARRAY results.
Here's the syntax for RANDARRAY, with 5 arguments:
Tip: To experiment with the RANDARRAY arguments, download my RANDARRAY Syntax workbook.
There are three RANDARRAY examples in this section, to help you get started.
2.1) Random Numbers List
2.2) Random Months List
2.3) Random Names List
Download the the RANDARRAY Examples sample file to follow along.
In the screenshot below, the RANDARRAY formula in cell B8 creates a list of random numbers.
To make it easy to change the Rows and Max settings, the formula refers to cells on the worksheet.

In the screenshot below, the formula in cell B8 combines RANDARRAY with the Excel INDEX function, to create a random list of month names.

Here's how the formula works:
In the screenshot below, the formula in cell B8 combines RANDARRAY with the Excel INDEX function, to create a random list of employee names.

There are three advanced RANDARRAY examples in this section:
3.1) Random Workdays List
3.2) Choose Random Names
3.3) Random Combo List
For one of my Excel sample files, I needed a list of workdays, in a specific date range. Instead of creating a list for that workbook only, I made a new workbook, with tools to create lists of dates, in specific date ranges.
In the screenshot below, the blue cells have the date range start date and end date
To use one of the lists, select the formula cell at the top of the list, press Ctrl+A to select the list, then Ctrl+C to copy the list. Go to the location where you want the date, and Paste As VALUES

On another sheet in the sample file, I used the WORKDAYS.INTL function, to create the list of valid workdays.
With that function, you can specify the working days and non-working days.

See how you can choose a specific number of random names, from a list of names on a worksheet. There are detailed steps on the Choose Random Names page

Download the Random Name Chooser workbook, to follow along
In this example, RANDARRAY formulas create random lists from three different tables - tblDays, tblItems and tblNames.
The data in each table is a named range: ListDays, ListItems and ListNames. Those names are used in the RANDARRAY formulas.

On the Data sheet, there's a table named tblData. It has 3 columns to fill with fictional data - Days, Items and Names

On the Combos sheet, there are formulas to create the fictional data:
To fill the columns in the table named tblData, you could:

Here's the formula in cells D6 and G6, and the details are below.
Here's how the formula works:
To fill the columns in the table named tblData, you could use either one of the random lists, from D6 or G6.

For some sample Excel files, it's good to have data that's a bit more complex. It's helpful for practising with functions like TEXTSPLIT, or splitting first and last names.
To create that type of data, there's another formula on the Combo sheet, in cell B6. It creates a "combo" list - with one or two values from the random lists.

Here's the combo formula in cell B6
Here's how the combo formula works
For example, in the screenshot above, "Binder" was in row 12 twice, so the result in cell B12 is "Binder"
Note: All of these files use spill functions available in Excel 365.
Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.
Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!
INDEX function and MATCH Function
Last updated: October 6, 2026 11:46 AM