How to Use the BYROW and BYCOL Functions in Google Sheets

In data analysis workflows, spreadsheet users frequently need to apply calculations across entire rows or columns—such as calculating maximum values across quarterly projections, evaluating logical criteria per employee record, or summing multi-department expenses. Traditionally, users drag formulas down hundreds of rows, creating cluttered workbooks that are prone to reference errors when new records are appended.

Google Sheets introduced BYROW and BYCOL, two modern Lambda Helper Functions (LHFs). These functions process multi-dimensional data arrays row by row or column by column, applying custom lambda calculations and returning cleanly spilled dynamic array results from a single formula cell.

Understanding BYROW Syntax and Operation

The BYROW function iterates through each row of a specified array, passes that row as a parameter into a custom LAMBDA calculation, and outputs a vertical single-column array of results:

BYROW(array, LAMBDA(row, formula_expression))

• array: The rectangular range of cells you wish to evaluate (e.g. B2:E20).
• LAMBDA(row, ...): A custom lambda function declaration where row serves as the variable name representing the current row slice being processed.
• formula_expression: The mathematical or logical calculation applied to the row variable.

Calculating Row-Wise Maximums and Averages

Suppose cells B2:E20 contain quarterly sales figures (Q1, Q2, Q3, Q4) for 19 regional teams, and you want to calculate the maximum quarterly revenue achieved by each team in column F:

=BYROW(B2:E20, LAMBDA(r, MAX(r)))

Entering this single formula into cell F2 automatically computes and spills the maximum value for each team across rows 2 through 20. If you instead want to calculate the average quarterly revenue for each team, simply substitute the calculation:

=BYROW(B2:E20, LAMBDA(r, AVERAGE(r)))

Understanding BYCOL Syntax and Column Aggregation

While BYROW operates horizontally across rows, BYCOL operates vertically down columns, outputting a horizontal single-row array of results:

BYCOL(array, LAMBDA(col, formula_expression))

To calculate the total sum for each quarterly column in range B2:E20:

=BYCOL(B2:E20, LAMBDA(c, SUM(c)))

Entering this formula in cell B21 instantly spills the four column sums horizontally across cells B21:E21.

Conditional Evaluation with BYROW and Logic Functions

BYROW becomes exceptionally powerful when combined with logical checks to flag anomalous records:

Suppose you want to return “Review” if any quarterly value in a row falls below £5,000, or “On Target” if all quarters meet the threshold:

=BYROW(B2:E20, LAMBDA(r, IF(MIN(r) < 5000, "Review", "On Target")))

Because the formula lives in a single master cell, updating the range or modifying business criteria takes effect immediately across all records without manually copying formulas down the sheet.