Function Problems

How to Extract Excel Rows When Specific Columns Contain Values

Natalie Taylor Oct 1, 2026 868 views

Question details

The user needs to extract complete rows of data to a separate worksheet if any cell within a specific range of columns (G through J) contains a value greater than zero.

How to Extract Excel Rows When Specific Columns Contain Values

Product: Microsoft Excel
Device & OS: not provided
Scenario: Filtering and extracting matching records automatically to an empty destination worksheet without using SQL.
Observed behavior: The user wants the matching records to spill dynamically into the new sheet, avoiding simple sums and bypassing complex database queries.

Before you start

Verify the exact file name, worksheet name, and cell range of your source data before applying the formula, and ensure the destination area has enough empty space to accommodate the extracted rows.

Solution 1Recommended

Use Dynamic Array Formulas (LET and FILTER)

Use modern dynamic array functions to automatically evaluate multiple columns and extract matching rows into a new worksheet.

By combining functions like LET, FILTER, BYROW, and CHOOSECOLS, you can create a powerful formula that checks if any value in columns G through J is greater than zero, and spills the corresponding full row (Columns A through X) to your destination.

Select the destination cell

Open your destination worksheet and click on cell A2 (or the top-left cell where you want the extracted data to begin).

Enter the dynamic array formula

Type the following formula: =LET(rng,'[testing-5.xlsx]report'!$A$2:$X$23,FILTER(IF(rng="","",rng),BYROW(CHOOSECOLS(rng,7,8,9,10),LAMBDA(a,SUM(--(a>0))>0))))

Update the data references

Replace '[testing-5.xlsx]report'!$A$2:$X$23 with the actual workbook name, worksheet name, and data range of your source file.

Execute the formula

Press Enter. The matching rows will automatically spill into the adjacent cells downwards and rightwards.

Use Dynamic Array Formulas (LET and FILTER)

Version Compatibility: This solution relies on dynamic array functions (LET, BYROW, CHOOSECOLS, LAMBDA) which require Microsoft 365 or a compatible modern spreadsheet software.

Solution 2

Filter Rows Using Power Query

Use Power Query as a robust, no-code alternative if your data requires regular automated refreshing.

Effortlessly Extract Data with Dynamic Arrays in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas like FILTER, allowing you to instantly extract and manage complex datasets across multiple columns without writing SQL or VBA code.

1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your source data.
2. Prepare destination sheet: Create a new worksheet where you want the filtered rows to appear.
3. Apply the FILTER formula: Select the first cell (e.g., A2) and input your combined FILTER and logical criteria formula.
4. Spill the results: Press Enter to dynamically populate the rows. The data will automatically update if the source values change.

Fully compatible with Microsoft Excel dynamic array formulas and functionsSupports complex multi-column data filtering instantlyLightweight application with a clean, familiar interfaceFree to use for everyday data analysis and reporting tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my dynamic array formula return a #SPILL! error?

A #SPILL! error occurs when there is existing text, data, or merged cells blocking the space where the formula needs to expand. To fix this, clear all cells below and to the right of your formula cell.

How do I change which columns the formula checks?

You can modify the column index numbers in the CHOOSECOLS(rng, 7, 8, 9, 10) section of the formula. For example, to check columns B and C, change the numbers to 2, 3.

Can I use these formulas in older versions of Excel like Excel 2016 or 2019?

No, functions like BYROW, CHOOSECOLS, and LET were introduced in Microsoft 365. For older versions, you must use alternative methods such as Power Query, advanced filters, or complex INDEX/AGGREGATE array formulas.

Excel & Data Problems