How to Use BYCOL Function in Google Sheets

In this article, you will learn what the BYCOL formula is and how to use it. This function is meant to be used with the LAMBDA function. The formula groups values by columns in a selected range or an array, allows the LAMBDA function to use the grouped values as input, and returns a computed value for each column in a row. 

‍

How to insert the BYCOL formula in Google Sheets

1. Type “=BYCOL” or go to “Insert” → “Function” → “Array” → “BYCOL”.
2. Select a range or an array to which you apply the LAMBDA by columns.
3. Enter a LAMBDA function with a placeholder and logic.
4. Press the “Enter” key.

‍

What is the BYCOL function in Google Sheets?

The BYCOL function is beneficial when you want to run the LAMBDA function by columns and show each calculation result in a row. The BYCOL function automatically displays the results once you enter the appropriate formula in a cell. So, you don’t need to copy a formula and paste it to all cells in which you want to show calculation results.

‍

The generic syntax is as follows:

=BYCOL(array_or_range, lambda)

Array_or_range: This array or range is used as input in the LAMBDA function.

Lambda: Define the LAMBDA function such as LAMBDA(column, average(column)) with one argument and “formula_expression”. You can also use your Named Functions here. Note the argument corresponds to a series of values in a column. You can also use your Named Functions here.

‍

Imagine you are a finance manager. You want to do a financial analysis on competitors based on the data set in the picture. The BYCOL formula allows you to show calculation results by columns (meaning by items in the case below).

‍

Here are the assumptions in the formula in cell C42 in the screenshot above.

Array_or_range: C36: G40

Lambda: LAMBDA(ColumnValue,MAX(ColumnValue))