Function Problems

How to Use BYROW to Subtract Columns with a Spilled Formula in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to find a way to subtract values across two columns and return the results as a dynamic spilled array, specifically exploring the BYROW function.

Product: Excel 365
Device & OS: not provided
Scenario: Calculating row-by-row differences for columns containing spilled arrays without having to manually drag the formula down.
Observed behavior: The user needs a formula that accurately subtracts the corresponding values of two columns (e.g., column B and C) and automatically spills the result down a third column.

Before you start

Ensure you are using a spreadsheet version that supports dynamic arrays and the latest functions, such as Microsoft 365, to utilize the BYROW and LAMBDA formulas without errors.

Solution 1Recommended

Use a Simple Spilled Array Formula

The most straightforward way to subtract two columns dynamically is by using a direct array subtraction formula, which avoids the complexity of nested functions.

If you only need to subtract a standard range from another standard range, you do not necessarily need the BYROW function. A simple array formula handles this seamlessly.

Select the target cell

Click on the cell where you want the first subtraction result to appear (for example, D2).

Enter the array formula

Type the formula =B2:B7-C2:C7 into the formula bar.

Apply the calculation

Press Enter. The formula will calculate the differences and automatically spill the results down the column for the specified rows.

Solution 2

Use BYROW with LAMBDA and INDEX

When working with complex spilled array columns as source data, combining BYROW, LAMBDA, and INDEX provides precise row-by-row iteration.

Use WPS Spreadsheet for Array Calculations

WPS Spreadsheet provides powerful dynamic array capabilities, allowing you to execute complex column arithmetic and spill results automatically.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the data columns you need to subtract.
2. Enter the array subtraction formula: Select an empty cell in a new column and type your subtraction array, such as =B2:B7-C2:C7.
3. Generate the spilled results: Press Enter. Depending on the version, the results will either spill automatically or populate as a traditional array formula (using Ctrl+Shift+Enter).

Fully compatible with Microsoft Excel (.xlsx) formats and standard array functions.Handles large datasets efficiently with fast calculation speeds.Familiar and intuitive interface for seamless transition.Free and lightweight alternative to heavy productivity suites.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my BYROW formula return a #SPILL! error?

A #SPILL! error occurs when the destination cells required to display the calculated array are not entirely blank. Ensure there is enough empty space below your formula cell to accommodate all the results.

Can I use BYROW and LAMBDA in older versions of Excel?

No, BYROW and LAMBDA are modern dynamic array functions introduced in Microsoft 365. If you are using Excel 2016, 2019, or an older version, you must use standard formulas and manually drag them down, or use legacy array formulas with Ctrl+Shift+Enter.

Can I use entire column references like B:C in the BYROW formula?

Yes, you can use whole column references, but doing so will force Excel to calculate over a million rows, which severely slows down performance. It is highly recommended to use specific ranges (e.g., B2:C100) or format your data as an Excel Table.

What is the advantage of using INDEX inside LAMBDA?

Using INDEX inside LAMBDA allows the formula to dynamically isolate specific columns within the current row being processed by BYROW. It ensures the arithmetic operations target the exact corresponding data points without needing direct cell references.

Excel & Data Problems