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.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
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.