How to Fix Excel Filters That Do Not Include All Rows in WPS Spreadsheets
Question details
User needs to restore missing records in an Excel or WPS Spreadsheets filter range when the filter stops early or omits rows.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working in a spreadsheet with a long data range and applies a filter to review records, but the filter excludes rows or stops before the end of the dataset.
- Observed behavior
- The filter omits records, ends at a blank row or blank column, or fails to include rows with leading or trailing spaces.
Select a single cell inside the data area, then press Ctrl+A to see whether the full range is selected before applying a filter. Also check for blank rows, blank columns, and extra spaces inside the range.
Convert the data range to an Excel Table and reapply the filter
Use this when you want WPS Spreadsheets or Excel to automatically include the full contiguous dataset and expand the filter range as new rows are added.
An Excel Table automatically treats the entire table as one range, so filters do not stop at blank cells or an incomplete manual selection.
Open the worksheet in WPS Spreadsheets and click any cell in the data area that contains your headers and records.
Press Ctrl+T, verify that the dialog shows the entire dataset, check My table has headers, and click OK.
Go to the Data tab, click Filter, then click the dropdown arrow in a column heading to filter the full table range.
Reset an existing filter with Data > Clear
Use this when a filter already excludes records and you need to restore all hidden rows, especially after saving the workbook.
Remove blank rows, blank columns, and extra spaces before filtering
Use this when the dataset has blank separators or inconsistent text values that make the filter stop early.
Fix Excel Filter Ranges with WPS Spreadsheets Free
Open the affected Excel workbook in WPS Spreadsheets, convert the range to a table, or reset the filter to restore all rows. WPS Spreadsheets is free, lightweight, and fully compatible with Excel files, so you can fix incomplete filters without losing formatting or data.
- 1. Download WPS Office Free: Go to the WPS Office website, download WPS Office free, install it, and open WPS Spreadsheets.
- 2. Open the Excel workbook: In WPS Spreadsheets, click Menu > Open and choose the affected XLSX or XLS file.
- 3. Select the entire data range: Click any cell inside the data, press Ctrl+A, and confirm the selection covers all headers and records.
- 4. Convert the range to an Excel Table: Press Ctrl+T, verify the table range, check My table has headers, and click OK.
- 5. Reset or reapply the filter: Go to Data > Clear to remove old filter criteria, then click Filter and use the column dropdown to apply a new filter.
Frequently Asked Questions
Why does my Excel filter stop at a blank row?
Excel treats a completely blank row as the end of a contiguous range. Remove the blank row or convert the range to an Excel Table with Ctrl+T so the filter includes all rows in the table.
Why do rows with leading spaces not appear when filtering?
Leading spaces make text values different from the visible text. Clean them with =TRIM(cell) or use Find & Replace to remove spaces, then reapply the filter.
How do I clear an existing filter without undoing other changes?
Go to Data > Clear in the Sort & Filter group. This removes the filter criteria and restores hidden rows without undoing other workbook changes.
Will converting data to a table change my spreadsheet layout?
No. The table keeps existing formulas and formatting, adds banded rows, and automatically expands the filter range when new rows are added.
Can WPS Spreadsheets fix an Excel filter range without Microsoft Excel?
Yes. Open the XLSX file in WPS Spreadsheets, use Data > Clear or convert the range to a table, and save it back as XLSX without reformatting.
