Ravindra BagaleCourses & study guides Track your progress

Excel BYROW Function

By · Last updated:

Chala mitrano — today we learn BYROW. Read Why → What → How, then try the practice list.

Version note. Microsoft 365 with LAMBDA.
Fictional sample data for learning. Fictional student scores and stock grids.

Purpose

Run a LAMBDA once per row of an array and spill one result per row.

Return value

A column (usually) of per-row results.

Syntax

BYROW(array, lambda)

Arguments

ArgumentRequired?Description
arrayRequired2D range or array.
lambdaRequiredLAMBDA(row) that returns a single value (or row result).

Version

Microsoft 365 with LAMBDA.

How to use — Why → What → How

Why: Row totals, row max, or row-level checks without helper columns.

What: Each row is passed as a vector into your LAMBDA.

How — numbered steps:

  1. Example: =BYROW(B2:D10, LAMBDA(r, SUM(r))).
  2. Use MAX/MIN/AVERAGE the same way.
  3. Keep the LAMBDA returning one value per row for a clean column spill.
  4. Pair with HSTACK to glue results beside the data.
  5. Test on 3 rows first.

Ravindra Bagale's Tip

BYROW = row-wise; BYCOL = column-wise. Do not mix them up in interviews.

Ravindra Bagale's Tip

If you need many outputs per row, consider MAP or a wider LAMBDA carefully.

Worked examples

1) Row totals

Qty table B2:D5.

Formula: =BYROW(B2:D5, LAMBDA(r, SUM(r)))

Result: One total per row

What it means: SUM across each row vector.

2) Row max score

Tests.

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

Result: Best score each student

What it means: MAX per row.

3) All cells > 0?

Stock checks.

Formula: =BYROW(B2:D8, LAMBDA(r, IF(MIN(r)>0,"OK","Gap")))

Result: OK/Gap

What it means: Row quality flag.

4) Average spend (£)

Leeds cafe categories.

Formula: =BYROW(B2:D6, LAMBDA(r, ROUND(AVERAGE(r),2)))

Result: Row averages

What it means: Currency-friendly ROUND.

5) Count blanks in row

Dirty import.

Formula: =BYROW(A2:F30, LAMBDA(r, COUNTBLANK(r)))

Result: Blank counts

What it means: Cleaning helper.

Common errors

#CALC!: LAMBDA must accept the row array.
#SPILL!: Blocked output.

Practice tasks (10+)

  1. Task 1. Row SUM for a 5×3 block.
    Hint: try on a tiny range first
  2. Task 2. Row MAX and row MIN.
    Hint: try on a tiny range first
  3. Task 3. Flag rows where SUM>1000.
    Hint: try on a tiny range first
  4. Task 4. BYROW + TEXTJOIN for a row label (advanced).
    Hint: try on a tiny range first
  5. Task 5. Compare BYROW SUM vs MMATH on rows.
    Hint: try on a tiny range first
  6. Task 6. HSTACK original data with BYROW totals.
    Hint: try on a tiny range first
  7. Task 7. Interview: BYROW vs MAP.
    Hint: try on a tiny range first
  8. Task 8. Fix arity error in LAMBDA(r, ...).
    Hint: try on a tiny range first
  9. Task 9. BYROW on a table column set (structured refs carefully).
    Hint: try on a tiny range first
  10. Task 10. Bonus: BYROW with LAMBDA that uses INDEX(r,1).
    Hint: try on a tiny range first

Quick quiz

Q1. BYROW applies a LAMBDA to each row.
Q2. BYROW is the same as BYCOL.
Q3. Needs Microsoft 365.
Q4. Typical result is one value per row.

Answers: Q1=True, Q2=False, Q3=True, Q4=True

Related: LAMBDA · LET · GROUPBY · Function hub · Learning path