Formula Errors

Transfer Excel Data Without Blank Rows Using FILTER

Huda Qurayshi Oct 10, 2026 871 views

Question details

The user needs to extract specific data from a worksheet and return it in a compact list without any empty rows.

How to Transfer Excel Data Without Blank Rows Using the FILTER Function

Product: Excel
Device & OS: not provided
Scenario: Extracting or transferring data based on specific criteria to create a compact, gapless list.
Observed behavior: Using standard IF formulas transfers the required data but leaves blank rows for records that do not meet the criteria, resulting in a fragmented list.

Before you start

Ensure you are using a version of Excel or spreadsheet software that supports dynamic arrays, such as Microsoft 365, Excel 2021, or the latest version of WPS Office.

Solution 1Recommended

Use the FILTER Function to Remove Blank Rows

Replace standard IF formulas with the dynamic FILTER function to automatically spill results and exclude empty records.

The FILTER function is a dynamic array formula that evaluates a range against specific criteria and extracts only the matching rows, completely ignoring the non-matching ones so no gaps are left behind.

Select the destination cell

Click on the top-left cell where you want your new, compact data list to begin.

Enter the FILTER formula

Type the formula =FILTER(Sheet1!B2:B100, Sheet1!B2:B100<>"") and adjust the ranges to match your actual dataset.

Apply the formula

Press the Enter key. The filtered data will automatically spill down into the adjacent cells, creating a continuous list without any blank rows.

Use the FILTER Function to Remove Blank Rows

Dynamic Spill Feature: Because FILTER is a dynamic array function, you do not need to drag the formula down. Just ensure there is enough empty space below your target cell to avoid a #SPILL! error.

Solution 2

Use Power Query as an Alternative

If you are using an older version of Excel that lacks the FILTER function, Power Query can extract and clean the data effectively.

Easy Data Filtering in WPS Office

Use Advanced Array Formulas Easily with WPS Office

WPS Spreadsheet fully supports modern dynamic array functions, including FILTER. This allows you to seamlessly transfer, filter, and organize your data without dealing with frustrating blank rows left by traditional IF statements.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the file containing the data you want to transfer.
2. Select your destination: Click the cell where you want the gapless list to appear.
3. Apply the FILTER function: Type =FILTER(A2:A100, A2:A100<>"") and hit Enter to generate your compact list automatically.

Fully compatible with Microsoft Excel dynamic array formulasBuilt-in FILTER and UNIQUE functions for advanced data extractionLightweight application that opens quickly and runs smoothly on any deviceCompletely free alternative to Microsoft Office

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a #NAME? error when using the FILTER function?

The #NAME? error usually occurs if your spreadsheet software does not support dynamic array functions. You need Microsoft 365, Excel 2021, or the latest version of WPS Office to use the FILTER function.

How can I filter based on multiple criteria without getting blank rows?

You can evaluate multiple conditions by multiplying them in the FILTER function's include argument. For example, using =FILTER(A2:A100, (B2:B100="Yes")*(A2:A100<>"")) will extract records where column B is 'Yes' while still ignoring any blank entries.

How do I handle the #CALC! error in the FILTER function?

The #CALC! error appears when the FILTER function finds no matching records based on your criteria. You can handle this gracefully by adding the optional [if_empty] argument at the end of the formula, like this: =FILTER(A2:A100, A2:A100<>"", "No Results").

Excel & Data Problems