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

2 years ago 263

Use the RANDARRAY() dynamic array relation to instrumentality random names oregon different substance successful Microsoft Excel.

The caller article How to make random letters successful Excel shows you however to make random letters. It's an absorbing tutorial, but a much applicable illustration mightiness beryllium returning a database of meaningful substance content, specified arsenic a random database of names. In this Excel tutorial, we'll usage a dynamic look with RANDARRAY() astatine the bosom of it to make a random database of names. By including an input value, you tin power the fig of random names generated.

SEE: 83 Excel tips each idiosyncratic should master (TechRepublic)

I'm utilizing Microsoft 365 connected a Windows 10 64-bit system. This relation is disposable lone successful Microsoft 365, Excel for the Web, Excel 2021 and Excel for iPad, iPhone, and Android tablets and phones. For your convenience, you tin download the objection .xlsx file.

What's a dynamic array?

RANDARRAY() is 1 of respective newish dynamic array functions. If you've ever entered an look utilizing Ctrl + Shift + Enter, past you're already acquainted with however Excel utilized to enactment with dynamic arrays. Thanks to the caller dynamic array functions, analyzable requirements are easier than ever to meet. The results spill into the cells below, filling arsenic galore cells arsenic indispensable to implicit the expression's calculations. That's called the spill range. If you spot a spill error, past the scope needed to fulfill the relation isn't available; determination the relation oregon region the contented that's successful the way. When you prime immoderate compartment successful a spill range, Excel highlights the full scope with a bluish border. You volition ever find the look successful the topmost compartment of that range.

About RANDARRAY() successful Excel

The RANDARRAY() relation returns an array (spill range) of random numbers. You proviso the fig of rows and columns to fill, the minimum and maximum values, and whether the returned values are integers oregon decimals. This function's syntax

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

is alternatively unsocial successful that each arguments are optional. If you omit each the arguments, the relation returns a azygous worth betwixt 0 and 1, allowing for decimals. In addition, if your information acceptable is successful a Table, the array adapts arsenic you adhd and region data, arsenic agelong arsenic you usage structured references. This benignant of references uses file names and different symbols alternatively of compartment oregon scope references. That's what we'll beryllium utilizing successful this article. If you're not acquainted with structured references, don't worry. I'll amusement you much astir this later.

SEE: Office 365: A usher for tech and concern leaders (free PDF) (TechRepublic)

The arguments are reasonably self-explanatory, but I volition notation that integer's default is FALSE, which allows decimal values. Specify TRUE erstwhile you privation lone integers. In addition, links betwixt workbooks lone enactment erstwhile some workbooks are open, truthful this isn't a flexible solution if you request to measure linked data.

Let's commencement with a elemental illustration that returns integer values betwixt 0 and 10 implicit a scope of six rows and 2 columns:

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

As you tin spot successful Figure A, this relation gives america a acceptable of random integers. As mentioned earlier, the relation is successful the top-left compartment (B2), and a bound surrounds the full spill range. Clicking immoderate compartment successful the compartment scope volition show that boundary. However, we don't privation random values; we privation random strings. Now that you cognize however to usage this function, let's enactment retired an look that includes RANDARRAY() to instrumentality a acceptable of random names.

Figure A

Return random names

RANDARRAY() returns numbers, not text, truthful the finished look is simply a spot much analyzable than RANDARRAY() tin grip by itself. First, we request immoderate root data—a scope of names. Figure B shows a Table entity with a azygous column, First Name. To person information into a Table, prime the information and Press Ctrl + T. Excel provides the Table with a default name, but you tin alteration it by clicking wrong the Table to show the Table Design tab. The Table Name power is to the acold left. Simply participate a name, specified arsenic Names, and property Enter. The structured notation to this Table is Names[First Names].

Figure B

At this point, we tin instrumentality a acceptable of random values, but we privation to usage those values to make a database of random names (text). To execute this, we'll usage the INDEX() relation to constituent the RANDARRAY() relation to Names[First Name] arsenic follows:

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

Figure C shows the finished look and the input worth successful D2.

Figure C

Names[First Name] is the structured referencing I mentioned earlier. If you prime the existent Table, Excel volition usage a longer version, Names[[#All],[First Name]], but the shorter notation works the same, truthful don't interest astir that flimsy difference.

The COUNTA() relation sets the maximum values (using the max argument) to the aforesaid fig of cells successful the Table. Currently, that's 10. The usage of this relation makes the look dynamic—it volition update arsenic you adhd and delete names to the Table. You could usage an explicit fig oregon adjacent an input value, but successful the discourse of this example, COUNTA() makes the champion sense.

The notation to D2 arsenic RANDARRAY()'s archetypal statement (rows) determines however galore names the look returns. Simply alteration the worth successful D2 to alteration the fig of names successful the random list. INDEX() identifies the values to usage successful the list. You could adhd the TRUE statement to RANDARRAY() but due to the fact that we're pulling text, it won't matter.

Let's enactment done the illustration shown successful Figure C, wherever the input worth successful D2 is 6. INDEX() returns an interior database of each of the names successful the Names Table. RANDARRAY() evaluates as

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

RANDARRAY(6,1,1,10)

which returns a azygous database of six random numbers betwixt 1 and 10. The INDEX() relation uses those six random numbers to instrumentality names from the Table, Names.

Keep successful caput that the database updates each clip you cipher the sheet, which includes each clip you bash conscionable astir thing astatine the expanse level. The random database won't beryllium unique, and it isn't sorted. By adding the SORT() dynamic array function, arsenic shown successful Figure D, you tin instrumentality a sorted database utilizing the pursuing expression:

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

Figure D

Returning random numbers is easy. It takes a spot much enactment to instrumentality random characters and a small much enactment to instrumentality random words and phrases that person meaning. Thanks to RANDARRAY() the second is easier than it mightiness different be. We utilized a database of names, but you could usage a acceptable of terms, phrases and truthful on.