How to Use the MAKEARRAY Function to Generate Custom Grids in Excel

The Limitation of Static Data Grids

If you are building an advanced dashboard or a complex financial model in Microsoft Excel, you occasionally need to generate an empty grid of data based on user input. For example, if a manager types “5” for Rows and “3” for Columns in an input cell, you might want Excel to instantly generate a 5×3 grid on the screen, populated with a default baseline number or a dynamic calculation.

Historically, achieving this level of dynamic grid generation required writing a visual basic (VBA) macro that looped through the spreadsheet and physically typed data into cells. Today, Excel has a dedicated dynamic array function designed exclusively to generate custom grids on the fly: MAKEARRAY.

The MAKEARRAY function allows you to specify the exact height and width of a grid, and then uses a custom LAMBDA formula to decide exactly what data should be placed inside every single cell of that grid.

Understanding the Syntax

The MAKEARRAY function is a “Helper Function.” It does not perform calculations on its own; it builds the grid and then relies on a LAMBDA formula to fill it.

=MAKEARRAY(rows, cols, LAMBDA(row_index, col_index, calculation))

• rows: The number of rows you want the grid to have (e.g., 5).
• cols: The number of columns you want the grid to have (e.g., 3).
• LAMBDA: The custom formula that tells Excel what to put inside each cell. It must always accept two variables: the current row number and the current column number.

Example 1: Generating a Blank Grid

Assume you want to instantly generate a grid that is 5 rows tall and 3 columns wide, and you want every single cell in that grid to contain the word “Pending”.

Click on an empty cell and type:

=MAKEARRAY(5, 3, LAMBDA(r, c, "Pending"))

How this works:

1. Excel creates the 5×3 grid in memory.
2. It goes to the very first cell (Row 1, Column 1) and passes the numbers 1, 1 into the LAMBDA.
3. The LAMBDA ignores those numbers and simply outputs the word “Pending”.
4. Excel moves to the next cell and repeats the process until the 15-cell grid is completely filled.

The result is a perfectly spilled 5×3 array on your screen.

Example 2: Creating a Multiplication Table

The true power of the MAKEARRAY function is that the LAMBDA formula knows the exact row and column coordinate of the cell it is currently filling. You can use these coordinates (the r and c variables) to perform dynamic math.

For example, you can instantly generate a classic 10×10 multiplication table using this formula:

=MAKEARRAY(10, 10, LAMBDA(r, c, r * c))

How this works:

1. Excel moves to Row 3, Column 4.
2. It passes the numbers 3, 4 into the LAMBDA formula.
3. The LAMBDA executes the math 3 * 4 and outputs 12 into that specific cell.
4. It instantly repeats this logic for all 100 cells in the array.

Example 3: Dynamic Calendars and Schedules

Because the rows and cols arguments can point to other cells in your spreadsheet, you can create highly interactive dashboards.

Assume cell B1 contains the number of employees (e.g., 10), and cell B2 contains the number of days in the month (e.g., 31).

You can use MAKEARRAY to instantly generate a blank scheduling grid tailored to those exact numbers, pre-filled with an “Off” status:

=MAKEARRAY(B1, B2, LAMBDA(emp, day, "Off"))

If you change the number of employees in cell B1 from 10 to 50, the MAKEARRAY function will instantly expand, spilling 40 new rows of data onto the screen automatically.