Contact
12 programs
Explore the site

BYROW in Excel: Score Each Record with One Formula

Lookup Intermediate Microsoft Excel

Apply a rule to every Excel row using BYROW, with totals, multi-condition checks, shortfall calculations and array-shape troubleshooting.

Verification status: See the evidence and test scope described in this guide. How formula examples are checked

Quick answer: BYROW processes each row as a group and returns one answer per row in a single column. It is useful for checking complete records rather than transforming each individual cell.

Availability and version history

  • First introduced: Preview announced July 26, 2021, for Windows Beta 2108 (build 14312.20008) and Mac 16.52 (build 21072100). These are announced preview minima. Official introduction.
  • Current documented status: the dedicated help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac. Availability depends on the installed build.
  • Evidence checked: October 14, 2026. Official BYROW help. Examples below were independently calculated, not executed in Excel.

Syntax and shape

=BYROW(array,LAMBDA(row,calculation))

The LAMBDA receives the current row as its single named parameter and must return one value. Wrong LAMBDA parameters produce Microsoft’s documented #VALUE! incorrect-parameters error; a non-scalar result can produce #CALC!. BYROW reference

The parameter name is a local label. scores in the following examples is not a named range that must be created in advance. Formulas use English names and comma separators.

Set up three assessment records

Enter A1:D4:

Record Theory Practical Review
A 65 70 80
B 90 55 85
C 50 60 70

All nine scores are numeric. These fictional records demonstrate formula logic only. Use F2:F4 or another unobstructed area for output.

Example 1: total each record

=BYROW(B2:D4,LAMBDA(scores,SUM(scores)))

Expected vertical output: 215, 230, 180. Record A contributes 65+70+80; B contributes 90+55+85; C contributes 50+60+70. The source names and headings are outside the numeric range and are not included.

A single SUM(B2:D4) would instead total the whole block to 625. BYROW changes the grouping, not the meaning of addition.

Example 2: require every score to reach 60

=BYROW(B2:D4,LAMBDA(scores,MIN(scores)>=60))

Expected output: TRUE, FALSE, FALSE, vertically. The minimum score in record A is 65. B has a 55, and C has a 50, so their rows fail the rule even though other scores are higher.

This is different from asking whether at least one score reaches 60. A test based on MAX would answer that other question and would accept all three records here.

Example 3: apply two row-level conditions

=BYROW(B2:D4,LAMBDA(scores,IF(AND(MIN(scores)>=60,AVERAGE(scores)>=70),"Pass","Review")))

Expected output: Pass, Review, Review. Record A has an average of 215/3, about 71.67, and no score below 60. B’s average is above 70 but its minimum fails; C fails the minimum and average criteria.

The rule is deliberately stated in the formula. Before using such a calculation for a real assessment process, confirm that these are the intended rules and that missing scores have an agreed treatment. A blank measurement should not silently acquire a passing interpretation.

Advanced example: quantify the lowest-score shortfall

=BYROW(B2:D4,LAMBDA(scores,MAX(0,60-MIN(scores))))

Expected output: 0, 5, 10. Record A is already above the floor, so MAX prevents a negative shortfall. B needs its lowest score to rise by 5; C needs a rise of 10.

This reports the distance of the lowest score from 60. It is not the number of failed components or the total of every deficit. For example, a row with two low scores could have a larger combined deficit than this single minimum-based measure.

Why returning the row itself is different

=BYROW(B2:D4,LAMBDA(scores,scores)) attempts to return an array for each row rather than one scalar. It violates the documented single-result rule. BYROW is not a general mechanism for returning a separate table from each record.

Use a direct array calculation or an appropriate mapping function when every cell needs its own result. Use BYCOL when the grouping should be by assessment column rather than by record.

Practical checks

  • Verify the input includes only the intended score columns and no existing total.
  • Keep row labels aligned when placing the returned three-value column beside the source.
  • Inspect numeric text and source errors before interpreting a result.
  • If the output cannot expand, move the formula to a clear area rather than deleting unrelated cells.
  • If the range grows, update the boundary or use a carefully scoped table reference.

Verification: Inputs are fictional. Expected values and array shapes were independently calculated from these inputs; no native Excel execution or screenshot is claimed. See the editorial testing standards.