How to Use BYROW Function in Google Sheets

This guide will explain how to use BYROWfunction in Google Sheets.

When we need to group an array by rows by applying a LAMBDA function to each row, we can easily do this using the BYROW function.

The rules for using the BYROW function in Google Sheets are the following:

• The LAMBDA function must have exactly 1 name argument. Otherwise, the function returns an #N/A error. 
• Every row should be grouped into a single value. The function does not support array results for grouped values. 
• The formula_expression argument in the LAMBDA function must use the names inputted in the name argument.  
• We can pass a named function for the LAMBDA parameter. Moreover, there should be exactly 1 argument placeholder defined for it. 

Google Sheets released the LAMBDA function alongside a few Lambda helper functions (LHFs), which can be used to create more complex LAMBDA formulas.

Some Lambda helper functions released are the MAP function, REDUCE function, BYCOL function, BYROW function, SCAN function, and MAKEARRAY function.  

Previously, we had to use a helper column to exclude hidden rows or include visible rows in formulas. Now, the BYROW function lets us perform calculations without using helper columns. 

The BYROW function can expand the result of non-expanding formulas and be used together with the LAMBDA function. 

In this guide, we will provide a step-by-step tutorial on how to use the BYROW function in Google Sheets. Additionally, we will explore the syntax and a real example of using the function.

Great! Let’s dive right in.

The Anatomy of the BYROW Function

The syntax or the way we write the BYROW function is as follows:

=BYROW(array_or_range,LAMBDA)

• = the equal sign is how we start any function in Google Sheets.
• BYROW() refers to our BYROW function. This function operates on an array or range and returns a new column array created by grouping each row to a single value. 
• array_or_range is a required argument. This refers to the array or range we want to group by rows.
• LAMBDA is another required argument. This is the LAMBDA we want to apply to each row in the given array or range to get its grouped value. 

The Anatomy of the LAMBDA Function

The syntax or the way we write the LAMBDA function when using it with the REDUCE function is as follows:

=LAMBDA(name,formula_expression)

• = the equal sign is how we activate any function in Google Sheets.
• LAMBDA() is our LAMBDA function. This function is used to create and return a custom function with a set of names and a formula that uses them.
• name is a required argument. This refers to the name to be used inside the formula_expression argument. We must have exactly 1 name argument.
• formula_expression is a required argument. This refers to the formula we want to calculate or execute. We need to use the name we inputted in the name argument. 

Note: A different syntax exists when using the LAMBDA function as a standalone. Please refer to this guide to learn about the complete anatomy of the LAMBDA function. 

A Real Example of Using BYROW Function in Google Sheets

Let’s say we have a data set containing the monthly sales of three different stores from January to May. Our initial data set would look like this:

In the spreadsheet above, we can see the three stores, the months, and the monthly sales of each store. For example, we want to calculate the average monthly sales of each store. 

Before we can use the BYROW function, we first need to create our LAMBDA formula. 

We will use the LAMBDA formula:

=LAMBDA(sale,AVERAGE(sale))

The first part of the formula is our name argument. Since we must only have 1 name argument, we used sale. 

Our goal is to calculate the average sale of each store. Thus, we used the AVERAGE function and the same name argument for our formula_expression.

Then, we can place our LAMBDA formula inside a BYROW function:

=BYROW(B2:F4,LAMBDA(sale,AVERAGE(sale)))

The first part of the formula is the range containing the rows we want to apply the LAMBDA formula to. Lastly, we simply type in our LAMBDA formula. 

Although we can use three separate AVERAGE functions to perform the calculation, the BYROW function operates more efficiently. 

Firstly, we pass an array of data to the BYROW function using the array_or_range argument. Then, it passes each row into the LAMBDA formula to calculate a single value for that row. 

Lastly, the BYROW function returns these values in a column array with the same number of rows as the given input array.

Our final data set would look like this:

You can make your own copy of the spreadsheet above using the link below. 

Amazing! Now we can dive into the steps of using the BYROW function in Google Sheets.

How to Use BYROW Function in Google Sheets

1. First, we will create a new column in the table to display the results.

2. In the first row of the new column, we will type in an equal sign and the BYROW function to start our formula “=BYROW(”.

3. We will select the range containing the rows we want to group and calculate. In this case, our formula would become “=BYROW(B2:F4”.

4. Next, we will type in the LAMBDA function. This will make our formula “=BYROW(B2:F4,LAMBDA(”.

5. We will input “sale” as our name argument. Our formula will become “=BYROW(B2:F4,LAMBDA(sale”.

6. Since we are calculating the average sales, we will input the AVERAGE function in our formula “=BYROW(B2:F4,LAMBDA(sale,AVERAGE(”.

7. Then, we will type in “sale” in our formula_expression argument. Our final formula would be “=BYROW(B2:F4,LAMBDA(sale,AVERAGE(sale)))”.

8. Lastly, we will press the Enter key to return the result.

And tada! We have successfully used the BYROW function in Google Sheets.

You can apply this guide whenever you need to group an array by rows by applying a LAMBDA function to each row. You can now use the BYROW function and the various other Google Sheets formulas available to create great worksheets that work for you.

FAQs:

1. What is the common error in using the BYROW function?

One common error is the given LAMBDA function does not have exactly 1 name argument and 1 formula_expression as an argument.

When this occurs, the function will return “Wrong number of arguments to LAMBDA. Expected 1 argument, but got 2 arguments.”

2. Do I have to use Lambda helper functions with the LAMBDA function?

Yes and No. In Google Sheets, we can use the LAMBDA function as a standalone to create a custom function and also use it with Lambda helper functions. 

However, we must use the LAMBDA function with Lambda helper functions to create custom functions in Excel.

That’s pretty much it! Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive early access to new content.

Subscribe