Excel BYROW, BYCOL & MAKEARRAY: Supercharge Your Array Formulas in 2026

If you thought Excel’s dynamic array revolution ended with XLOOKUP and FILTER, think again. In 2026, three powerful lambda-based functions — BYROW, BYCOL, and MAKEARRAY — are transforming how Excel power users build formulas. These functions let you apply custom logic row-by-row, column-by-column, or generate entirely new arrays from scratch — all without a single line of VBA.

Whether you’re a financial analyst building dynamic models, a data professional cleaning messy datasets, or a business user who wants Excel to do more heavy lifting, BYROW, BYCOL, and MAKEARRAY are skills worth mastering this year.

What Are BYROW, BYCOL, and MAKEARRAY?

These three functions are part of Excel’s Lambda family, introduced alongside the LAMBDA function itself. They build on Excel’s dynamic array engine and let you write logic once and apply it across an entire range. Here’s a quick overview:

BYROW — applies a custom formula to each row in a range and returns a column of results.

BYCOL — applies a custom formula to each column in a range and returns a row of results.

MAKEARRAY — generates a 2D array of any size using a formula you define, based on row and column position.

Together, they replace tedious copy-paste patterns and helper columns with clean, single-cell formulas.

Understanding BYROW

BYROW takes two arguments: the array you want to process, and a LAMBDA that defines what to do with each row.

Syntax:

=BYROW(array, LAMBDA(row, formula))

Practical Example: Row-by-Row Sum of Selected Columns

Suppose you have sales data in columns B through F (Jan–May) and you only want to sum Q1 (Jan–Mar) for each row without adding helper columns. Instead of dragging a SUM formula down, use:

=BYROW(B2:D10, LAMBDA(row, SUM(row)))

This returns a single column array with the Q1 total for each of the 9 rows. As your data expands, the formula adapts automatically.

Advanced Use: Conditional Row Logic

BYROW really shines when you combine it with IF or other logic:

=BYROW(A2:C10, LAMBDA(row, IF(MAX(row)>100, "High", "Low")))

This classifies each row as High or Low based on whether any value exceeds 100 — something that previously required a complex helper column with nested functions.

Understanding BYCOL

BYCOL mirrors BYROW, but operates on columns instead of rows:

=BYCOL(array, LAMBDA(col, formula))

Practical Example: Column-Level Averages

If you have monthly revenue figures in columns B through M (12 months) and want to find the average for each month across all product lines (rows 2–20):

=BYCOL(B2:M20, LAMBDA(col, AVERAGE(col)))

The result is a single-row array with 12 values — one average per month. No more copying AVERAGE formulas across a summary row.

Advanced Use: Non-Blank Count Per Column

=BYCOL(B2:M20, LAMBDA(col, COUNTA(col)))

This counts how many non-empty cells exist in each column — invaluable for data quality checks on large tables.

Understanding MAKEARRAY

MAKEARRAY is the most creative of the three. Instead of processing an existing range, it generates a brand-new array by calling a LAMBDA for each cell position:

=MAKEARRAY(rows, cols, LAMBDA(r, c, formula))

Practical Example: Multiplication Table

Generate a 10×10 multiplication table in a single formula:

=MAKEARRAY(10, 10, LAMBDA(r, c, r*c))

Excel fills a 10×10 grid where each cell contains the product of its row number and column number. This used to require a static table or VBA — now it’s one cell.

Advanced Use: Dynamic Date Calendar Grid

Create a 5×7 grid of dates starting from a given date (in A1):

=MAKEARRAY(5, 7, LAMBDA(r, c, A1 + (r-1)*7 + (c-1)))

This produces a calendar-style layout of 35 consecutive dates, formatted as a grid. Format the output range as dates to see a fully dynamic calendar.

Combining BYROW, BYCOL, and MAKEARRAY

The real power comes from combining these functions. Here’s a real-world scenario: you have a 5×12 sales matrix (5 salespeople, 12 months) and you want to compute each salesperson’s performance rating based on their total vs. the team average.

Step 1 — Row totals with BYROW:

=BYROW(B2:M6, LAMBDA(row, SUM(row)))

Step 2 — Team monthly averages with BYCOL:

=BYCOL(B2:M6, LAMBDA(col, AVERAGE(col)))

Nesting these inside a MAKEARRAY lets you build a dynamic scoring matrix that updates instantly when any sales figure changes.

Tips for Getting Started in 2026

Make sure you’re on Microsoft 365 — these functions require a current subscription. They are not available in Excel 2019 or earlier standalone versions.

Practice with small arrays first. Start with BYROW on a 5×3 table before applying it to large datasets.

Use Copilot in Excel to help write BYROW and BYCOL formulas. Type your intent in plain English and Copilot will generate the lambda logic.

Combine with LAMBDA-defined helper functions — store your custom LAMBDA in the Name Manager, then reuse it inside BYROW or BYCOL across any sheet.

Conclusion

BYROW, BYCOL, and MAKEARRAY represent the next generation of Excel productivity. They eliminate entire categories of repetitive work — helper columns, copy-paste chains, and VBA macros — replacing them with elegant, self-maintaining formulas. In 2026, mastering these three functions is one of the most impactful investments you can make in your Excel skills.

Try building a BYROW formula on your next data project. Start simple, combine with BYCOL, and you’ll quickly see why these functions have become essentials in every Excel power user’s toolkit. Have a specific use case you’d like to tackle? Drop it in the comments below — we’d love to help you build it!