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:
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.
Connect with us.
Need a solution? Want to partner with us? Please complete the fields below to connect with a member of our team.