Google Sheets Vs Excel: BYROW – The Key Difference

If you use Excel and Google Sheets and leverage the BYROW function, there’s a key difference to understand.

In Google Sheets, the BYROW function can return a two-dimensional array output. However, in Excel, it cannot.

The purpose of the BYROW function in both Excel and Google Sheets remains the same: applying a user-defined lambda function to an array separated by rows. The lambda function calculates and returns a single value for a row, and BYROW repeats this calculation for each row in the specified array.

Excel adheres to this principle. However, in Google Sheets, the BYROW function has the added capability of returning a two-dimensional array.

Before we delve into the key difference between the BYROW function in Excel and Google Sheets, let’s explore their similarities in operation. We’ll then illustrate this difference with three examples below.

Understanding the Common Ground: BYROW Operation in Both Platforms

We have the following data in A1:C2 in both Excel and Google Sheets:

5410
154

The following formula in cell D1 will return 19 (5+4+10) in cell D1 and 10 (1+5+4) in cell D2:

=BYROW(A1:C2, LAMBDA(r, SUM(r)))

The above formula works both in Excel and Google Sheets.

Now let’s take the same data. How do we sort them in ascending order row by row and return the following result?

4510
145

In Excel, the BYROW function can’t return a 2-dimensional array, so we can rule out that possibility. However, in Google Sheets, we can achieve this. With the following examples, you can understand the difference between BYROW in Excel and Google Sheets.

BYROW in Excel Vs BYROW in Google Sheets

The following formulas will work only in Google Sheets. That’s the added advantage of the BYROW function in Google Sheets.

BYROW with SORT

The following formula will sort each row and return a two-dimensional array:

=BYROW(A1:C2, LAMBDA(r, TRANSPOSE(SORT(TRANSPOSE(r)))))

How does this formula work?

At the time of writing this tutorial, the SORT function cannot sort a row in Google Sheets (though it’s possible in Excel). So, we first transpose the row values, sort them, and then transpose them back.

I’ve already explained this in one of my earlier tutorials here: How to Sort Horizontally (Columns Left to Right) in Google Sheets.

Here is the lambda function to sort a single row:

=LAMBDA(r, TRANSPOSE(SORT(TRANSPOSE(r)))) (A1:C1)

Use this function within BYROW after removing the function call, which is (A1:C1), and that is the above BYROW formula.

You can read more about this here: Row-Wise Sorting in a 2-D Array in Google Sheets.

BYROW with RANK

Here is another comparison where BYROW works in Google Sheets but not in Excel. In this case, as well, the formula returns a two-dimensional array.

Sample Data (A1:C2):

302524
252824

The RANK function returns the rank of a specified value in a dataset. To obtain the rank of the array, utilize ARRAYFORMULA with it:

For instance, the following formula will return the rank of values in A1:C1:

=ARRAYFORMULA(RANK(A1:C1, A1:C1))

Output:

123

Now, let’s write a Lambda function for this calculation:

=LAMBDA(r, ARRAYFORMULA(RANK(r, r))) (A1:C1)

Use this Lambda function within BYROW for the array A1:C2 as follows:

=BYROW(A1:C2, LAMBDA(r, ARRAYFORMULA(RANK(r, r))))

Output:

123
213

BYROW with SEQUENCE

Here is another example to explain the difference between BYROW in Excel and Google Sheets.

Assume you have the numbers 5, 4, and 1 in cells A1:A3. The following SEQUENCE formula will return the sequence numbers 1 to 5 in a row.

=SEQUENCE(1, A1)

Here is the equivalent Lambda function.

=LAMBDA(r, SEQUENCE(1, r))(A1)

We can use the array A1:A3 in BYROW and the above lambda function to generate a sequence in each row as follows.

=BYROW(A1:A3, LAMBDA(r, SEQUENCE(1, r)))
12345
1234
1

This works in Google Sheets but not in Excel.

Trying the above formulas that return a two-dimensional array in Excel will result in the #CALC error.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Days Between Weekday Names in Excel and Google Sheets

There isn't a specific function to calculate the number of days between weekday names...

Display Month Names Only at Month Start (Excel & Google Sheets)

This tutorial explains how to display month names only at the start of each...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.