Home > Formulas > Math Trig >RANDARRAY

RANDARRAY Function Examples

Debra Dalgleish - Contextures

Use the RANDARRAY function to make lists of random numbers or random text in Excel 365.

In older versions, use RAND or RANDBETWEEN.

RANDARRAY and INDEX formula creates random list of months

1) Video: RANDARRAY Function

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.

1.1) RANDARRAY Arguments

Here's the syntax for RANDARRAY, with 5 arguments:

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

Tip: To experiment with the RANDARRAY arguments, download my RANDARRAY Syntax workbook.

2) RANDARRAY Examples - Intro

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.

2.1) Create Random Numbers List

In the screenshot below, the RANDARRAY formula in cell B8 creates a list of random numbers.

  • =RANDARRAY($B$4, 1, 1, $C$4, TRUE)

To make it easy to change the Rows and Max settings, the formula refers to cells on the worksheet.

  • $B$4 - enter the number of Rows to create
  • $C$4 - enter the highest number (Max) to return

RANDARRAY example list of numbers

2.2) Create Random Months List

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

  • =INDEX(ListMonths, RANDARRAY($B$4,1,1,12,TRUE))

RANDARRAY and INDEX formula creates random list of months

  • To make it easy to change the Rows setting, the formula refers cell B4 - where you can type a number.
  • The Max argument is set as 12 in the formula - the number of months in each year.

Here's how the formula works:

  1. The RANDARRAY function returns a list of 6 random whole numbers between 1 and 12, in one column.
  2. For each integer in the list, the INDEX function returns the month name from that position in the list of months (ListMonths)

2.3) Create Random Names List

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

  • =INDEX(ListNames, RANDARRAY($B$4,1,1, COUNTA(ListNames),TRUE))

RANDARRAY and INDEX formula creates random list of names

  • To make it easy to change the Rows setting, the formula refers cell B4 - where you can type a number.
  • For the Max argument, the COUNTA function returns the number of values in employees list, named ListNames

3) RANDARRAY Examples - Advanced

There are three advanced RANDARRAY examples in this section:

    3.1) Random Workdays List

    3.2) Choose Random Names

    3.3) Random Combo List

3.1) Random Workdays 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.

Workdays - Monday to Friday

In the screenshot below, the blue cells have the date range start date and end date

  • In the Random Days file, you'll also enter the number of records that need dates.

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

random lists of workdays in specific date range

  • Workdays: Starting in cell F6, there's a list of all the valid workdays in that range.
    • The formula uses the WORKDAY function, which always uses Saturday and Sunday as days off.
  • Random (Sorted): In column J, there's a list of 244 random dates, pulled from the list in column F. That list is sorted, from earliest to latest
  • Random: In column L, there's a list of 244 random dates, pulled from the list in column F. That list is NOT sorted

Workdays - Set the Working Days

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.

  • At the top of the sheet, type a zero for the working days, and a 1 for the non-working days.

set working days for random lists of workdays in specific date range

3.2 Choose Random Names

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

random list of names created with formulas

Download the Random Name Chooser workbook, to follow along

3.3) Create Random Combo Lists

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.

Source lists for 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

sample data table to fill with random days, items, names

RANDARRAY Lists for Fictional Data

On the Combos sheet, there are formulas to create the fictional data:

  • Cells D6 and G6 contain RANDARRAY formulas
  • In cell D4, type 1, 2, or 3, to create random lists of Days, Items, or Names.

To fill the columns in the table named tblData, you could:

RANDARRAY formulas create random lists of names

RANDARRAY Formula

Here's the formula in cells D6 and G6, and the details are below.

  • =LET(list, CHOOSE($D$4,ListDays,ListItems,ListNames), INDEX(list, RANDARRAY(COUNT(tblData[ID]),1,1, COUNTA(list),TRUE)))

How the Formula Works

Here's how the formula works:

  • The INDEX and RANDARRAY functions return a random list, based on which number you type in cell D4.
  • The CHOOSE function returns the named range for the selected number: 1=ListDays, 2=ListItems, and 3=ListNames
  • The COUNTA functions return the number of items in the selected list, and the number of IDs in the table named tblCombo.
  • The LET function creates a list variable for the CHOOSE function, so the formula is easier to read

Copy Random List to tblData

To fill the columns in the table named tblData, you could use either one of the random lists, from D6 or G6.

  • Select cell D6 or G6
  • Press Ctrl+A to select the entire list, then Ctrl+C to copy
  • Go to the table named tblData
  • Right-click the first data cell in the column where you want to paste the random list
  • In the pop-up menu, click the Passte Values icon

Paste the copied list as values

Combine the Lists

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.

Formula to combine data from random lists

Combo Formula

Here's the combo formula in cell B6

  • =D6#&IF(D6#=G6#,"",", "&G6#)

Here's how the combo formula works

  • The value from column D is always used.
    • The reference is D6# - the formula cell address (D6), followed by the Spill operator (#).
  • If a row has two different values in the random lists, those two items are combined in column B, separated by a comma and space
  • If the two values are the same, only the first one is used, and there's no comma in the result

For example, in the screenshot above, "Binder" was in row 12 twice, so the result in cell B12 is "Binder"

4) Get the Excel Files ✅

Note: All of these files use spill functions available in Excel 365.

Get Monthly Excel Tips!

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!

Related Links

RAND and RANDBETWEEN

Random Name Chooser

CHOOSE Function

INDEX function and MATCH Function

Count Functions

WORKDAY Function

 

 

Last updated: October 6, 2026 11:46 AM