How to Create Arrays in Excel Using the MAKEARRAY Function

Understanding the MAKEARRAY Function Syntax

The MAKEARRAY function is designed to construct an array of values based on dimensions you define (number of rows and columns). The true power of this function lies in the inclusion of the third argument: a LAMBDA function, which specifies the calculation logic for every single cell in the resulting array.

The function uses the following standardized syntax structure, which must be followed precisely for valid execution:

=MAKEARRAY(rows, cols, lambda(row, col))

The arguments are defined as follows, each playing a critical role in shaping the final output array:

• rows: This mandatory argument specifies the total number of rows that the generated array must contain. This dictates the vertical extent of the output.
• cols: This mandatory argument specifies the total number of columns that the generated array must contain, determining the horizontal extent.
• lambda(row, col): This is a user-defined LAMBDA function that takes two parameters (typically representing the current row index and column index) and defines the calculation or action that determines the value for the cell at that specific intersection.

The practical examples below demonstrate several powerful ways to utilize the MAKEARRAY function, moving beyond simple data generation into complex, dynamic data modeling within Excel.

Example 1: Generating a Calculated Matrix Using MAKEARRAY

This first example demonstrates how to leverage the LAMBDA function within MAKEARRAY to create a structured matrix where every value is derived from a specific formula involving its position (row and column index). This technique is essential for generating lookup tables, multiplication tables, or customized data grids.

To generate an array spanning 5 rows and 3 columns, where the value in each cell is calculated by multiplying its row index (r) by its column index (c), and then multiplying the result by 2, we would enter the following formula into cell A1:

=MAKEARRAY(5, 3, LAMBDA(r,c, 2*r*c))

The successful execution of this dynamic array formula results in the immediate spilling of the calculated values across the necessary range, as illustrated in the screenshot below:

The resulting array automatically dimensions itself to contain exactly 5 rows and 3 columns. The core mechanism involves the LAMBDA function executing the formula 2*r*c for each cell, using its positional coordinates as input.

We can verify the calculation logic by examining specific positions within the resulting matrix:

• The value in row 1 and column 1 (r=1, c=1): Calculation is 2 * 1 * 1 = 2.
• The value in row 2 and column 1 (r=2, c=1): Calculation is 2 * 2 * 1 = 4.
• The value in row 3 and column 1 (r=3, c=1): Calculation is 2 * 3 * 1 = 6.
• Similarly, the value at row 5 and column 3 (r=5, c=3) would be 2 * 5 * 3 = 30.

This confirms that the calculation is applied universally across the specified dimensions, providing a clean and maintainable way to generate structured data based on positional logic.

Example 2: Generating Random Text Samples Dynamically

Beyond numerical calculations, the MAKEARRAY function is highly effective for simulation and generating random text outputs. By nesting functions like RANDBETWEEN and CHOOSE inside the LAMBDA definition, we can instruct Excel to randomly select from a predefined list of text strings for each array element.

To create a single-column list of 15 rows containing randomly chosen team names (“Mavs”, “Hawks”, or “Kings”), we use the following formula. Note that since the array is one column wide, the column index (c) is ignored in the LAMBDA calculation:

=MAKEARRAY(15,1,LAMBDA(r,c,CHOOSE(RANDBETWEEN(1,3),"Mavs",Hawks","Kings")))

The resulting dataset is ideal for creating placeholder data, running simple text randomization tests, or demonstrating data distribution, as seen here:

This specific formula constructs an array with 15 rows and 1 column. For every single cell, the nested RANDBETWEEN function generates a random integer (1, 2, or 3), which is then passed to the CHOOSE function to select the corresponding text string (“Mavs”, “Hawks”, or “Kings”). The result is a highly randomized but controlled data sample.

Example 3: Constructing Arrays with Random Numeric Data

A common requirement in data modeling and testing is the creation of datasets populated by random numerical values. Utilizing MAKEARRAY in conjunction with the RANDBETWEEN function provides a streamlined method for generating such simulated data grids quickly and efficiently.

To generate an array spanning 10 rows and 2 columns, where every cell contains a random integer between 1 and 100, we enter the following formula into cell A1:

=MAKEARRAY(10, 2, LAMBDA(r,c, RANDBETWEEN(1,100)))

This formula establishes the dimensions (10×2) and then applies the randomization logic across the entire resulting grid. Observe the output in the screenshot below, demonstrating the spilled array:

The final output is an array perfectly sized at 10 rows and 2 columns. The primary calculation executed by the internal function is simply RANDBETWEEN(1, 100), which is recalculated every time the worksheet refreshes. This capability is crucial for statistical sampling, dynamic charting, and stress-testing spreadsheet models using varying inputs.

Summary and Advanced Applications

The integration of dynamic array functions, particularly MAKEARRAY, fundamentally changes how users approach data generation and calculation in Excel. By encapsulating both the dimensions and the calculation logic within a single cell, complex data structures can be created instantly, requiring minimal manual manipulation or intermediate helper columns.

Whether you need to generate lookup matrices, simulate random data for statistical analysis, or create highly tailored data views based on positional criteria, MAKEARRAY provides an elegant and powerful solution. Mastering its use, especially in combination with other dynamic functions like RANDBETWEEN, CHOOSE, or FILTER, will significantly enhance your productivity and modeling accuracy.

Related Excel Tutorials

To further explore advanced data manipulation and dynamic array capabilities in Excel, consider reviewing the following tutorials on related common operations: