Fix Excel Sorting When Two Pasted Lists Sort Separately
Question details
The user needs to sort two pasted sections of Excel data together instead of having each section sorted independently.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Two lists have been pasted into what should be one table and the user wants to sort all rows by the same column.
- Observed behavior
- Excel detects separate ranges and sorts each pasted list independently, usually because blank rows, spaces, or hidden characters interrupt the data.
Check that every row uses the same columns and that the table has only one header row. Turn on filters or use the Name Box to inspect the full range before applying a sort.
Remove gaps and clean pasted text before sorting the full table
Use this method when pasted data contains blank rows, leading spaces, or invisible characters that make Excel treat one table as multiple lists.
A blank row can split a dataset into separate regions. Text copied from websites or Word may also contain leading spaces or nonprinting characters that affect sorting and matching.
Select the table area, then use Home > Find & Select > Go To Special > Blanks. Right-click a selected blank cell and choose Delete > Entire row, but only if those blank rows are not intentionally separating records.
In an empty column beside the affected text, enter =TRIM(CLEAN(A2)), replacing A2 with the first cell that needs cleaning. Press Enter and fill the formula down through all relevant rows.
Copy the helper-column results, select the original column, then use Home > Paste > Paste Values to replace the pasted text without formulas.
Select every column and row in the cleaned table, including its header row. Go to Data > Sort, choose the column to sort by, select the order, and confirm that My data has headers is enabled when applicable.

Check for hidden rows, filters, and spaces at the start of cells
Use this check when the table looks continuous but part of it still does not move with the rest during sorting.
Clean pasted lists and sort one complete table in WPS Spreadsheet
WPS Spreadsheet lets you remove interruptions in pasted data, clean text with formulas, and sort the entire selected range from one familiar spreadsheet workspace.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, open the Excel workbook, and select the worksheet containing the pasted lists.
- 2. Remove blank rows within the table: Select the data area and delete unintended blank rows so all records form one continuous range.
- 3. Clean text in a helper column: Enter =TRIM(CLEAN(A2)) beside the first affected value, then fill the formula down to clean copied text.
- 4. Paste cleaned values over the original column: Copy the cleaned results, select the original cells, and choose Paste Special > Values.
- 5. Sort the complete data range: Select the entire table, choose Data > Sort, set the sort column and order, and confirm the header setting before sorting.

Frequently Asked Questions
Why does Excel sort only part of my pasted data?
Excel commonly stops at a blank row or another interruption and treats the data below it as a separate range. Delete unintended blank rows, then select the complete table before choosing Data > Sort.
How can I remove leading spaces from pasted names in Excel?
Use a helper-column formula such as =TRIM(A2) for ordinary extra spaces. For text copied from Word or another source, use =TRIM(CLEAN(A2)), fill it down, then copy and paste the results as values.
Does CLEAN remove every hidden character from copied text?
CLEAN removes many nonprinting characters, but some copied web text may contain nonbreaking spaces. If TRIM(CLEAN(A2)) does not work, replace the nonbreaking space with a normal space using SUBSTITUTE before trimming.
Should I select one cell or the entire table before sorting?
Select the entire contiguous table whenever Excel has previously split the sort into separate sections. Include all related columns so row values remain together during the sort.
Can filtered or hidden rows affect sorting?
Yes. Clear filters from Data > Clear and unhide relevant rows or columns before checking the range and sorting. This helps you confirm that all intended records are included.




