How to Build a Grid with MAKEARRAY in Excel

Generate a whole pricing table from a few inputs with MAKEARRAY — every cell calculated from its row and column number.

You will learn to

• Build a price grid from row and column numbers
• Derive a second grid from the same inputs
• Turn a grid of numbers into a grid of checks

How it works

MAKEARRAY(rows, columns, LAMBDA(r, c, calculation)) creates a grid of the size you ask for and fills each cell by calling the LAMBDA with that cell’s row number and column number. It is perfect for quote tables, rate cards and schedules that are generated from a handful of inputs.

Because rows and columns can come from cells, the grid resizes when the inputs change. Use r and c inside the calculation to scale quantities or options, for example =MAKEARRAY(B3,B4,LAMBDA(r,c,B5+r*B2*(B6+(c-1)*B7))).

=MAKEARRAY(B3,B4,LAMBDA(r,c,B5+r*B2*(B6+(c-1)*B7)))