Function Problems

Fix Excel BYROW Function #CALC! Error When Spilling Multiple Columns

Huma Ashraf Ch Sep 30, 2026 870 views

Question details

The user needs to resolve a #CALC! error caused by the BYROW function when attempting to return an array that spills across multiple columns.

Why BYROW Cannot Spill Multiple Columns in Excel

Product: Excel
Device & OS: not provided
Scenario: Attempting to use the BYROW and LAMBDA functions in Excel to spill or repeat a calculated result across multiple columns for each row.
Observed behavior: Excel returns a #CALC! error because the BYROW function strictly requires its LAMBDA function to return only a single value per row, preventing multi-column arrays from spilling.

Before you start

Verify that you are using a Microsoft 365 subscription or a version of Excel that supports dynamic arrays, as functions like EXPAND are required for this workaround.

Solution 1Recommended

Use the EXPAND Function Instead of BYROW

Bypass the BYROW limitation by using the EXPAND function, which can successfully repeat a source value across a specified number of columns without triggering a #CALC! error.

Excel's official documentation indicates that BYROW is designed to return exactly one result for each row passed into its array. When a LAMBDA function inside BYROW attempts to output an array of multiple columns, Excel's calculation engine blocks the operation and outputs a #CALC! error.

To achieve the desired multiple-column spill effect, you should replace BYROW entirely with a function built for array expansion, such as the EXPAND function.

Select the target cell

Click on the first cell where you want the spilled data to begin (for example, cell C5).

Enter the EXPAND formula

Type the formula =EXPAND(A5,1,7,A5) into the formula bar. Replace 'A5' with the reference to your source cell, and replace '7' with the total number of columns you want to fill.

Apply and copy down

Press Enter to execute the formula. The value will now spill across the specified number of columns. You can then select cell C5 and drag the fill handle down to apply the formula to subsequent rows.

Use the EXPAND Function Instead of BYROW

Understanding EXPAND Syntax: The EXPAND function syntax is EXPAND(array, rows, [columns], [pad_with]). In the recommended formula, it takes the single cell A5, creates an array of 1 row and 7 columns, and pads the empty space with the value from A5.

Powerful Spreadsheet Solution

Use Advanced Dynamic Arrays Seamlessly in WPS Spreadsheets

WPS Office offers a robust spreadsheet application that fully supports modern array formulas, advanced data processing, and complex calculations. It is fully compatible with Microsoft Excel files, allowing you to manipulate your data efficiently without compatibility issues.

1. Open your workbook in WPS: Launch WPS Office and open your .xlsx file in the WPS Spreadsheets module.
2. Select your target cell: Click the cell where you want to apply your advanced array formula.
3. Execute the formula: Input your alternative function (such as EXPAND or other array formulas) and press Enter to instantly spill the results across your grid.
4. Drag to fill: Use the familiar fill handle at the bottom-right of the cell to copy your functional logic down to the rest of your dataset.

100% compatibility with Microsoft Excel (.xlsx) formats and standard functionsLightweight application that loads faster and consumes fewer system resourcesSupports advanced formula authoring for complex data manipulationFree to download and use with a highly familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

What does the #CALC! error mean in Excel?

The #CALC! error means that Excel's calculation engine encountered an unsupported scenario. This frequently happens with dynamic arrays when a function attempts to produce a result that goes against its design rules, such as BYROW trying to return an array instead of a single value.

Can I use LAMBDA without BYROW to return multiple columns?

Yes. You can write custom LAMBDA functions independently of BYROW to return arrays, or you can use other helper functions like MAKEARRAY if you need to dynamically generate a multi-dimensional grid based on row and column indexes.

Are functions like BYROW and EXPAND available in all versions of Excel?

No, dynamic array functions like BYROW, LAMBDA, and EXPAND are generally only available in Microsoft 365 and modern standalone versions like Excel 2021 or newer. Older versions, such as Excel 2019 or Excel 2016, do not support these functions natively.

Excel & Data Problems