# BYCOL function in Excel

> BYCOL runs a LAMBDA on each column of a range and returns one result per column, in a single formula that spills across.

Source: https://spreadsheetpractice.com/functions/bycol · Updated October 28, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on October 30, 2026

## Quick answer

- **What it does:** BYCOL runs a LAMBDA on each column of a range and returns one result per column, in a single formula that spills across.
- **Syntax:** `=BYCOL(array, lambda)`
- **Most common use:** `=BYCOL(B2:D4,LAMBDA(c,SUM(c)))` returns 36 (Each region's total)
- **Available in:** Excel 2024 and Microsoft 365
- **Category:** [Dynamic array functions](https://spreadsheetpractice.com/functions/category/dynamic-arrays)
- **Returns:** A one-row array with one result per column.
- **Google Sheets:** Available in Google Sheets.

## What it does

BYCOL runs a LAMBDA on each column of a range and returns one result per column, in a single formula that spills across. Use it for column totals, averages or spreads under a table, or any per-column calculation you would otherwise copy across.

## Syntax

`=BYCOL(array, lambda)`

| Argument | Required? | What it is |
|---|---|---|
| `array` | Required | The range or array to process column by column. |
| `lambda` | Required | A LAMBDA with one input, the column, that returns a single value. |

## Examples

Each result was calculated by running the formula on the sample sheet.

| Cell | Formula | Result | What it shows |
|---|---|---|---|
| B5 | `=BYCOL(B2:D4,LAMBDA(c,SUM(c)))` | 36 (spills 1 × 3) | Each region's total |
| B6 | `=BYCOL(B2:D4,LAMBDA(c,AVERAGE(c)))` | 12.00 (spills 1 × 3) | Each region's average |
| B7 | `=BYCOL(B2:D4,LAMBDA(c,MAX(c)-MIN(c)))` | 6 (spills 1 × 3) | Each region's range, best minus worst |

### The sample sheet

Every formula above runs on this data:

|  | A | B | C | D |
|---|---|---|---|---|
| 1 | Month | North | South | West |
| 2 | Jan | 12 | 8 | 20 |
| 3 | Feb | 15 | 11 | 7 |
| 4 | Mar | 9 | 14 | 13 |
| 5 |  | 36 | 33 | 40 |
| 6 |  | 12.00 | 11.00 | 13.33 |
| 7 |  | 6 | 6 | 13 |

## Common mistakes

| What you see | Why, and how to fix it |
|---|---|
| #CALC! | The LAMBDA returns more than one value per column. |
| #SPILL! | The cells to the right aren't empty. |

## Tips

- =BYCOL(B2:M50,LAMBDA(c,COUNTBLANK(c))) shows how many gaps each column has.

## Related functions

- [BYROW](https://spreadsheetpractice.com/functions/byrow): BYROW runs a LAMBDA on each row of a range and returns one result per row, all from one spilling formula.
- [MAP](https://spreadsheetpractice.com/functions/map): MAP runs a LAMBDA on each value of an array, or on matching values from several arrays, and returns an array of the same size.
- [LAMBDA](https://spreadsheetpractice.com/functions/lambda): LAMBDA creates your own function from a formula: name the inputs, then write the calculation, as in LAMBDA(price, rate, price*(1+rate)).

## Questions

### How do I total every column with one formula?

=BYCOL(B2:F20,LAMBDA(c,SUM(c))) spills a total under each column.

### What is the difference between BYROW and BYCOL?

BYROW gives one result per row, down a column. BYCOL gives one per column, across a row.

## Practice

A graded BYCOL exercise ("Column totals") is on [the BYCOL page](https://spreadsheetpractice.com/functions/bycol). It is checked instantly, then re-checked on changed data.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.