Function Problems

How to Filter Excel Data from Another Sheet and Keep Blanks Empty

Partner Editor Oct 7, 2026 868 views

Question details

The user needs to extract specific transaction rows from a summary sheet into a different destination sheet, ensuring that any blank cells in the source data remain blank instead of appearing as zeros.

How to Filter Excel Data from Another Sheet and Keep Blanks Empty

Product: Excel
Device & OS: not provided
Scenario: Filtering transaction records across different worksheets based on specific criteria, while maintaining clean formatting without unwanted zero values.
Observed behavior: Standard array formulas often convert blank source cells into zeros in the destination sheet, which clutters the data.

Before you start

Ensure your spreadsheet software supports dynamic arrays, as the FILTER and LET functions are only available in newer versions such as Microsoft 365, Excel 2021, and the latest WPS Office updates.

Solution 1Recommended

Use LET and FILTER Functions Together

Combine the LET and FILTER functions to evaluate the filtered array once and automatically replace any zero values with true blanks.

When using a standard FILTER function, empty cells in the source data return as zeros in the destination sheet. Wrapping the FILTER function inside a LET function allows you to assign the filtered array to a variable (e.g., 'f'). You can then use an IF statement to check if the variable is blank, returning an empty text string if true.

Select the Destination Cell

Click on the top-left cell in your destination sheet (e.g., cell A8 in the DOCS Transaction Sheet) where you want the filtered data to begin.

Enter the Array Formula

Type the formula exactly as follows: =LET(f,FILTER('Summary Transaction Sheet'!A8:H27,'Summary Transaction Sheet'!K8:K27<>"",""),IF(f="","",f)). Do not enter this into multiple cells.

Allow the Results to Spill

Press Enter. Because this is a dynamic array formula, it will automatically spill the filtered results into the adjacent rows and columns. Ensure the adjacent cells are empty to avoid a #SPILL! error.

Regional Settings: If your computer's regional settings use semicolons as list separators, replace the commas in the formula with semicolons (e.g., =LET(f;FILTER(...))).

Advanced Spreadsheet Features

Filter and Manage Complex Data Easily with WPS Office

WPS Spreadsheet fully supports advanced dynamic array functions like FILTER, LET, and UNIQUE, allowing you to build complex data reports and solve formatting issues seamlessly.

1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your transaction data.
2. Input the Dynamic Formula: Select the target cell in your destination sheet and type your LET and FILTER formula.
3. View Clean Results: Press Enter to instantly populate your filtered data with properly preserved blank cells.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Supports dynamic array functions for automatic data spilling without complex workarounds.Lightweight software with a fast, responsive interface for handling large datasets.Free and easy-to-use alternative with a familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the FILTER function return zeros for empty cells?

Spreadsheet software typically evaluates empty cells as zero when transferring data through formulas. To prevent this, you must explicitly tell the formula to output an empty string ("") if the source cell is blank by using an IF statement.

Can I filter data based on multiple criteria from another sheet?

Yes. You can multiply criteria arrays within the FILTER function to act as AND logic, or add them to act as OR logic (e.g., (Sheet1!A1:A10="NCT")+(Sheet1!A1:A10="MVR")).

What does the #SPILL! error mean when using dynamic arrays?

The #SPILL! error occurs when the destination range has existing data, text, or merged cells blocking the formula from expanding. Clear the cells below and to the right of your formula to allow the data to populate automatically.

Excel & Data Problems