How to Use the Excel BYROW and BYCOL Functions

You can use BYROW and BYCOL functions with the LAMBDA helper in Excel. Microsoft introduced LAMBDA to help you customize and execute formulas without needing Visual Basic for Applications (VBA) coding.

LAMBDA helpers reduce the need to enter the same formula multiple times. This output is dynamic and updates as the reference data changes.

Using BYROW and BYCOL

This example uses sales data, where column A lists six salespeople’s names, and subsequent columns list their monthly sales totals. Your row 1 header would be “Name,” followed by each month in the neighboring columns.

1. Define Your Objectives and Output Cells

Start by determining what you want to know to identify the reference data. Let’s assume you need the yearly sales total per person and each month’s total sales. Type “Yearly Total” in N1 — the first column next to “December” — and “Monthly Total” in A8 — the first row beneath the final salesperson’s name.

2. Start Building the Formula

Type the equal sign and appropriate function in the destination cell — BYCOL for columnar data and BYROW for row-based values. The helper should appear.

3. Add Your Array

Click on “array” in the builder and select the data you want to analyze. Your selection must be rectangular. In this example, you’d choose the range B2 to M7.

4. Add the LAMBDA Helper

Click on “[function]” in your builder and type “LAMBDA” to tell Excel you want to use the helper. Arguments should appear to generate the remaining syntax.

5. Create Your Variable

Name this first field — your variable. In this example, you’d call it “monthly” to run a BYCOL monthly sales total. Add a comma and continue.

6. Enter the Calculation for LAMBDA to Run

Identify the calculation you need. In this case, it’s addition, so type “sum.” Arguments should appear labeled “number1,” “[number2],” and so forth, following an open bracket. Usually, you would define the array here for SUM. You’ve done that in a previous step. The array is “monthly,” so type that again. Your builder should reflect:

=BYCOL(B2:M7,LAMBDA(monthly,sum(monthly

7. Close the Function

Add the three closing brackets and press “Enter.” The BYCOL array of results should appear in the output cell you selected. In this example, each column would display a monthly sales total in row 8. Follow these same steps to build a BYROW function in column N to display a row-by-row yearly total per salesperson.

Learn More About Our Managed IT Services

We’re on a mission to simplify technology and support productivity. Whether you’re looking for cybersecurity expertise or a customized do-it-for-me IT service, we can help with cost-effective, end-to-end IT solutions.

See how much you can save, then contact us online to learn more about what we can do for your company.