Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to use BYROW and BYCOL to apply a LAMBDA per row or column in Excel and Google Sheets
Topic:LAMBDA (reusable formulas)Excel & Google Sheets
=BYROW(A2:C5, LAMBDA(row, SUM(row)))Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Build your data table with values in A2:C5 (3 columns, 4 data rows — e.g. quarterly sales by region).
- 2In D2, enter =BYROW(A2:C5, LAMBDA(row, SUM(row))) to get the row total for each of the 4 rows. The result spills into D2:D5.
- 3For a per-column result, enter =BYCOL(A2:C5, LAMBDA(col, AVERAGE(col))) in A7 to get the average of each column. Results spill into A7:C7.
- 4Inside the LAMBDA, the argument receives the whole row (or column) as an array — you can use any array-aware function: SUM, MAX, COUNTA, etc.
- 5Combine BYROW and BYCOL in one sheet to produce both row totals and column totals from the same source data.
Tips
- The LAMBDA body must return a scalar (single value) for BYROW/BYCOL — returning an array from the LAMBDA causes a #VALUE! error.
- BYROW and BYCOL are available in Microsoft 365 and Google Sheets; they are absent from Excel 2019 and earlier.
- For Sheets users who need BYCOL-style averages in older syntax, ARRAYFORMULA(AVERAGEIF(...)) is a common workaround.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Can I use BYROW for conditional row logic, like flagging rows where any cell exceeds a threshold?
Yes: =BYROW(A2:C5, LAMBDA(row, IF(MAX(row)>100, "Flag", "OK"))). The LAMBDA receives the full row array, so MAX, MIN, COUNT, and any aggregate work inside it.
Does BYROW work on a single column?
Yes, but for a single column each "row" is just one cell, making BYROW equivalent to MAP. Use MAP for single-column transformations — it is clearer.
What is the output orientation of BYCOL?
BYCOL returns a single row (horizontal array), one result per column of the input. Use TRANSPOSE if you need it as a vertical column.
More on LAMBDA (reusable formulas)
See all →Reference
LAMBDA
Defines a reusable custom function using named parameters and a formula body, letting you call it like any built-in function — no VBA or scripts needed.
How-toHow to write a recursive LAMBDA function
Build self-referencing recursive functions using LAMBDA with the Excel Name Manager or Google Sheets named functions — for tasks like factorial, Fibonacci, or tree traversal.
How-toHow to use LAMBDA with MAP across a range
Use MAP with a LAMBDA to apply a custom formula to every cell in a range in Excel 365 and Google Sheets, returning a transformed array without helper columns.
How-toHow to use LAMBDA with REDUCE for cumulative math
Use REDUCE with a LAMBDA to fold a range into a single accumulated value in Excel 365 and Google Sheets — perfect for running totals, products, or custom aggregations.
How-toHow to use LAMBDA with SCAN for a running balance
Use SCAN with a LAMBDA to compute a running balance or cumulative total in Excel 365 and Google Sheets, returning one result per row without helper columns.
How-toHow to use MAKEARRAY with LAMBDA
MAKEARRAY generates a 2-D array of any size in Excel 365 and Google Sheets by calling a LAMBDA with each row and column index, letting you build multiplication tables, calendars, or computed grids.