Build a Calculated Grid with MAKEARRAY

Excel 365LAMBDA

MAKEARRAY generates a grid where every cell is computed from its row and column position — a multiplication table, a distance matrix, a calendar layout. You set the size and a formula that knows where it is.

Quick formula: to build a 3×3 multiplication table:

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

For each cell, the LAMBDA gets its row r and column c and returns r * c; MAKEARRAY spills the whole grid.

Functions used (tap for the full reference guide):

MAKEARRAY LAMBDA

The example

A 3×3 times-table, every cell = row × column.

	A	B	C
1	1	2	3
2	2	4	6
3	3	6	9

The formula

The multiplication grid:

=MAKEARRAY(3, 3, LAMBDA(r, c, r * c)) // each cell = its row × column

How it works

MAKEARRAY builds a grid position by position:

The first two arguments are the rows and columns of the output grid (3 × 3).
The LAMBDA(r, c, …) is called for every cell and receives that cell’s row and column index — r * c here makes the times-table.
MAKEARRAY assembles all the results into one spilled grid.
Because the formula sees its position, you can build matrices, patterns, or position-aware lookups that plain spilling can’t.

Position-aware power: use r and c to INDEX into other ranges, build a running grid, or create a checkerboard (ISEVEN(r+c)). It’s SEQUENCE for two dimensions, with full formula control per cell.

Try it: interactive demo

Live demo

Set the grid size for a row×column times-table.

Rows

Cols

Variations

Checkerboard pattern

Alternate 0/1 by position:

=MAKEARRAY(8, 8, LAMBDA(r, c, MOD(r+c, 2)))

Position-aware lookup

Pull from other ranges by index:

=MAKEARRAY(n, m, LAMBDA(r, c, INDEX(data, r, c)))

Identity matrix

1 on the diagonal, 0 elsewhere:

=MAKEARRAY(3, 3, LAMBDA(r, c, --(r=c)))

Pitfalls & errors

The LAMBDA needs two parameters. MAKEARRAY always passes row and column — LAMBDA(r, c, …). One parameter errors.

Indices start at 1. r and c are 1-based positions within the output, not sheet rows/columns.

Excel 365 only, and large grids are heavy — a 1000×1000 MAKEARRAY computes a million cells.

Practice workbook

📊

Download the free Build a Calculated Grid with MAKEARRAY practice workbook

MAKEARRAY times-table, checkerboard, and identity-matrix examples (results shown), plus 4 challenges with answers. No sign-up required.

Frequently asked questions

What does MAKEARRAY do in Excel?

MAKEARRAY builds a grid where each cell is computed from its position: =MAKEARRAY(rows, cols, LAMBDA(r, c, ...)). The LAMBDA receives each cell's row and column index. Requires Excel 365.

How do I make a multiplication table with a formula?

Use =MAKEARRAY(9, 9, LAMBDA(r, c, r*c)) to spill a 9x9 times-table where every cell equals its row times its column.

How is MAKEARRAY different from SEQUENCE?

SEQUENCE generates a numeric series. MAKEARRAY runs a full LAMBDA per cell that knows its row and column, so you can build patterns, matrices, and position-aware calculations.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: MAKEARRAY · LAMBDA