Overview of the Excel BYROW Function
The Excel BYROW function is a logical function used to apply a LAMBDA to each row in a given array, which returns an array of the results. In other words, if the original array is 3-columns by 4-rows, the returned array is 1-column by 4-rows. For more details, see Microsoft’s official BYROW function documentation.
Syntax
=BYROW(array, lambda)
Arguments
array – An array to be separated by row.
lambda – A LAMBDA that takes a row as a single parameter and calculates a result.
Version
Microsoft 365
Purpose
The Excel BYROW function applies a LAMBDA function to each row in the input array and returns a single result per row. The BYROW function allows the user to process data across an array in a row-by-row fashion. Similarly, the BYCOL function allows the user to process data across an array in a column-by-column fashion. For example – if the input to the BYROW function is a 3-column by 4-row array, it will return a one-dimensional array with 4 values (across 4 rows). 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 BYROW Function
To calculate the sum of each row in an array, we can use the BYROW function:
Let’s use the BYROW function to calculate the sum of each row 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 F4 (in the data set above), we see the BYROW function result in a 1-column by 4-row array. The resultant array from the BYROW function spills across into F5:F7.
Similarly, we can use the BYROW function to apply other calculations to the input array. In this next example, let’s find the min value in each row through using the MIN function. Using the following data set:
We can use the following formula:
Here we see the BYROW function result in a 1-column by 4-row array, with values spilling over from cell F4 again, with the min value from each row in the input array.
Notes
- The BYROW 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 BYROW function will return a #CALC! error
- Official Documentation: For more examples and in-depth details, refer to Microsoft’s official BYROW 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!
