Overview of the Excel BYCOL Function
The Excel BYCOL function is a logical function used to apply a LAMBDA to each column in a given array, which returns an array of the results. In other words, if the original array is 4-columns by 3-rows, the returned array is 4-columns by 1-row. For more details, see Microsoft’s official BYCOL function documentation.
Syntax
=BYCOL(array, lambda)
Arguments
array – An array to be separated by column.
lambda – A LAMBDA that takes a column as a single parameter and calculates a result.
Version
Microsoft 365
Purpose
The Excel BYCOL function applies a LAMBDA function to each column in the input array and returns a single result per column. The BYCOL function allows the user to process data across an array in a column-by-column fashion. Similarly, the BYROW function allows the user to process data across an array in a row-by-row fashion. For example – if the input to the BYCOL function is a 4-column by 3-row array, it will return a one-dimensional array with 4 values (across 4 columns). The array that is returned will be the result of a calculation performed on the input array, through use of a LAMBDA.
Examples of the Excel BYCOL Function
To calculate the sum of each column in an array, we can use the BYCOL function:
Let’s use the BYCOL function to calculate the sum of each column in an array on the following data set. It should be noted that the ‘array’ variable used in the LAMBDA function can be named anything you wish. It does not need to be called ‘array’.
We can use the following formula:
When this formula is entered into cell B8 (in the data set above), we see the BYCOL function result in a 4-column by 1-row array. The resultant array from the BYCOL function spills across in C8:E8.
Similarly, we can use the BYCOL function to apply other calculations to the input array. In this next example, let’s find the max value in each column through using the MAX function. Using the following data set:
We can use the following formula:
Here we see the BYCOL function result in a 4-column by 1-row array, with values spilling over from cell B8 again, with the max value from each column in the input array.
Notes
- The BYCOL function uses input arrays; data ranges on their own will not work
- The LAMBDA function must return a single result for each column, otherwise the BYCOL function will return a #CALC! error
- Official Documentation: For more examples and in-depth details, refer to Microsoft’s official BYCOL function documentation.
Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has been consulting on and teaching best practices in Microsoft Excel to over 400 companies across 30+ countries.
Want to learn more?
Join My Excel Academy Today!
This All-Access Pass Includes:
- Access to All My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
30-Day Money-Back Guarantee
Want to learn more?
Join My Excel Academy Today!
All-Access Pass Includes:
- Access to All My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
30-Day Money-Back Guarantee
Want to learn more?
Join My Excel Academy Today!
30-Day Money-Back Guarantee
This all-access pass includes:
- Access to all My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
Don’t hesitate – join now!
