How to Use the BYROW and BYCOL Functions to Loop Over Arrays in Excel

The Limitation of Spilled Arrays

In Microsoft Excel, the modern dynamic array engine is incredibly powerful. If you have a column of 100 prices (A1:A100) and you want to calculate a 10% tax on all of them, you simply type =A1:A100 * 0.10. Excel instantly performs the math 100 separate times and spills the results seamlessly down your screen.

However, this automatic spilling only works for basic arithmetic. What if you want to use a more complex aggregation function, like MAX, AVERAGE, or TEXTJOIN, on an entire grid of data, but you want Excel to calculate the results individually for each specific row?

For example, if you have a grid showing the monthly sales for 50 different employees (Rows 1 to 50, Columns B through M), and you type =MAX(B1:M50), Excel will not give you 50 different answers. It will collapse the entire grid and output one single number: the absolute highest sale made by anyone, ever.

To force Excel to run an aggregation function individually across every single row (or column) and spill the results, you must use the BYROW or BYCOL helper functions.

Understanding the Syntax

The BYROW and BYCOL functions do not perform any math themselves. They simply slice up a large 2D grid into individual 1D rows (or columns) and feed them, one by one, into a custom LAMBDA formula.

=BYROW(array, LAMBDA(row_variable, calculation))

• array: The massive 2D grid of data you want to slice up.
• LAMBDA: The custom formula you want to execute on each slice.

Example 1: Averaging Data Row-by-Row

Let’s use the employee sales example. You have a list of 50 employees. Columns B through M contain their monthly sales data for the entire year.

You want to instantly generate a brand new column that displays the Average Monthly Sale for each specific employee.

You could type =AVERAGE(B1:M1) and physically drag the small green box down 50 rows. But dragging formulas is dangerous, prone to human error, and completely breaks if you add a 51st employee later.

Instead, use BYROW to generate a dynamic, self-spilling array:

=BYROW(B1:M50, LAMBDA(EmployeeData, AVERAGE(EmployeeData)))

How this works:

1. Excel looks at the massive grid (B1:M50) and slices off the very first row (B1:M1).
2. It feeds that single row into the LAMBDA, assigning it the temporary name EmployeeData.
3. The AVERAGE function calculates the average for that specific row and prints the answer on the screen.
4. Excel moves down to the second row (B2:M2) and repeats the process perfectly, looping 50 times.

The result is a perfect, self-contained column of 50 averages that will automatically expand or shrink if you change the initial array size.

Example 2: Analyzing Data Column-by-Column

The BYCOL function operates on the exact same logic, but it slices the grid vertically instead of horizontally.

Using the same sales grid, let’s assume you want to find the single highest sale (the MAX) that occurred in each individual month (Columns B through M), rather than for each employee.

You write the formula underneath the grid:

=BYCOL(B1:M50, LAMBDA(MonthData, MAX(MonthData)))

How this works:

1. Excel slices off the first column (B1:B50 – January).
2. It finds the absolute maximum number inside that specific vertical column and prints the result.
3. It moves one column to the right (C1:C50 – February) and repeats the process.

The result is a perfectly spilled horizontal row displaying the “best sale” for all 12 months, generated by a single, un-breakable formula.