The Challenge of Row-by-Row Calculations
Dynamic array functions revolutionized Microsoft Excel. When you use a function like UNIQUE or SORT, you select a large range of data, press enter, and the result “spills” down the column automatically. You no longer have to manually click and drag a formula down 500 rows.
However, applying a complex mathematical calculation (like finding the maximum value or calculating a moving average) to each individual row within a dynamic array used to be impossible. If you fed a 10-row array into the MAX function, Excel would just return one single number: the maximum value of the entire block. It wouldn’t return 10 different numbers representing the maximum value of each row.
To solve this, Excel introduced the BYROW function. It forces Excel to take a dynamic array, look at the first row, run a custom LAMBDA calculation on it, output the result, and then move down to the next row and repeat the process.
Understanding the Syntax
The syntax for BYROW relies entirely on a custom LAMBDA function to do the heavy lifting.
=BYROW(array, LAMBDA(row_variable, calculation))
- array: The multi-column range of data you want to analyze row by row (e.g., A1:D10).
- LAMBDA: The required helper function.
- row_variable: A name you invent (like “r” or “myRow”) to represent the current row being processed.
- calculation: The math you want to perform on that specific row.
Example 1: Finding the Maximum Value Per Row
Assume you are a teacher. You have a spreadsheet where Column A is the student’s name. Columns B, C, and D contain their scores for Test 1, Test 2, and Test 3. The data runs from row 2 to row 30.
You want to create a new column that displays each student’s highest test score. Without BYROW, you would type =MAX(B2:D2) and drag it down 29 times. If you add a new student to row 31, you have to drag it down again.
With BYROW, you write one single formula in row 2:
=BYROW(B2:D30, LAMBDA(row, MAX(row)))
How it works:
- Excel looks at the first row of the array (B2:D2).
- It temporarily names that specific chunk of data “row”.
- It runs
MAX(row), finding the highest score for the first student, and outputs it. - It then automatically moves down to B3:D3, names it “row”, finds the max, and outputs it directly below the first answer.
- It spills all 29 answers instantly.
Example 2: Combining Text Across Rows
BYROW is not just for math; it is exceptionally useful for text manipulation. Assume Columns A, B, and C contain a person’s First Name, Middle Initial, and Last Name. Some people do not have a middle initial, leaving Column B blank.
If you use a standard TEXTJOIN formula and drag it down, it works, but it isn’t dynamic. By wrapping TEXTJOIN inside BYROW, you can generate a single dynamic array of perfectly formatted full names:
=BYROW(A2:C100, LAMBDA(r, TEXTJOIN(" ", TRUE, r)))
In this formula, TEXTJOIN uses a space (” “) as the delimiter, TRUE tells it to ignore the blank cells of people without middle initials, and r represents the current row of three cells being processed.
By mastering BYROW, you can completely eliminate the need to manually drag formulas down columns, making your Excel workbooks fully dynamic, resilient, and significantly less prone to human error.