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

Use the RANDARRAY() dynamic array operate to return random names or different textual content in Microsoft Excel.


Picture: PixieMe/Shutterstock

The latest article Learn how to generate random letters in Excel reveals you methods to generate random letters. It is an fascinating tutorial, however a extra sensible instance is likely to be returning a listing of significant textual content content material, akin to a random record of names. On this Excel tutorial, we’ll use a dynamic expression with RANDARRAY() on the coronary heart of it to generate a random record of names. By together with an enter worth, you may management the variety of random names generated.

SEE: 83 Excel ideas each person ought to grasp (TechRepublic)

I am utilizing Microsoft 365 on a Home windows 10 64-bit system. This operate is on the market solely in Microsoft 365, Excel for the Net, Excel 2021 and Excel for iPad, iPhone, and Android tablets and telephones. In your comfort, you may obtain the demonstration .xlsx file.

What’s a dynamic array?

RANDARRAY() is certainly one of a number of newish dynamic array features. Should you’ve ever entered an expression utilizing Ctrl + Shift + Enter, then you definately’re already aware of how Excel used to work with dynamic arrays. Due to the brand new dynamic array features, sophisticated necessities are simpler than ever to fulfill. The outcomes spill into the cells under, filling as many cells as mandatory to finish the expression’s calculations. That is referred to as the spill vary. Should you see a spill error, then the vary wanted to satisfy the operate is not accessible; transfer the operate or take away the content material that is in the best way. When you choose any cell in a spill vary, Excel highlights your complete vary with a blue border. You’ll at all times discover the expression within the topmost cell of that vary.

About RANDARRAY() in Excel

The RANDARRAY() operate returns an array (spill vary) of random numbers. You provide the variety of rows and columns to fill, the minimal and most values, and whether or not the returned values are integers or decimals. This operate’s syntax


is quite distinctive in that each one arguments are elective. Should you omit all of the arguments, the operate returns a single worth between 0 and 1, permitting for decimals. As well as, in case your information set is in a Desk, the array adapts as you add and take away information, so long as you utilize structured references. The sort of references makes use of column names and different symbols as an alternative of cell or vary references. That is what we’ll be utilizing on this article. Should you’re not aware of structured references, don’t fret. I will present you extra about this later.

SEE: Workplace 365: A information for tech and enterprise leaders (free PDF) (TechRepublic)

The arguments are pretty self-explanatory, however I’ll point out that integer’s default is FALSE, which permits decimal values. Specify TRUE whenever you need solely integers. As well as, hyperlinks between workbooks solely work when each workbooks are open, so this is not a versatile answer if it is advisable consider linked information.

Let’s begin with a easy instance that returns integer values between 0 and 10 over a variety of six rows and two columns:


As you may see in Determine A, this operate provides us a set of random integers. As talked about earlier, the operate is within the top-left cell (B2), and a boundary surrounds your complete spill vary. Clicking any cell within the cell vary will show that boundary. Nonetheless, we do not need random values; we would like random strings. Now that you understand how to make use of this operate, let’s work out an expression that features RANDARRAY() to return a set of random names.  

Determine A


  Use RANDARRAY() to return random values.

Return random names

RANDARRAY() returns numbers, not textual content, so the completed expression is a little more advanced than RANDARRAY() can deal with by itself. First, we’d like some supply information—a variety of names. Determine B reveals a Desk object with a single column, First Identify. To transform information right into a Desk, choose the info and Press Ctrl + T. Excel gives the Desk with a default title, however you may change it by clicking contained in the Desk to show the Desk Design tab. The Desk Identify management is to the far left. Merely enter a reputation, akin to Names, and press Enter. The structured reference to this Desk is Names[First Names].

Determine B


  We’ll use an expression to return random names from this Desk.

At this level, we will return a set of random values, however we wish to use these values to generate a listing of random names (textual content). To perform this, we’ll use the INDEX() operate to level the RANDARRAY() operate to Names[First Name] as follows:

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

Determine C reveals the completed expression and the enter worth in D2.

Determine C


  The expression makes use of RANDARRAY() to generate a listing of random names, beginning at D5.

Names[First Name] is the structured referencing I discussed earlier. If you choose the precise Desk, Excel will use an extended model, Names[[#All],[First Name]], however the shorter reference works the identical, so don’t fret about that slight distinction.

The COUNTA() operate units the utmost values (utilizing the max argument) to the identical variety of cells within the Desk. Presently, that is 10. The usage of this operate makes the expression dynamic—it’s going to replace as you add and delete names to the Desk. You could possibly use an specific quantity and even an enter worth, however within the context of this instance, COUNTA() makes the very best sense.

The reference to D2 as RANDARRAY()’s first argument (rows) determines what number of names the expression returns. Merely change the worth in D2 to alter the variety of names within the random record. INDEX() identifies the values to make use of within the record. You could possibly add the TRUE argument to RANDARRAY() however as a result of we’re pulling textual content, it will not matter.

Let’s work by the instance proven in Determine C, the place the enter worth in D2 is 6. INDEX() returns an inside record of the entire names within the Names Desk. RANDARRAY() evaluates as

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


which returns a single record of six random numbers between 1 and 10. The INDEX() operate makes use of these six random numbers to return names from the Desk, Names.

Take into account that the record updates each time you calculate the sheet, which incorporates each time you do absolutely anything on the sheet stage. The random record will not be distinctive, and it is not sorted. By including the SORT() dynamic array operate, as proven in Determine D, you may return a sorted record utilizing the next expression:

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

Determine D


  Add SORT() to kind the random record of names.

Returning random numbers is straightforward. It takes a bit extra work to return random characters and just a little extra work to return random phrases and phrases which have which means. Due to RANDARRAY() the latter is simpler than it’d in any other case be. We used a listing of names, however you possibly can use a set of phrases, phrases and so forth. 

Additionally see

Recent Articles


Related Stories

Leave A Reply

Please enter your comment!
Please enter your name here

Stay on op - Ge the daily news in your inbox