Can BYCOL be used to sort each column individually?

No. BYCOL applies a function to whole columns but does not rearrange data. To sort a column, use the SORT function on the desired range.

How does BYCOL treat empty cells in the source array?

Empty cells are passed to the lambda as blanks. Functions like COUNT or AVERAGE will ignore them, while text functions may treat them as empty strings.

Is BYCOL available in older versions of Excel?

BYCOL is part of the dynamic-array functions introduced in Excel 365 and Excel for the web. It is not available in perpetual-license Excel versions prior to 2019.

Can BYCOL handle non-contiguous ranges?

Yes, you can pass a union of ranges (e.g., (A1:C5, E1:G5)) as the array argument. BYCOL will treat the combined area as a single array, applying the lambda to each resulting column.

BYCOL function

BYCOL applies a LAMBDA to each column of an array, returning a new array that aggregates or transforms column-wise results.

=BYCOL(array, lambda)

Generate a BYCOL formula

Describe what you need. The generator will reach for BYCOL where BYCOL is the right tool, and tell you when it is not.

Arguments

How BYCOL reads its arguments

Argument	Required	Description
array	Required	A range or literal array; BYCOL will feed each column of this array to the lambda, and empty cells are treated as blanks.
lambda	Required	A LAMBDA that receives a single column (as a vertical array) and must return a value or array; mismatched return sizes cause errors.

Returns

Returns a one-dimensional or two-dimensional array whose shape matches the lambda outputs for each column.

Availability

Excel: 365 · Google Sheets: Supported

Worked examples

1. Length of each header

EmpID	Name	Dept	HireDate	Salary	Status
101	Alice	Sales	2020-03-15	75000	Active
102	Bob	Engineering	2019-07-01	90000	Active
103	Carol	HR	2021-01-20	65000	On Leave
104	David	Sales	2018-11-30	80000	Active
105	Eve	Engineering	2022-05-10	72000	Inactive

=BYCOL({{"EmpID","Name","Dept","HireDate","Salary","Status"};{101,"Alice","Sales","2020-03-15",75000,"Active"};{102,"Bob","Engineering","2019-07-01",90000,"Active"};{103,"Carol","HR","2021-01-20",65000,"On Leave"};{104,"David","Sales","2018-11-30",80000,"Active"};{105,"Eve","Engineering","2022-05-10",72000,"Inactive"}}, LAMBDA(col, LEN(INDEX(col,1))))

Result: 544866

The lambda receives each column, extracts the first cell (the header) with INDEX(col,1), and measures its length with LEN. BYCOL runs this on all six columns, producing a row of header lengths: EmpID (5), Name (4), Dept (4), HireDate (8), Salary (6) and Status (6).

2. Average numeric columns, N/A for text columns

EmpID	Name	Dept	HireDate	Salary	Status
101	Alice	Sales	2020-03-15	75000	Active
102	Bob	Engineering	2019-07-01	90000	Active
103	Carol	HR	2021-01-20	65000	On Leave
104	David	Sales	2018-11-30	80000	Active
105	Eve	Engineering	2022-05-10	72000	Inactive

=BYCOL({{"EmpID","Name","Dept","HireDate","Salary","Status"};{101,"Alice","Sales","2020-03-15",75000,"Active"};{102,"Bob","Engineering","2019-07-01",90000,"Active"};{103,"Carol","HR","2021-01-20",65000,"On Leave"};{104,"David","Sales","2018-11-30",80000,"Active"};{105,"Eve","Engineering","2022-05-10",72000,"Inactive"}}, LAMBDA(col, IF(ISNUMBER(INDEX(col,2)), AVERAGE(col), "N/A")))

Result: 103N/AN/AN/A76400N/A

The lambda checks the second row of each column; if it is numeric, the whole column is averaged. EmpID and Salary contain numbers, so their averages are 103 and 76,400 respectively. All other columns start with text, so the lambda returns "N/A" for them. BYCOL assembles these six results into a single row.

3. Count of Active status per column

EmpID	Name	Dept	HireDate	Salary	Status
101	Alice	Sales	2020-03-15	75000	Active
102	Bob	Engineering	2019-07-01	90000	Active
103	Carol	HR	2021-01-20	65000	On Leave
104	David	Sales	2018-11-30	80000	Active
105	Eve	Engineering	2022-05-10	72000	Inactive

=BYCOL({{"EmpID","Name","Dept","HireDate","Salary","Status"};{101,"Alice","Sales","2020-03-15",75000,"Active"};{102,"Bob","Engineering","2019-07-01",90000,"Active"};{103,"Carol","HR","2021-01-20",65000,"On Leave"};{104,"David","Sales","2018-11-30",80000,"Active"};{105,"Eve","Engineering","2022-05-10",72000,"Inactive"}}, LAMBDA(col, COUNTIF(col, "Active")))

Result: 000003

COUNTIF scans each column for the exact text "Active". Only the Status column contains that value three times (Alice, Bob, David); all other columns have none, so BYCOL returns a row of zeros except for the final column, which shows 3.

Common errors

Which BYCOL error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	One lambda returns a column-sized array while another returns a single scalar, so BYCOL cannot align the results across columns.	Make every lambda return the same sized array (e.g., always wrap scalar results in a one-cell array).
#SPILL!	The BYCOL formula is placed where the resulting array would overwrite existing data, preventing the spill.	Move the formula to an empty range or clear the obstructing cells so the full result can spill.
#N/A	A lookup inside the lambda (such as XLOOKUP) fails to find a match for a particular column, propagating the #N/A to BYCOL's output.	Wrap the lookup in IFERROR or supply the optional if_not_found argument to return a fallback value.

Tips and when to use something else

Use BYROW when you need to operate across rows instead of columns.
Combine BYCOL with LET to store the source roster once and improve performance.
If you only need a single aggregated value (e.g., total salary), consider REDUCE instead of BYCOL.
When you want to retrieve a matching record by employee ID, XLOOKUP or VLOOKUP is more appropriate than BYCOL.

Related functions

BYROWBYROW applies a LAMBDA to each row of a range and returns a vertical array of the results, enabling row-wise calculations in Excel 365 and Google Sheets.MAPMAP applies a custom LAMBDA to each element of a range, returning a new array of the same shape with the calculated results.LAMBDALAMBDA lets you create custom, reusable functions directly in a cell, returning any value or array based on supplied parameters.REDUCEREDUCE iteratively applies a LAMBDA to each element of an array, carrying forward an accumulator that starts with the initial value.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.

Frequently asked questions

Can BYCOL be used to sort each column individually?: No. BYCOL applies a function to whole columns but does not rearrange data. To sort a column, use the SORT function on the desired range.
How does BYCOL treat empty cells in the source array?: Empty cells are passed to the lambda as blanks. Functions like COUNT or AVERAGE will ignore them, while text functions may treat them as empty strings.
Is BYCOL available in older versions of Excel?: BYCOL is part of the dynamic-array functions introduced in Excel 365 and Excel for the web. It is not available in perpetual-license Excel versions prior to 2019.
Can BYCOL handle non-contiguous ranges?: Yes, you can pass a union of ranges (e.g., (A1:C5, E1:G5)) as the array argument. BYCOL will treat the combined area as a single array, applying the lambda to each resulting column.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator