Fix Excel Sorting, Filtering, and Formatting Not Applying to All Rows
Question details
The user needs Excel to include all data rows when sorting, filtering, and applying formatting.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains rows below an apparent range boundary that need the same sorting, filtering, and formatting as the rows above.
- Observed behavior
- Excel operations affect only rows above an orange line or defined range, leaving lower rows unchanged.
Identify the first and last rows and columns of the dataset, including any rows below the visible boundary. Check whether the data is already in an Excel table by selecting a cell and looking for the Table Design tab.
Include All Data Rows in an Excel Table
Use this when the current table or selected range excludes rows that should be sorted and filtered.
Select the data from the header row through the last row and column you want to include. Include any rows below the visible boundary.
For a new table, choose Home > Format as Table, select a style, and confirm the full range. If the data is already in a table, select a table cell and choose Table Design > Resize Table, then enter the full range.
When creating the table, check My table has headers if the first selected row contains column names, then select OK.
Use a header's filter arrow to sort or filter the expanded table. To apply formatting, select the full range of cells to format and use the options on the Home tab.
Copy the Data to a New Worksheet
Use this if the full range is included but sorting, filtering, or formatting still behaves unexpectedly.
Sort and Filter All Rows in WPS Spreadsheet
Open the workbook in WPS Spreadsheet, select the complete dataset, and apply sorting, filtering, or formatting to the intended range.
- 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
- 2. Select all data: Select the dataset from its header row through the last row and column you want to include.
- 3. Sort or filter the selection: On the Data tab, choose Sort or Filter. Confirm the selected range if prompted, then choose the sort order or filter criteria.
- 4. Format the full range: Select the cells you want to format and use the font, alignment, or number-format controls on the Home tab.

Frequently Asked Questions
Why does Excel leave some rows out of a sort or filter?
The table or selected data range may end before those rows. Select a cell in the table and use Table Design > Resize Table to check and expand its range.
How do I include rows below an existing Excel table?
Select a cell in the table, choose Table Design > Resize Table, and specify a range that extends through the last data row.
Does an orange line always mean the data range is corrupted?
No. A visible line alone does not identify the cause. Check the selected range or table boundary first, and test the data in a new worksheet if the issue continues.
Why should I try a values-only paste?
A values-only paste can help test whether source formatting is contributing to the problem. Use Home > Paste > Values in a new sheet, but note that formulas and source formatting will not be retained.




