How to use BYCOL and BYROW functions in Excel?

Introduction

The Excel 365 tool offers several easy-to-use functions that allow you to rapidly evaluate the datasets. In this article, we unravel the concept of BYCOL and BYROWS functions in Excel 365. Few operations are not executed properly while using a dynamic array. Sometimes, users may find it challenging to manipulate the functions of each cell in a matrix. To overcome this problem, BYCOL and BYROWS are effectively used with lambda functions and once users can customize the formula, the result will be displayed either in one row or one column.

Illustration of BYCOL function in Excel

Step 1

Open the new worksheet in Excel 365. Assume the sample dataset comprises the production of various fruits in distinct countries.

Step 2

Enter the formula =BYCOL(B2:E5,LAMBDA(total, SUM(total))) in the B6 cell and then press the “Enter” tab.

Explanation

BYCOL(B2:E5,LAMBDA(total, SUM(total)))

• The first argument “B2:E5” represents the range of the specified array.

• The second argument defines the lambda function as having two further parameters inside it. The first parameter “total” specifies the name of the argument and the second argument “SUM(total)” denotes the formula and utilizes the same name that is defined in the first argument. Users may choose any name inside the lambda function.

• After applying the lambda function, the summation of every column is evaluated and returns the calculated value per column.

Step 3

Therefore, the result will be obtained in the range of B6:E7 cells as highlighted in the below image −

BYROWS function in Excel

Step 1

Users need to create the sample dataset to perform the BYROWS function.

Step 2

Write the formula =BYROW(Sheet6!B2:E5,LAMBDA(total,SUM(total))) in the F2 cell as shown in below image −

Explanation

BYROW(Sheet6!B2:E5,LAMBDA(total,SUM(total)))

• Sheet6!B2:E5 − Suppose users work with multiple worksheets. In this case, users must specify the sheet name along with the range of the array.

• LAMBDA(total,SUM(total))) − The name of the lambda function, which takes two arguments, is defined by the second argument of the BYROW function. The first argument represents the argument's name. The users can define any meaningful name. The formula "SUM(total)" indicates that the sum function of the "total" parameter is applied to each row, yielding the sum of the individual rows. Here, the total belongs to the individual rows.

Note − Only one name is to be defined inside the Lambda function.

Step 3

Press the “Enter” tab to get the result as highlighted in the below image. Therefore, the total production of distinct fruits has been evaluated through the BYROW functions.

Conclusion

Users may improve their Excel proficiency by mastering the thorough description of the BYCOL and BYROW functions depicted in both examples. Users must write both formulas carefully to fetch an accurate result otherwise the error will come. These functions save users time and quickly generate the result for every cell either row-wise or column-wise.