How to generate random text using the RANDARRAY() function in Excel

The recent article How to generate random letters in Excel shows you how to generate random letters. It’s an interesting tutorial, but a more practical example might be returning a list of meaningful text content, such as a random list of names. In this Excel tutorial, we’ll use a dynamic expression with RANDARRAY() at the heart of it to generate a random list of names. By including an input value, you can control the number of random names generated.

SEE: 83 Excel tips every user should master (TechRepublic)

I’m using Microsoft 365 on a Windows 10 64-bit system. This function is available only in Microsoft 365, Excel for the Web, Excel 2021 and Excel for iPad, iPhone, and Android tablets and phones. For your convenience, you can download the demonstration .xlsx file.

What’s a dynamic array?

RANDARRAY() is one of several newish dynamic array functions. If you’ve ever entered an expression using Ctrl + Shift + Enter, then you’re already familiar with how Excel used to work with dynamic arrays. Thanks to the new dynamic array functions, complicated requirements are easier than ever to meet. The results spill into the cells below, filling as many cells as necessary to complete the expression’s calculations. That’s called the spill range. If you see a spill error, then the range needed to fulfill the function isn’t available; move the function or remove the content that’s in the way. When you select any cell in a spill range, Excel highlights the entire range with a blue border. You will always find the expression in the topmost cell of that range.

About RANDARRAY() in Excel

The RANDARRAY() function returns an array (spill range) of random numbers. You supply the number of rows and columns to fill, the minimum and maximum values, and whether the returned values are integers or decimals. This function’s syntax

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

is rather unique in that all arguments are optional. If you omit all the arguments, the function returns a single value between 0 and 1, allowing for decimals. In addition, if your data set is in a Table, the array adapts as you add and remove data, as long as you use structured references. This type of references uses column names and other symbols instead of cell or range references. That’s what we’ll be using in this article. If you’re not familiar with structured references, don’t worry. I’ll show you more about this later.

SEE: Office 365: A guide for tech and business leaders (free PDF) (TechRepublic)

The arguments are fairly self-explanatory, but I will mention that integer’s default is FALSE, which allows decimal values. Specify TRUE when you want only integers. In addition, links between workbooks only work when both workbooks are open, so this isn’t a flexible solution if you need to evaluate linked data.

Let’s start with a simple example that returns integer values between 0 and 10 over a range of six rows and two columns:

RANDARRAY(6,2,0,10,TRUE)

As you can see in Figure A, this function gives us a set of random integers. As mentioned earlier, the function is in the top-left cell (B2), and a boundary surrounds the entire spill range. Clicking any cell in the cell range will display that boundary. However, we don’t want random values; we want random strings. Now that you know how to use this function, let’s work out an expression that includes RANDARRAY() to return a set of random names.

Figure A

Return random names

RANDARRAY() returns numbers, not text, so the finished expression is a bit more complex than RANDARRAY() can handle by itself. First, we need some source data—a range of names. Figure B shows a Table object with a single column, First Name. To convert data into a Table, select the data and Press Ctrl + T. Excel provides the Table with a default name, but you can change it by clicking inside the Table to display the Table Design tab. The Table Name control is to the far left. Simply enter a name, such as Names, and press Enter. The structured reference to this Table is Names[First Names].

Figure B

At this point, we can return a set of random values, but we want to use those values to generate a list of random names (text). To accomplish this, we’ll use the INDEX() function to point the RANDARRAY() function to Names[First Name] as follows:

=INDEX(Names[First Name],RANDARRAY(D2,1,1,COUNTA(Names[First Name])))

Figure C shows the finished expression and the input value in D2.

Figure C

Names[First Name] is the structured referencing I mentioned earlier. If you select the actual Table, Excel will use a longer version, Names[[#All],[First Name]], but the shorter reference works the same, so don’t worry about that slight difference.

The COUNTA() function sets the maximum values (using the max argument) to the same number of cells in the Table. Currently, that’s 10. The use of this function makes the expression dynamic—it will update as you add and delete names to the Table. You could use an explicit number or even an input value, but in the context of this example, COUNTA() makes the best sense.

The reference to D2 as RANDARRAY()’s first argument (rows) determines how many names the expression returns. Simply change the value in D2 to change the number of names in the random list. INDEX() identifies the values to use in the list. You could add the TRUE argument to RANDARRAY() but because we’re pulling text, it won’t matter.

Let’s work through the example shown in Figure C, where the input value in D2 is 6. INDEX() returns an internal list of all of the names in the Names Table. RANDARRAY() evaluates as

RANDARRAY(D2,1,1,COUNTA(Names[First Name]))

RANDARRAY(6,1,1,10)

which returns a single list of six random numbers between 1 and 10. The INDEX() function uses those six random numbers to return names from the Table, Names.

Keep in mind that the list updates every time you calculate the sheet, which includes every time you do just about anything at the sheet level. The random list won’t be unique, and it isn’t sorted. By adding the SORT() dynamic array function, as shown in Figure D, you can return a sorted list using the following expression:

=SORT(INDEX(Names[First Name],RANDARRAY(D2,1,1,COUNTA(Names[First Name]))))

Figure D

Returning random numbers is easy. It takes a bit more work to return random characters and a little more work to return random words and phrases that have meaning. Thanks to RANDARRAY() the latter is easier than it might otherwise be. We used a list of names, but you could use a set of terms, phrases and so on.