Function Problems

How to Use Excel Formula to Copy Matching Values Without Blank Rows

Aamir Naveed Akram Sep 28, 2026 869 views

Question details

Extract specific data rows that meet a certain condition from one column to another, ensuring the results appear consecutively without blank rows.

How to Copy Matching Values Without Blank Rows Using Excel Formulas

Product: Excel
Device & OS: not provided
Scenario: Transferring rows where a specific condition (e.g., the word 'TEST') is met, while filtering out non-matching records and empty cells.
Observed behavior: Standard extraction methods or simple conditional references leave blank rows where the condition is not met, or the formula returns a #CALC! error when no matching values are found.

Before you start

Ensure you are using a spreadsheet version that supports Dynamic Array formulas (like Microsoft Office 365, Excel 2021, or the latest version of WPS Office), as the FILTER function is required for this operation.

Solution 1Recommended

Use the FILTER Function with Multiple Criteria

The dynamic array FILTER function is the most efficient way to extract data conditionally while automatically collapsing blank rows.

By multiplying two conditions inside the FILTER function, you can enforce an 'AND' logic. This ensures that the extracted data matches your specific keyword and is not an empty cell. Additionally, providing an empty string as the last argument prevents error codes when no data matches the criteria.

Select the destination cell

Click on the first cell of the column where you want the matching, consecutive values to appear.

Input the FILTER formula

Type the formula: =FILTER([Book2]Sheet1!$B:$B,([Book2]Sheet1!$F:$F="Test")*([Book2]Sheet1!$B:$B<>""),""). Replace the workbook and column references with your actual data ranges.

Understand the arguments

The first argument ($B:$B) is the data to return. The second argument uses the asterisk (*) to ensure Column F contains 'Test' AND Column B is not blank (<>""). The final argument ("") is the 'if_empty' fallback.

Apply the formula

Press the Enter key. The formula will automatically spill down the column, listing all matching values consecutively without any blank rows in between.

Use the FILTER Function with Multiple Criteria

Preventing #CALC! Errors: The empty string ("") at the end of the formula is crucial. It tells the spreadsheet to display a blank cell instead of a #CALC! error if absolutely no rows meet your 'Test' condition.

Advanced Spreadsheet Features

Filter and Extract Data Dynamically in WPS Spreadsheet

WPS Office offers full support for advanced dynamic array formulas like FILTER. You can organize data, remove blank rows, and automate complex workflows easily, all within a familiar interface.

1. Open your dataset: Launch WPS Spreadsheet and open your .xlsx file containing the source data.
2. Insert the FILTER formula: Select the target cell where you want your clean data to start, and type the FILTER function with your specific conditions.
3. Execute to spill data: Press Enter. WPS Spreadsheet will instantly calculate and spill the matching records into consecutive rows seamlessly.

Free and lightweight office suiteSeamless compatibility with Microsoft Excel (.xlsx) filesFull support for modern dynamic array functions like FILTER and XLOOKUPCross-platform support for Windows, Mac, Linux, iOS, and Android

microsoft office alternative - wps office

Frequently Asked Questions

Why is my FILTER function returning a #CALC! error?

The #CALC! error occurs when the FILTER function calculates the data but cannot find any records that meet your specified criteria. You can prevent this by adding a fallback value, such as an empty string (""), as the third argument in your formula.

Can I use multiple conditions in the FILTER function?

Yes, you can use the asterisk (*) to apply multiple conditions (AND logic) or a plus sign (+) for OR logic. Enclose each condition in parentheses, for example: (Range1="Condition1")*(Range2<>"").

What if I have an older version of Excel that doesn't support the FILTER function?

If the dynamic FILTER function is unavailable in your version, you can achieve similar results using the built-in 'Advanced Filter' tool located under the 'Data' tab, or by combining the INDEX, AGGREGATE, and ROW functions as an array formula.

Will the extracted list update automatically if the source data changes?

Yes, because the FILTER function is a dynamic array formula, your extracted list will automatically update, expand, or shrink whenever the source data is modified, added to, or deleted.

Excel & Data Problems