The MAKEARRAY function lets you generate custom arrays in Excel without manual data entry. This tutorial walks you through creating dynamic arrays for practical business applications, from simple number grids to complex data matrices. Let’s start building arrays that automate your spreadsheet tasks.

Create Your First Array with MAKEARRAY

The MAKEARRAY function creates arrays using three key parameters: rows, columns, and a lambda expression that determines cell values. Let’s break down how to use it effectively.

Basic Syntax

=MAKEARRAY(rows, cols, lambda)

The function requires:

• rows: Number of rows in the output array
• cols: Number of columns in the output array
• lambda: Formula to calculate each cell’s value

Let’s create your first array:

1. Open a new Excel worksheet
2. Select cell A1
3. Enter the formula:

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

1. Press Enter

This creates a 3×3 array where each cell contains the product of its row and column numbers.

Generate a Simple Numbers Grid

Let’s build practical number grids using MAKEARRAY:

Create a Sequential Number Grid:

1. Select cell A1
2. Enter:

=MAKEARRAY(5,5,LAMBDA(r,c,(r-1)*5+c))

This generates a 5×5 grid with sequential numbers from 1 to 25.

Build a Multiplication Table:

1. Select cell A1
2. Enter:

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

This creates a 10×10 multiplication table.

Calculate Custom Values Across Rows and Columns

MAKEARRAY’s power lies in its ability to use row and column parameters for calculations:

Create a Growth Rate Matrix:

=MAKEARRAY(12,4,LAMBDA(r,c,1.05^((r-1)*c)))

This generates a matrix showing 5% growth rates across periods.

Build Practical MAKEARRAY Solutions

Let’s apply MAKEARRAY to real business scenarios.

Sales Performance Calculator

Monthly Sales Target Matrix:

=MAKEARRAY(12,3,LAMBDA(r,c,5000*(1.1^(c-1))*(1+IF(MOD(r,3)=0,0.15,0))))

This creates:

• 12 months of sales targets
• 3 product categories
• 15% quarterly bonus targets

Custom Data Table Generator

Regional Price Matrix:

=MAKEARRAY(4,5,LAMBDA(r,c,BASE_PRICE*(1+REGION_MARKUP[r])*(1+SEASON_FACTOR[c])))

Replace BASE_PRICE, REGION_MARKUP, and SEASON_FACTOR with your specific values.

What is the MAKEARRAY Function?

MAKEARRAY’s components work together to:

• Define array dimensions
• Process row and column positions
• Apply calculations consistently
• Return dynamic results

Limitations:

• Maximum 16,384 rows
• Maximum 16,384 columns
• Must return numeric or text values

Combine MAKEARRAY with Other Functions

Enhance MAKEARRAY’s capabilities by combining it with other functions:

With LAMBDA:

=LET(base,1000,growth,1.1,

MAKEARRAY(12,3,LAMBDA(r,c,base*growth^(r-1))))

Array Manipulation Techniques

Filter Arrays:

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

MAKEARRAY(10,2,LAMBDA(r,c,r*c))>10)

Conditional Arrays:

=MAKEARRAY(5,5,LAMBDA(r,c,IF(r=c,1,0)))

This creates an identity matrix.

Next Steps

You’ve learned to create dynamic arrays for various business applications. Start by implementing these formulas in your spreadsheets, then customize them for your specific needs. For more advanced data automation and real-time updates, try Coefficient’s Excel add-in.