The Excel BYCOL function hands each entire column of a range to a LAMBDA and spills one result per column, across a single row. It is the one-formula totals row: SUM each month, MAX each column, count passes per test — without copying a formula sideways. Its twin BYROW works the same way, row by row.
c receives a whole column (three cells at once), so put an aggregation like SUM, MAX, or AVERAGE inside.
Syntax
| Argument | Description | |
|---|---|---|
array | Required | The range or array to process column by column. |
lambda | Required | A one-parameter LAMBDA. The parameter receives one complete column as a vertical vector — not a single cell — and the body must boil it down to a single value. |
Available in: Excel for Microsoft 365 and Excel for the web only. Older versions show #NAME?. The output is a one-row array spilling sideways — one value per input column.
A totals row from one formula
Sales per rep sit in B2:E4, one column per month. One formula in B5 builds the entire totals row:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Jan | Feb | Mar | Apr |
| 2 | Ava | 120 | 135 | 150 | 140 |
| 3 | Ben | 95 | 110 | 105 | 125 |
| 4 | Caro | 140 | 120 | 160 | 155 |
| 5 | Total | 355 | 365 | 415 | 420 |
Inside the LAMBDA, c is the whole column — B2:B4 on the first pass, C2:C4 on the second. Any range aggregation fits:
Try it: aggregate each column live
Choose what the LAMBDA does to each column and watch the spilled row update.
Per-column logic and dynamic-array combos
Verdicts per column work just like aggregates — any single-value expression goes in the body:
Feed BYCOL’s spilled row to other functions. Which month had the highest total?
Cleaner with LET, which computes the totals once:
Family map: per-row results are BYROW; element-by-element transforms are MAP; one grand total with custom step logic is REDUCE; building a grid from row/column indexes is MAKEARRAY.
Errors & common pitfalls
#CALC! — the LAMBDA returns an array, not a single value. Each column must collapse to one result. LAMBDA(c, c*2) returns a whole column of values per column — BYCOL can’t place it. To transform every cell, use MAP.
#CALC! — wrong parameter count. BYCOL’s LAMBDA takes exactly one parameter (the column vector). Declaring two, or none, fails.
#NAME? — older Excel. BYCOL exists only in Excel for Microsoft 365 and Excel for the web. Excel 2021 and earlier don’t recognize it.
Pitfall: the parameter is a vector, not a cell. LAMBDA(c, c>100) compares the whole column at once and yields an array — wrap it in an aggregate: LAMBDA(c, SUM(c)>100).
Pitfall: it spills sideways. BYCOL returns a horizontal row — the cells to the right of the formula must be empty, or you get #SPILL!. Wrap the call in TRANSPOSE if you want the results vertical.
Practice workbook
Frequently asked questions
What does the Excel BYCOL function do?
What's the difference between BYCOL and BYROW?
Why does BYCOL return #CALC!?
c*2), or the LAMBDA doesn't declare exactly one parameter. Each column must reduce to one result — wrap it in SUM, MAX, COUNTIF, or similar.How do I make BYCOL results vertical?
=TRANSPOSE(BYCOL(B2:E4, LAMBDA(c, SUM(c)))) — handy when the results feed a vertical report.Which Excel versions support BYCOL?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class