The BYCOL function varies slightly between Google Sheets and Excel but remains true to its core purpose. It applies a lambda to each column in an array, which is why it’s also referred to as a lambda helper function.
In Google Sheets, BYCOL can return two-dimensional arrays, unlike in Excel.
We can utilize the ARRAYFORMULA function with the BYCOL function in Google Sheets to obtain a two-dimensional array.
This feature proves useful in situations where you need to extract the largest or smallest two values in each column simultaneously.
The primary objective of the BYCOL function in both Excel and Google Sheets is to apply a lambda function for a column to each column in a range, separated by columns.
Consequently, the output will be a row of values equivalent to the number of columns in the source array.
The key difference between BYCOL in Excel and Google Sheets is that in the latter, the function can sometimes return multiple rows. We’ll explore this with two examples shortly.
Now, let’s delve into how they operate similarly in both Excel and Google Sheets.
Note: BYCOL utilizes LAMBDA functions, a feature available in Microsoft 365 and Excel for the Web. This allows for BYCOL-like functionality in these versions of Excel.
Finding Common Ground: BYCOL in Google Sheets & Excel
Let’s simplify by using a 4 x 3 matrix in both Excel and Google Sheets, placed in the range A1:C4:
| 5 | 10 | 5 |
| 4 | 20 | 6 |
| 6 | 30 | 5 |
| 2 | 4 | 4 |
To total each column and return the individual results in cells A5:C5, we can utilize the following BYCOL formula, which works in both Excel and Google Sheets:
=BYCOL(A1:C4, LAMBDA(col, SUM(col)))If you need a quick explanation:
=SUM(A1:C4) // sums the range A1:C4.We’ll convert this to a lambda function:
=LAMBDA(col, SUM(col))(A1:A4)We’ll apply this lambda function to the array A1:C4 instead of A1:A4. BYCOL does precisely that.
The above formula remains similar in Google Sheets and Excel. However, the key difference is an additional capability in Google Sheets.
How BYCOL Differs in Excel and Google Sheets
Here are two examples that illustrate the key difference between BYCOL in Google Sheets and Excel.
Example #1: Using LARGE Function
Let’s consider the following sample data in cells A1:F4 in both Google Sheets and Excel:
| Athlete 1 | Athlete 2 | Athlete 3 | Athlete 4 | Athlete 5 | |
| Attempt 1 | 6.8 | 7.1 | 6.8 | 7.2 | 6.4 |
| Attempt 2 | 7.1 | 7.2 | 7 | 6.9 | 6.7 |
| Attempt 3 | 7.2 | 7 | 6.9 | 7.5 | 6.9 |
This data represents the distances achieved by 5 athletes in a long jump event across three attempts.
To find the longest distance of each athlete across their attempts, we can use the following BYCOL formula in cell B5 in both Excel and Sheets:
=BYCOL(B2:F4, LAMBDA(score, MAX(score)))Output:
| 7.2 | 7.2 | 7 | 7.5 | 6.9 |
BYCOL can handle complex array operations, but finding the top two scores for each athlete requires a different approach.
In Google Sheets, we can utilize the following formula that takes advantage of its unique functionalities:
=BYCOL(B2:F4, LAMBDA(score, ARRAYFORMULA(LARGE(score, VSTACK(1, 2)))))Output:
| 7.2 | 7.2 | 7 | 7.5 | 6.9 |
| 7.1 | 7.1 | 6.9 | 7.2 | 6.7 |
However, this formula won’t work in Excel, as Excel’s BYCOL doesn’t support functions like ARRAYFORMULA, which can return multi-row outputs.
If you attempt it, Excel will return a #CALC error, stating ‘Nested arrays are not supported.’
The main difference between BYCOL in Excel and Google Sheets is that Excel cannot handle multi-row outputs.
Example #2: Using SEQUENCE Function
Let’s say you have the numbers 5, 4, and 2 in cells A1:C1.
In Google Sheets, you can use the following BYCOL formula in cell A2 to generate a sequence taking values from the array:
=BYCOL(A1:C1, LAMBDA(col, SEQUENCE(col)))Output:
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | |
| 4 | 4 | |
| 5 |
However, this won’t work in Excel.
In summary, BYCOL in Google Sheets can return two-dimensional array output, whereas in Excel, it cannot.
