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))

‍

Other LAMBDA support functions

Check the articles below to learn about other LAMBDA helper functions such as MAP, MAKEARRAY, SCAN, REDUCE, and BYROW.

How to Use MAP Function in Google Sheets

How to Use MAKEARRAY Function in Google Sheets

How to Use SCAN Function in Google Sheets

How to Use REDUCE Function in Google Sheets

How to Use BYROW Function in Google Sheets

‍

Analyze your live financial data in a snap

Are you learning this formula to process financial data, build a financial model, or conduct financial analysis? If so, LiveFlow may help you to automate manual workflows and keep numbers updated in real time. For instance, you can easily compare your Budget versus Actual without any manual work. Furthermore, you can access more than fifty templates here, from the simple Income Statement and Operational KPI templates to Multi-Currency Consolidated Income Statement, Balance Sheet, and Cash Flow templates. You can also customize these templates as you want without breaking the automated data inflow.

‍

To learn more about LiveFlow, book a demo.