Function Problems

How to Remove Blank Gaps in Excel Using the FILTER Function

Nimra Malik Sep 25, 2026 871 views

Question details

The user wants to extract a list of items matching a specific condition without leaving blank rows where the conditions do not match.

How to Remove Blank Gaps in Excel Using the FILTER Function

Product: Excel
Device & OS: not provided
Scenario: Extracting matching records from a data table to create a compact, contiguous list starting from the top.
Observed behavior: The current formula preserves the original row positions of the source data, resulting in a filtered list that is filled with empty gaps.

Before you start

Ensure your version of Excel supports Dynamic Arrays (such as Office 365, Excel 2021, or the latest version of WPS Office), as the FILTER function relies on this feature to automatically spill the results without gaps.

Solution 1Recommended

Use the FILTER Function to Create a Compact List

Replace traditional IF formulas with the FILTER function to automatically extract matching data without returning blank rows.

Traditional IF formulas evaluate data cell by cell. When an IF function returns an empty string for non-matching criteria, it preserves the original row position, creating a gap in your new list. The FILTER function solves this by extracting only the rows that meet the criteria, returning a neat, contiguous array.

Select the target cell

Click on the empty cell where you want the top of your new compact list to begin.

Enter the FILTER formula

Type the formula following the syntax: =FILTER(array, include, [if_empty]). For example, enter =FILTER(Table10[Subject], Table10[Area]="UK", "No matches").

Press Enter to spill the results

Press the Enter key. The matching subjects will automatically populate downwards in a continuous list, completely omitting the blank gaps.

Use the FILTER Function to Create a Compact List

Dynamic Updates: If the source table data changes, the FILTER function will automatically update the results in real-time while remaining perfectly gap-free.

WPS Spreadsheet Solution

Effortlessly Filter and Extract Data Without Gaps Using WPS Office

WPS Office Spreadsheet provides full support for Dynamic Array functions, including the FILTER function. You can quickly extract conditional data into compact lists without dealing with messy blank rows, just as you would in Microsoft Excel.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open the workbook containing your source data table.
2. Apply the FILTER function: Select an empty cell and type your FILTER formula, specifying the data array and your criteria (e.g., =FILTER(B2:B20, C2:C20="UK")).
3. Press Enter to view the compact list: Hit the Enter key, and WPS Spreadsheet will instantly spill the results, automatically removing all blank rows from the final output.

Easily extract and manage data without leaving blank rows or gaps.Full compatibility with Microsoft Excel's dynamic array functions like FILTER, SORT, and UNIQUE.Familiar user interface makes formula input and data analysis intuitive.Free to use and lightweight, ensuring smooth operation on any operating system.

Frequently Asked Questions

Why does my IF function leave blank rows when filtering data?

When you use an IF formula (like =IF(A2="UK", B2, "")) and drag it down, it evaluates every single row in the same position. If the condition is false, it outputs an empty string in that exact row, resulting in visible blank gaps in your data.

What should I do if the FILTER function returns a #CALC! error?

The #CALC! error typically occurs when the FILTER function finds no matches for your condition. You can prevent this error by providing the optional [if_empty] argument, for example: =FILTER(A1:A10, B1:B10="US", "No results found").

Can I filter data based on multiple conditions without leaving gaps?

Yes, you can use the FILTER function with multiple criteria. Multiply your conditions to apply AND logic (e.g., =FILTER(A2:A10, (B2:B10="UK")*(C2:C10="Active"))). This will still output a contiguous, gap-free list.

Does WPS Office support the FILTER function?

Yes, modern versions of WPS Office fully support dynamic array functions, including FILTER. You can use exactly the same formulas to extract compact lists without gaps, ensuring full compatibility with Excel files.

Excel & Data Problems