Data Import & Export

Fix Excel Sorting, Filtering, and Formatting Not Applying to All Rows

Olivia MillerOlivia Miller Nov 14, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Select the complete dataset

Select the data from the header row through the last row and column you want to include. Include any rows below the visible boundary.

2
Create or resize the table

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.

3
Confirm the headers

When creating the table, check My table has headers if the first selected row contains column names, then select OK.

4
Retry the operations

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.

Check the range: Blank rows can make automatic range selection stop early. Confirm the last data row in the table range before sorting or filtering.
Work with your full data range

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. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 2. Select all data: Select the dataset from its header row through the last row and column you want to include.
  3. 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. 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.
Select the full dataset before sorting or filteringOpen and edit Microsoft Excel XLSX and XLS filesApply formatting to a selected range from the Home tabUse a familiar spreadsheet interface
QA img-9

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.