Excel BYCOL and BYROW Functions

Excel BYCOL and BYROW functions fundamentally change the way we write formulas that calculate across columns and down rows.

They enable us to write a formula once and have it automatically applied to all rows or columns in the range we specify, improving productivity and accuracy.

These functions are currently available to Microsoft 365 users.

 

 

Watch the Video

Download Workbook

Download an Excel workbook with the examples in this post.

 

The Old, Slow Way

Before BYCOL and BYROW the only way we could automatically have an aggregation type of formula (SUM, AVERAGE, etc.) automatically fill down a column was to use an Excel Table.

In the image below, you can see the formula in column F is the same in each cell because it uses Table Structured References to refer to each row:

But what if you don’t want to or can’t use an Excel Table?

The next best option was to copy the formula down the column, but if we added more rows, we’d have to manually copy it down again.

The New Fast Way with BYROW Function

Now with the BYROW function, we can write one formula that automatically spills down a column.

It sounds counter intuitive because it’s called BYROW, but returns a column of values.

BYROW applies a function using LAMBDA to each row in an array and returns an array of equal height.

BYROW Function Syntax

BYROW( array, LAMBDA( arrayName, formula))

• array - the range of cells you want to pass to the LAMBDA formula one by one.
• arrayName – the name you give to the array in the first argument.
• formula – the formula you want to apply to each row in the array.

BYROW Function Example 1

We can write one formula in cell F5 that calculates the average of each row and spills the results to the remaining rows like so:

The easiest way to think about BYROW is simply, any formula that you want to apply to one row, can automatically be applied to multiple rows with BYROW.

=BYROW(C5:E14, LAMBDA(rowRng, AVERAGE(rowRng) ) )

In English it reads, pass each row in the range C5:E14 to LAMBDA to find the average for that row.

Step by step the formula can be explained as follows:

1. C5:E14: is the range of cells that the function will be applied to, one row at a time.
2. LAMBDA allows you to define custom, reusable functions in Excel. It's like creating a mini-function on-the-fly without having to define a new named function in Excel
3. roWRng: is the name given to the rows in cells C5:E14. Note: this name does not need to be defined in the name manager, it is simply defined inside of LAMBDA. Whenever the LAMBDA function is called, it will operate on this row range.
4. AVERAGE(roWRng): This is the reusable function. It calculates the average of the values in ‘rowRng’, which represents a single row from the range ‘C5:E14’.

BYROW Dynamic Ranges

We know that BYROW returns a spilled array of values the same height as the array.

However, if we want to allow for more rows in the table to be added later without having to update the formula, we can reference more rows in the first argument with the TAKE function and discard any empty rows before passing them to LAMBDA.

BYROW Function Example 2

Here I want to allow for more students to be added up to row 20 and automatically have the AVERAGE calculation in column F spill down to any new rows.

In the animated image below, you can see the AVERAGE calculation in column F automatically update to include a new student on row 15.

BYCOL Function

Like BYROW, the easiest way to think about BYCOL is any formula that you want to apply to one column, can automatically be applied to multiple columns with BYCOL.

The BYCOL function applies a function using LAMBDA to each column in an array and returns an array of equal width, spilling across a row.

BYCOL Function Syntax

BYCOL( array, LAMBDA( arrayName, formula))

• array - the range of cells you want to pass to the LAMBDA formula one by one.
• arrayName – the name you give to the array in the first argument.
• formula – the formula you want to apply to each column in the array.

BYCOL Function Example 1

Let’s say we want to find the average scores for each subject.

The old way would be to write the formula in the first cell (C15) and then copy it across the columns.

However, now with BYCOL, we can write it once in cell C15 and the results spill to the remaining columns.

BYCOL Function Example 2

Let’s say we want to identify which student got the top score in each subject.

In row 16 we can easily identify the top score using MAX

In row 17 we can use INDEX and MATCH to return the student with the MAX score.

This formula can be explained as follows:

=INDEX(B5:B14, BYCOL(C5:E14, LAMBDA(colRng, MATCH(MAX(colRng), colRng, 0))))

In essence, the formula performs the following steps:

- For each column in the range C5:E14, use MATCH to find the row number where the maximum value of that column is located.

- Then uses that row number in INDEX to retrieve the corresponding value from the range B5:B14.

Step by step the formula can be explained as follows

1. INDEX returns the name from the range B5:B14 based on a row number that will be determined by the rest of the formula.
2. BYCOL passes each column in the range C5:E14, to the LAMBDA.
3. colRng is the name used by LAMBDA when referring to the columns in the range C5:E14
4. MATCH(MAX(colRng), colRng, 0): is the body of the LAMBDA function. Here's what it does:
• MAX(colRng): Finds the maximum value in the column range colRng one column at a time.
• MATCH(..., colRng, 0): Searches for the maximum value (found in the previous step) within the column range colRng and returns its relative position. The third argument, `0`, specifies that we want an exact match.

BYCOL & BYROW Important notes

1. If you’re familiar with LAMBDA, then it’s important to note that you do not need to define a name for the LAMBDA used by BYROW, although you can if you want.
2. BYROW can evaluate a LAMBDA written inside it, without having to input the arguments inside parentheses at the end. e.g. this is not required: =BYROW(C5:F14,LAMBDA(x,SUM(x))((C5:F14)))
3. LAMBDA takes more than one name argument, but with the BYROW and BYCOL function it can only take one name argument.

Related Lessons

Excel LAMBDA Function

Excel TAKE Function

Excel INDEX and MATCH formulas