Excel BYCOL Function

BYCOL applies a LAMBDA function to each column of an array and returns one result per column as a spilled row. It's the column-based counterpart to BYROW — useful for column-level summaries, averages, or checks across a wide dataset.

Syntax

BYCOL(array, lambda)

Argument	What it means
array required	The range to process column by column.
lambda required	A LAMBDA function with one parameter (representing each column). Must return a single value.

Examples

Average of each column

=BYCOL(B2:F20, LAMBDA(col, AVERAGE(col)))

Returns a 5-cell row where each cell is the AVERAGE of that column. A single formula replaces 5 separate AVERAGE formulas.

Count non-blank entries per column

=BYCOL(B2:F20, LAMBDA(col, COUNTA(col)))

Check if any column has a value above a threshold

=BYCOL(B2:F20, LAMBDA(col, MAX(col) > 1000))

Returns TRUE or FALSE for each column — useful for quickly flagging which columns have any value above your threshold.

💡 Combine with VSTACK for column summaries below a table

=VSTACK(B2:F20, BYCOL(B2:F20, LAMBDA(col, SUM(col)))) appends a totals row beneath your data as a formula output, leaving the original table untouched.

What version of Excel supports BYCOL?

Excel 365 and Excel for the web only. Not available in Excel 2021 or earlier.

What is the difference between BYCOL and just using SUM on a column?

SUM on a single column gives one total. BYCOL applies the same formula to every column in a multi-column range simultaneously and returns all the results in one spilled row — without writing the formula once per column.

Practice BYCOL for real

Type it in a live spreadsheet, get instant feedback. No videos, no downloads.

Start practising free →