- Get link
- X
- Other Apps
This article explains in detail how to use Excel dynamic arrays with the BYROW and BYCOL functions to build highly automated, maintenance-friendly models and reports in Excel.
1. What dynamic arrays change in modern Excel
Dynamic arrays allow a single formula to return multiple values that automatically “spill” into adjacent cells. Instead of copying the same formula across rows and columns, you now enter one formula in a single cell and let Excel handle the rest.
BYROW and BYCOL are dynamic array functions designed for row-wise and column-wise processing. They apply a calculation to each row or column of a range and return one result per row or column as a spilled array. In Excel for Microsoft 365, Excel for Microsoft 365 for Mac, and Excel for the web, these functions are available as part of the modern dynamic array engine.
Note : BYROW and BYCOL are available only in modern Excel versions that support dynamic arrays such as Microsoft 365 and Excel for the web. In perpetual versions like Excel 2016 or 2019, these functions are not available.
Key characteristics of dynamic arrays in this context are as follows.
- One formula returns multiple results and spills automatically into neighboring cells.
- Spill ranges resize automatically when the source data changes size.
- Functions such as BYROW and BYCOL treat the entire range as a single array input and handle iteration over rows or columns internally.
2. BYROW and BYCOL fundamentals
2.1 BYROW syntax and behavior
BYROW applies a LAMBDA function to each row of an array and returns a single-column array containing one result per row.
=BYROW(array, LAMBDA(row, calculation)) array: The range or array to process row by row.LAMBDA(row, calculation): A LAMBDA that receives one argument representing a single row from the array and returns a single result for that row.
When the input array has R rows by C columns, BYROW returns R results by 1 column. The formula is entered in a single cell, and all results spill downward.
2.2 BYCOL syntax and behavior
BYCOL applies a LAMBDA function to each column of an array and returns a single-row array containing one result per column.
=BYCOL(array, LAMBDA(column, calculation)) array: The range or array to process column by column.LAMBDA(column, calculation): A LAMBDA that receives one argument representing a single column from the array and returns a single result for that column.
When the input array has R rows by C columns, BYCOL returns 1 row by C columns. The formula is entered in a single cell and spills horizontally to the right.
2.3 Short-form “function only” syntax
For simple aggregations, BYROW and BYCOL accept built-in functions directly as the second argument. This is often called a short-form or “eta” syntax.
=BYROW(array, SUM) // sum of each row =BYCOL(array, AVERAGE) // average of each column Internally, Excel treats these as LAMBDA wrappers, but the short-form syntax is faster to write and easier to read for straightforward calculations.
3. Example dataset for BYROW and BYCOL
Assume a simple sales table with months in rows and product categories in columns.
| Month | Product A | Product B | Product C |
|---|---|---|---|
| Jan | 120 | 90 | 60 |
| Feb | 140 | 80 | 75 |
| Mar | 160 | 110 | 95 |
Assume this table occupies the range A2:D5 where A2:D2 is the header row, and the numeric data is in B3:D5.
4. Row-wise calculations with BYROW
4.1 Row totals with short-form BYROW
To calculate total sales per month across all products, you can use BYROW over the numeric portion of the table.
=BYROW(B3:D5, SUM) Enter this formula in cell E3. Excel returns three results (one per row) and spills them down into E3:E5. You no longer need to maintain separate formulas in each row.
4.2 Row totals with LAMBDA for more control
If you need more complex logic, use the full LAMBDA syntax. For example, ignoring negative values in each row.
=BYROW( B3:D5, LAMBDA(r, SUM(IF(r > 0, r)) ) ) Confirm this formula with Enter (not Ctrl+Shift+Enter). The IF expression runs for each row and BYROW returns one result per row. Because dynamic arrays handle the array arithmetic, no legacy CSE input is required.
4.3 Row-wise statistics with BYROW
With the same pattern, you can compute other row-wise statistics from the same range.
=BYROW(B3:D5, LAMBDA(r, MAX(r))) // maximum per month =BYROW(B3:D5, LAMBDA(r, MIN(r))) // minimum per month =BYROW(B3:D5, LAMBDA(r, AVERAGE(r))) // average per month This design is extremely compact: one formula, one source range, and a flexible calculation encapsulated in LAMBDA.
4.4 Row-wise normalization or scaling
BYROW is also useful when you need to normalize each row so that values sum to 1 or 100 percent. For example, to convert each month’s product sales into percentage of row total:
=BYROW( B3:D5, LAMBDA(r, r / SUM(r) ) ) If you enter this formula in B8, Excel returns a three-by-three array of percentages. This is often easier and more robust than using helper columns and multiple copy-pasted formulas.
Note : When a LAMBDA returns an array such as
r / SUM(r), BYROW simply returns that array as the row result. Ensure that the LAMBDA produces a consistent structure for all rows to avoid unexpected spill shapes.5. Column-wise calculations with BYCOL
5.1 Column totals with short-form BYCOL
To calculate total sales per product across all months:
=BYCOL(B3:D5, SUM) Enter this in cell B6. Excel returns one result per column and spills horizontally into B6:D6. Each value is the total for a given product.
5.2 Column-wise statistics with LAMBDA
Use LAMBDA when you need per-column logic that cannot be expressed as a simple built-in function.
=BYCOL( B3:D5, LAMBDA(c, AVERAGE(IF(c > 0, c)) ) ) This formula calculates the average for each product, ignoring nonpositive values. The LAMBDA receives a whole column as c, and BYCOL returns a single row with three results.
5.3 Column-wise quality checks
BYCOL is also convenient for quick data-quality checks. For instance, to count how many numeric entries exist in each column:
=BYCOL( B3:D5, LAMBDA(c, COUNT(c) ) ) This immediately shows whether there are missing entries or structural issues in particular columns.
6. Combining BYROW and BYCOL with dynamic array tools
6.1 Using BYROW and BYCOL on spill ranges
One of the strongest patterns is to chain BYROW or BYCOL with other dynamic array functions. For example, suppose a FILTER formula returns a spill range of sales data based on selected months.
=FILTER(B3:D5, A3:A5 <> "") Assume this formula sits in F3 and spills into a dynamic range. You can reference the spill range using the # suffix and pass it into BYROW.
=BYROW(F3#, SUM) Whenever the criteria for FILTER change, the spill range size changes automatically and BYROW recomputes totals for the new set of rows without any additional work.
6.2 VSTACK or HSTACK plus BYROW/BYCOL
You can vertically or horizontally combine multiple spill ranges using functions such as VSTACK or HSTACK, then consolidate them with BYROW or BYCOL. For example, to aggregate several filtered ranges row-wise:
=BYROW( VSTACK(range1#, range2#, range3#), LAMBDA(r, SUM(r)) ) This strategy is useful when building dashboards or budget reports where the underlying data source is split across several blocks or sheets.
6.3 BYROW and BYCOL with SORT, UNIQUE, and TAKE
Because BYROW and BYCOL operate on arrays, they integrate naturally with sorting and reshaping functions.
- Use SORT to reorder rows or columns before passing them into BYROW or BYCOL.
- Use UNIQUE to reduce duplicates and then apply BYROW or BYCOL on the cleaned array.
- Combine TAKE or DROP with BYROW and BYCOL to focus on leading or trailing segments of an array.
For example, to compute per-row totals only for the top two columns of a data block:
=BYROW( TAKE(B3:D5, , 2), SUM ) 7. Designing reusable LAMBDA-based helpers
Because BYROW and BYCOL are built around LAMBDA, they integrate naturally with named LAMBDA functions in the Name Manager. You can define a custom function once and reuse it in multiple places.
7.1 Defining a reusable LAMBDA
Suppose you often need to compute “normalized row percentages” for reporting. You can define a LAMBDA called ROWPCT via Name Manager:
LAMBDA(r, r / SUM(r) ) After defining this name, your worksheet formula to normalize rows becomes very compact.
=BYROW(B3:D5, ROWPCT) This improves consistency: if you later decide to change the definition (for example, excluding zeros or applying rounding), you edit the LAMBDA definition once instead of hunting through many formulas.
7.2 Composing multiple LAMBDA layers
You can also layer LAMBDA abstractions. For example, define a named function POS_SUM that returns the sum of positive values in an array:
LAMBDA(a, SUM(IF(a > 0, a)) ) Then implement row-wise and column-wise versions simply as:
=BYROW(B3:D5, POS_SUM) =BYCOL(B3:D5, POS_SUM) This design makes BYROW and BYCOL the iteration engine, while the named LAMBDA encapsulates the calculation logic.
8. Troubleshooting BYROW and BYCOL formulas
8.1 Handling #SPILL! errors
Since BYROW and BYCOL return spill ranges, #SPILL! errors are relatively common when other values block the target cells.
- Ensure that the spill range beneath or beside the formula cell is completely empty.
- Check for merged cells, hidden shapes, or accidental entries that might intercept the spill.
- Move the formula to a new column or row if the target region is crowded.
Note : A #SPILL! error does not usually indicate a problem with the BYROW or BYCOL logic itself. It almost always means something is physically blocking the spill range on the worksheet.
8.2 Handling #CALC! and #VALUE! errors
When the LAMBDA passed to BYROW or BYCOL is invalid, Excel returns #CALC! or #VALUE! errors.
- Check that the LAMBDA parameter list matches what BYROW or BYCOL expects (exactly one parameter representing a row or a column).
- Confirm that the LAMBDA returns a single value (for simple aggregations) or a consistent-size array per row or column.
- Verify that functions inside the LAMBDA can operate on arrays. Some legacy functions are not array-aware and may need alternatives.
As a debugging technique, you can test the LAMBDA expression independently by replacing the parameter reference with a concrete range that represents one row or column. Once this test works, plug the LAMBDA back into BYROW or BYCOL.
8.3 Compatibility with structured references and tables
BYROW and BYCOL work well with Excel Tables using structured references. For example, if a table named SalesTbl has a data body range SalesTbl[Product A]:SalesTbl[Product C], you can write:
=BYROW(SalesTbl[[Product A]:[Product C]], SUM) Because tables expand automatically, the BYROW results stay in sync as new rows are added. This is a powerful pattern for building self-updating reports without manual adjustment of range references.
9. Practical design patterns for reporting
9.1 Building a row and column summary grid
You can combine BYROW and BYCOL to build summary grids around a core data block.
- Use BYROW to generate row totals in a column to the right of the data.
- Use BYCOL to generate column totals in a row beneath the data.
- Use another dynamic formula (for example, SUM of the BYROW results) to compute the grand total.
With dynamic arrays, the entire summary adjusts automatically when the data block expands or contracts.
9.2 Scenario modeling with FILTER plus BYROW/BYCOL
In scenario models, each scenario can be represented by a spilled range filtered from a larger data table. BYROW and BYCOL can then be applied to compute totals or KPIs for each scenario range without duplicating formula structures. This reduces the risk of formula drift between scenarios and simplifies model auditing.
9.3 Using BYROW and BYCOL as loop replacements
For many analytical tasks where you would previously have considered VBA loops or complex SUMPRODUCT constructions, BYROW and BYCOL can act as pure worksheet-based loop engines. They iterate over rows or columns and apply custom logic defined by LAMBDA. This approach keeps logic transparent in formulas and reduces dependence on macros.
FAQ
When are BYROW and BYCOL available in Excel?
BYROW and BYCOL are available in Excel for Microsoft 365, Excel for Microsoft 365 for Mac, and Excel for the web. They are part of the dynamic array feature set and do not exist in older perpetual versions such as Excel 2016 or 2019.
What is the main difference between using BYROW/BYCOL and copying formulas manually?
With BYROW and BYCOL, one formula controls all row or column calculations. The results spill automatically based on the size of the array. This greatly reduces manual copying, the risk of inconsistent formulas, and maintenance effort when data ranges change size.
Do BYROW and BYCOL always require LAMBDA functions?
No. For simple aggregations you can use short-form syntax such as =BYROW(range, SUM) or =BYCOL(range, AVERAGE). For more advanced logic, pass a full LAMBDA expression so that you can control exactly how each row or column is processed.
Can BYROW and BYCOL operate on dynamic spill ranges from other formulas?
Yes. You can reference a spill range using the # suffix (for example, F3#) and pass that directly as the array argument to BYROW or BYCOL. When the spill range grows or shrinks, the BYROW/BYCOL result automatically resizes.
How do I debug a complex BYROW or BYCOL formula?
Start by isolating the LAMBDA logic. Replace the parameter reference with a fixed row or column range and confirm that the expression returns the expected result. Then restore the LAMBDA wrapper and reinsert it into BYROW or BYCOL. Also check for spill-blocking values and confirm that the number of parameters in the LAMBDA matches what BYROW or BYCOL expects.