Quick Navigation
BYCOL Function
Summary
The BYCOL function applies a LAMBDA function to each column of an array, returning a single-row array containing the results. This powerful dynamic array function enables column-wise processing of data ranges, making complex operations across multiple columns efficient and readable.
Syntax
BYCOL(array, LAMBDA(column))Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| array | Array |
Yes | Input array or range containing data to process by column |
| lambda | LAMBDA |
Yes | Custom LAMBDA function that receives one column as input and returns one value |
Using the BYCOL Function
BYCOL transforms column-wise operations from verbose formulas into concise, readable expressions. Instead of writing separate formulas for each column or using complex array formulas, BYCOL applies your custom logic to every column automatically, returning results in a single row.
Common BYCOL Examples
Maximum Value Per Column
=BYCOL(A1:C10, LAMBDA(col, MAX(col)))Returns the maximum value from each column in the range A1:C10 as a horizontal array.
Sum of Squared Values
=BYCOL(A1:C10, LAMBDA(col, SUMSQ(col)))Calculates the sum of squared values for each column, useful for statistical analysis.
Column Averages with Error Handling
=BYCOL(A1:D10, LAMBDA(col, IFERROR(AVERAGE(col), "No data")))Returns average of each column, showing 'No data' for columns with errors.
Frequently Asked Questions
Common Errors and Solutions
#VALUE! - Incorrect Parameters
Cause: Invalid LAMBDA syntax or wrong number of parameters
Solution: Ensure LAMBDA takes exactly one parameter (the column)
#CALC!
Cause: LAMBDA doesn't return a single value or no LAMBDA provided
Solution: Verify LAMBDA returns one result per column
#VALUE! - Invalid Array
Cause: First argument is not an array or range
Solution: Use proper array/range reference
Notes
- Input array M×N produces 1×M result array
- LAMBDA must accept one parameter and return one value
- Combines powerfully with other dynamic array functions
- Processes spilled arrays and ranges dynamically
- Ideal for dashboard metrics and column summaries
Compatibility
Available in: Excel 365, Excel 2021
Not available in: Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 365, Excel 2021