Function Problems

How to Filter Rows to Another Sheet in WPS Spreadsheets When a Column Is Blank

Steve K Nov 13, 2026 869 views

Question details

The user needs to return all rows from a master sheet where column M is empty onto another sheet in WPS Spreadsheets.

How to Filter Rows to Another Sheet in WPS Spreadsheets When a Column Is Blank

Product: WPS Spreadsheets
Device & OS: not provided
Scenario: The user manages a master sheet and needs to extract all records where column M is blank to another sheet.
Observed behavior: They want a formula or method that returns all matching rows automatically without manual filtering or copying.

Before you start

Open the workbook in WPS Spreadsheets and confirm the master sheet name and the exact data range, such as Sheet1!A1:P1000, so the formula references the correct rows and columns.

Solution 1Recommended

Use FILTER with ISBLANK to Return Rows with a Blank Column M

Use this formula when column M contains truly blank cells and you need an automatic spill range on another sheet.

Select the destination sheet

Open the workbook in WPS Spreadsheets and click Sheet2, or create a new sheet for the filtered results.

Enter the FILTER formula

Click the first output cell, such as A1, and enter =FILTER(Sheet1!$A$1:$P$1000,ISBLANK(Sheet1!$M$1:$M$1000)). Press Enter.

Adjust the range to your data

Replace Sheet1!$A$1:$P$1000 and Sheet1!$M$1:$M$1000 with the actual sheet name, row count, and columns in your workbook.

Use FILTER with ISBLANK to Return Rows with a Blank Column M

Using formulas that return empty strings: If column M contains formulas that return "", use =FILTER(Sheet1!$A$1:$P$1000,Sheet1!$M$1:$M$1000="") instead of ISBLANK.

Solution 2

Filter Rows Where Column M Appears Blank but Contains Formulas

Use this variation when column M has formulas returning empty strings, which the ISBLANK function does not treat as blank.

WPS Spreadsheets formula solution

Filter Blank-Column Rows to Another Sheet in WPS Spreadsheets

WPS Spreadsheets supports the dynamic FILTER function, so you can pull rows where column M is blank onto another sheet with one formula instead of manual filtering.

1. Open WPS Spreadsheets: Download WPS Office, open your workbook, and click the output sheet where results should appear.
2. Enter the FILTER formula: Click the first output cell and type =FILTER(Sheet1!$A$1:$P$1000,ISBLANK(Sheet1!$M$1:$M$1000)).
3. Press Enter and check the spill range: Press Enter to confirm. WPS Spreadsheets fills the matching rows automatically.
4. Adjust for more rows or empty-string formulas: Change the $1000 limit to match your data, or use Sheet1!$M$1:$M$1000="" if column M contains formulas returning empty strings.

One-formula automatic spill range updates when source data changesWorks with Excel .xlsx files and maintains Microsoft Excel formula compatibilityFree WPS Office includes WPS Spreadsheets, Writer, and PresentationYou can adjust row limits and columns without VBA or add-ins

Frequently Asked Questions

Why does my FILTER formula return #CALC! when no rows have a blank column M?

FILTER returns #CALC! when the condition matches no rows. You can wrap it with IFERROR, for example =IFERROR(FILTER(...),"No blank rows"), or populate a blank cell in column M if the result should not be empty.

What should I change if my data has more than 1,000 rows?

In the formula, change $1000 to the last row number of your data, such as $5000, and keep both the array and condition ranges the same height.

Can I output only certain columns instead of A:P?

Yes. Replace Sheet1!$A$1:$P$1000 with the exact column range you want, for example Sheet1!$A$1:$C$1000, while keeping the condition range on column M.

Does this FILTER formula work when column M contains formulas?

It works if the formula returns a true blank. If it returns an empty string (""), replace ISBLANK(Sheet1!$M$1:$M$1000) with Sheet1!$M$1:$M$1000="".

Excel & Data Problems