Excel Sorting Data Incorrectly? 11 Fixes That Work

A laptop on a bright desk showing a colourful spreadsheet grid with arrow shapes pointing up and down, a calculator and notebook beside it — excel sorting data incorrectly fix guide
A sort that scrambles rows usually means Excel sorted one column on its own or read numbers as text — both can be fixed without retyping the data.

You click Sort, and the names no longer match the numbers. Rows that belonged together are scattered, 10 appears before 2, or the header row vanishes into the middle of the data. When excel sorting data incorrectly scrambles a table you trusted, the damage looks serious — but the data itself is usually untouched, and the order can be repaired once the cause is found.

The good news is that sorting faults follow a short list of causes, and each one has a clear fix. Excel sorts exactly what you select, exactly as the data is stored: one column selected alone sorts alone, numbers stored as text sort like words, a wrong header setting moves the labels, and hidden rows get left behind. This guide works through each cause in order, from the selection to the data itself. Work on a copy of the file while you test, so a wrong sort never costs you the original order.

Table of Contents

  • Why This Error Happens
  • Quick Checks
  • Diagnosing the Problem
  • Fix 1: Expand the Selection So Whole Rows Sort Together
  • Fix 2: Set My Data Has Headers to Match Your Table
  • Fix 3: Convert Numbers Stored as Text to Real Numbers
  • Fix 4: Remove Leading Spaces and Fix Mixed Formats
  • Fix 5: Clear Filters and Unhide Hidden Rows and Columns
  • Fix 6: Remove Blank Rows and Columns That Split the Table
  • Fix 7: Remove Merged Cells from the Sort Range
  • Fix 8: Fix Dates and Times Stored as Text
  • Fix 9: Use the Sort Dialog for Multi-Level and Case-Sensitive Sorts
  • Fix 10: Sort a Copy When Formulas Depend on Row Order
  • Fix 11: Copy the Data to a Fresh Workbook and Update Excel
  • What to Avoid
  • Frequently Asked Questions
  • Conclusion
  • Helpful Official Resources

Why This Error Happens

Sorting rearranges rows by the values in one or more columns. Excel decides the new order from the values as stored, the cells you selected, and the options you chose — and a wrong choice in any of those three places produces a wrong order that looks like a fault. These are the causes found most often.

1. Only One Column Was Selected

The most damaging cause is also the most common: sorting a single column on its own. When only one column is selected, Excel offers to expand the selection to the whole table — and if that offer is dismissed, or the sort is run from a right-click on one column, the single column sorts alone while the other columns stay put. Names no longer match numbers, dates no longer match events, and the table is scrambled. The data is still all there, but the rows no longer mean what they meant. Expanding the selection before sorting, covered in Fix 1, prevents this completely.

2. Numbers Are Stored as Text

A number stored as text looks like a number but behaves like a word. Text sorts character by character, so 10 comes before 2, because the first character 1 comes before 2. Real numbers sort by value, so 2 comes before 10. Data pasted from a website, imported from another system, or typed with a leading apostrophe often arrives as text, and the green triangle in the corner of the cell is the usual clue. Mixed columns — some real numbers, some text — sort in two groups, which looks random until you know what to look for. Fix 3 converts the whole column to real numbers.

3. The Header Setting Does Not Match the Table

Excel asks, in effect, whether the first row holds labels or data. The My data has headers setting answers that question, and a wrong answer scrambles the sort in two directions. If the setting is off but the table has headers, the header row sorts into the data and the labels end up in the middle of the table. If the setting is on but the table has no headers, the first row of data is treated as a label and never moves, staying frozen at the top while everything else sorts around it. Fix 2 sets the option to match the table you actually have.

4. Leading Spaces and Mixed Formats Change the Order

A space at the start of a value sorts before letters and numbers, so ” Smith” lands far from “Smith” even though they look identical. Trailing spaces, non-breaking spaces pasted from web pages, and mixed formats — some cells formatted as text, some as numbers, some as dates — all split the sort into unexpected groups. The values look the same on screen, which is why this cause is so confusing: the table appears to sort randomly, when it is really sorting faithfully by values that differ invisibly. Fix 4 cleans the spaces and unifies the formats before sorting.

5. Hidden and Filtered Rows Are Left Behind

A sort moves visible rows, and rows hidden by a filter or hidden by hand can be left out of the rearrangement, staying in their old positions while the visible rows sort around them. The result is a table that is sorted in patches, with unsorted rows sitting in the middle. Filters are the usual culprit: a filter left on from earlier work hides rows silently, and the sort runs on the visible set only. Clearing the filter and unhiding every row before sorting, as Fix 5 explains, makes the sort cover the whole table.

6. Blank Rows, Blank Columns, and Merged Cells Break the Range

Excel guesses the sort range from the block of data around the selected cell, and a blank row or blank column reads as the edge of the table. Data beyond the blank is left out of the sort entirely, so half the table sorts and half stays put. Merged cells break sorting differently: Excel cannot sort a range containing merged cells of different sizes, and either refuses the sort or produces a jumbled result. Both problems come from the shape of the table rather than the values in it, and Fixes 6 and 7 reshape the table so the sort can run cleanly.

Quick Checks

Two minutes before the deep fixes.

  • Make a copy of the file first — can you undo a wrong sort with Ctrl + Z if it goes wrong?
  • Click inside the table — does Excel select the whole table, or only one column?
  • Look for green triangles in the corner of cells — are numbers stored as text?
  • Check the header row — does the table have labels, and is the header setting right?
  • Look for filter arrows and hidden row numbers — is a filter on, or are rows hidden?
  • Think about when it started — did excel sorting data incorrectly begin after pasting data from a website or another system?

Diagnosing the Problem

SymptomCauseGo to Fix
Names no longer match numbersOne column sorted aloneFix 1
Header row sorted into the dataHeader setting is wrongFix 2
10 appears before 2Numbers stored as textFix 3
Same-looking values sort apartLeading spaces or mixed formatsFix 4
Table sorted in patchesFilter on or rows hiddenFix 5
Half the table did not sortBlank row or column splits the rangeFix 6
Excel refuses to sort at allMerged cells in the rangeFix 7
Dates sort alphabeticallyDates stored as textFix 8

Fix 1: Expand the Selection So Whole Rows Sort Together

Start here, because a single-column sort is the most damaging mistake and the easiest to prevent. Expanding the selection tells Excel to move whole rows together, so every value stays with the row it belongs to.

Hands arranging printed table sheets on a desk beside a laptop showing spreadsheet shapes — excel sorting data incorrectly fix guide
Select the whole table before sorting, including every column that belongs to each row, so rows stay together.
  1. Save a copy of the file first, so the original order is safe while you test.
  2. Click any single cell inside the table — do not select a whole column.
  3. Click the Data tab, then click Sort A to Z or Sort Z to A.
  4. If Excel shows a Sort Warning dialog, choose Expand the selection and click Sort.
  5. Check the result: every row should still make sense, with names matching numbers.
  6. If the rows are scrambled, press Ctrl + Z at once to undo the sort.
  7. Repeat the sort with the whole table selected, by clicking the corner of the table and dragging across all columns.

The Sort Warning dialog is the safety net: it appears when Excel suspects you selected only part of the table, and the Expand the selection option is almost always the right choice. The dangerous option in that dialog is Continue with the current selection, which sorts the selected cells alone — choose it only when you truly want one column rearranged independently, which is rare. Sorting from a right-click inside one column skips the warning in some cases, so prefer the Data tab buttons, which always check the selection first. If a sort has already scrambled the table, Ctrl + Z undoes it as long as the file has not been saved and closed since — another reason to work on a copy.

Fix 2: Set My Data Has Headers to Match Your Table

The header setting tells Excel whether the first row holds labels. Getting it right keeps the labels at the top and lets every data row move freely; getting it wrong freezes a data row at the top or buries the labels in the data.

A laptop showing spreadsheet grid shapes with one column highlighted in a soft colour — excel sorting data incorrectly fix guide
A header row tells Excel which row holds the labels — sorting with the wrong header setting moves the labels into the data.
  1. Click any cell inside the table.
  2. Click the Data tab, then click Sort to open the Sort dialog.
  3. Look at the top-right of the dialog for My data has headers.
  4. If your table has a label row, tick the box — the column names in the dialog should show your labels.
  5. If your table has no label row, untick the box — the dialog should show Column A, Column B, and so on.
  6. Choose the column to sort by, and the order, then click OK.
  7. Check that the label row stayed at the top and every data row moved.

The dialog itself shows whether the setting is right: with the box ticked, the sort-by list shows your header labels; with it unticked, it shows generic column letters. Use that preview as the check, rather than guessing. Tables copied from elsewhere often arrive with a blank first row, a title row above the headers, or two header rows — each of which confuses the setting. Remove title rows and blank rows above the headers before sorting, so the first row of the range is the true header row. A table with no headers at all sorts fine with the box unticked, as long as the setting matches the reality of the table.

Fix 3: Convert Numbers Stored as Text to Real Numbers

Numbers stored as text sort alphabetically, so 10 lands before 2 and 100 lands before 20. Converting the column to real numbers restores value order, and the conversion takes seconds once the text cells are found.

  1. Select the column that sorts in the wrong order.
  2. Look for green triangles in the top-left corner of cells — those cells hold text, not numbers.
  3. Click a cell with a triangle, then click the warning icon that appears next to it.
  4. Choose Convert to Number from the menu.
  5. For a whole column at once, select the column, then click Data, then Text to Columns.
  6. In the wizard, click Next twice, choose General as the column format, and click Finish.
  7. Sort the column again and check that 2 now comes before 10.

The Text to Columns route converts a whole column in one pass, including cells with no visible triangle, and it is the reliable method for imported data. A second quick method works for small ranges: type the number 1 in an empty cell, copy it, select the text numbers, right-click, choose Paste Special, choose Multiply, and click OK — multiplying text numbers by 1 forces Excel to store them as real numbers. After converting, check the alignment: real numbers align to the right by default, while text aligns to the left, so a column that suddenly aligns right has converted correctly. Re-sort only after the conversion, because sorting before converting bakes the text order into the table.

Fix 4: Remove Leading Spaces and Fix Mixed Formats

Invisible spaces and mixed formats split a sort into groups that look random. A leading space sorts before every letter, a trailing space separates identical values, and a column mixing text and number formats sorts each group apart. Cleaning the values before sorting removes the invisible differences.

  1. Select the column that sorts oddly, and check a few cells in the formula bar for leading spaces.
  2. In a helper column, type the formula =TRIM(A2) for the first data cell, adjusting the cell reference to your table.
  3. Fill the formula down the helper column to cover all rows.
  4. Copy the helper column, then right-click the original column and choose Paste Special, then Values.
  5. Delete the helper column.
  6. Select the cleaned column, right-click, choose Format Cells, and set one format for the whole column — Number, Text, or Date, as the data requires.
  7. Sort the column again and check that matching values now sit together.

TRIM removes leading and trailing spaces and reduces repeated spaces between words to one, which fixes the common cases. Spaces pasted from web pages are sometimes non-breaking spaces, which TRIM leaves behind — for those, use Find and Replace with the non-breaking space character, or the CLEAN function alongside TRIM. Mixed formats need the Format Cells step: a column where half the cells are formatted as text and half as numbers will sort in two groups even after the values are cleaned. Set the format first, then re-enter or convert any value that still behaves as the old type. After cleaning, the sort should group identical values together with no strays.

Fix 5: Clear Filters and Unhide Hidden Rows and Columns

A filter left on, or rows hidden by hand, leaves part of the table out of the sort. The visible rows sort, the hidden rows stay, and the table ends up sorted in patches. Clearing every filter and unhiding every row before sorting makes the sort cover the whole table.

A person checking a spreadsheet on a laptop with a notebook of handwritten numbers beside it — excel sorting data incorrectly fix guide
Hidden rows and filtered rows can be left behind by a sort — clear filters and unhide rows before you sort the full table.
  1. Click the Data tab and check whether Filter is highlighted — if it is, a filter is active.
  2. Click Clear in the Sort and Filter group to remove all filter conditions.
  3. Click Filter again to turn the filter arrows off completely.
  4. Select the whole sheet by clicking the corner button above row 1 and left of column A.
  5. Right-click any row number and choose Unhide, then right-click any column letter and choose Unhide.
  6. Check the row numbers down the left side — they should run without gaps.
  7. Sort the table again and check that the order is consistent from top to bottom.

Row numbers that skip — 1, 2, 5, 6 — reveal hidden rows, and filter arrows in the header row reveal an active filter. Both are easy to miss on a busy sheet, especially a filter set by someone else or left over from earlier work. Sorting with a filter on is sometimes deliberate, but it should never be accidental: clear the filter, sort the whole table, then reapply the filter if you need it. If the filter arrows themselves misbehave, or the drop-down shows no values, our guide to Excel filter dropdown not working covers filter faults. After the sort, you can hide rows again if the presentation needs it — the order will already be correct.

Fix 6: Remove Blank Rows and Columns That Split the Table

Excel guesses the sort range from the unbroken block of data around the selected cell. A blank row or blank column reads as the edge of the table, so data beyond the blank is left out of the sort. The top half sorts, the bottom half stays, and the table looks half-sorted.

  1. Click inside the table and press Ctrl + A to select the current region.
  2. Check whether the selection covers the whole table, or stops at a blank row or column.
  3. Delete blank rows inside the table: right-click the row number and choose Delete.
  4. Delete blank columns inside the table the same way.
  5. If blank rows separate sections deliberately, sort each section separately, one at a time.
  6. Re-select the table and press Ctrl + A again to confirm the selection now covers everything.
  7. Sort the table and check that rows from the bottom of the table moved into their correct places.

Blank rows used as spacing are the usual cause: a blank row between groups, a blank row under the headers, or trailing blank rows at the foot of the table. Spacing belongs in the formatting — row height and borders — not in empty rows inside the data. A blank row at the very bottom, after the last data row, is harmless, because nothing sits beyond it. The dangerous blanks are the ones in the middle, splitting the table into two ranges that sort separately. When excel sorting data incorrectly affects only part of a table, a blank row in the middle is the first place to look.

Fix 7: Remove Merged Cells from the Sort Range

Merged cells and sorting do not mix. Excel cannot sort a range where merged cells differ in size, and it either refuses the sort with a warning or produces a jumbled result. Removing the merges, while keeping the values, lets the sort run cleanly.

  1. Select the whole table.
  2. Click the Home tab, then click the arrow next to Merge and Center, and choose Unmerge Cells.
  3. Check the unmerged area: the value stays in the top-left cell, and the other cells become empty.
  4. Fill any empty cells that need the value, by copying the value down the group.
  5. If the merges were used for headings, replace them with Center Across Selection alignment instead.
  6. Select the table again and sort it.
  7. Check that the sort runs without a merged-cell warning.

Center Across Selection gives the visual effect of a merged heading without merging the cells, and it sorts safely. Find it in Format Cells, on the Alignment tab, in the horizontal alignment list. Merged cells inside the data area — merged name cells spanning two rows, for example — are the worst case, because unmerging them leaves empty cells that must be filled before the sort makes sense. Fill them from the value above, so every row carries its own complete data. A table with no merged cells anywhere in the range is the only shape that sorts reliably every time.

Fix 8: Fix Dates and Times Stored as Text

Dates stored as text sort alphabetically, so April lands before February and 2024 lands before 2025 in the wrong places. Real dates sort chronologically. The fix is the same idea as Fix 3 — convert the text to real values — applied to dates and times.

A laptop and a second monitor on a desk showing spreadsheet grids with small filter arrow shapes — excel sorting data incorrectly fix guide
Dates stored as text sort alphabetically instead of chronologically — converting them to real dates restores the correct order.
  1. Select the date column and check the alignment: text dates align left, real dates align right.
  2. Click a date cell and look at the formula bar — a real date shows as a date, while text may show with an apostrophe or extra spaces.
  3. Select the column, click Data, then Text to Columns.
  4. Click Next twice, choose Date, and pick the date order that matches your data, such as day-month-year.
  5. Click Finish to convert the column to real dates.
  6. Select the converted column, right-click, choose Format Cells, and pick a date format that shows the order clearly.
  7. Sort the column and check that the earliest date is first and the latest is last.

The date order setting in step 4 matters: data written day-month-year, converted as month-day-year, swaps days and months and produces wrong dates that sort confidently and wrongly. Check a known date after converting — a date where the day is above 12 cannot be mistaken for a month, so it proves the order is right. Times stored as text need the same treatment, and mixed date-and-time values should be converted together, not split apart. If some dates refuse to convert, check for extra spaces with Fix 4, or for dates typed with dots or slashes the converter does not expect. A column of real dates sorts chronologically every time, with no exceptions.

Fix 9: Use the Sort Dialog for Multi-Level and Case-Sensitive Sorts

The quick A-to-Z button sorts by one column only. When the order depends on two columns — surname, then first name, for example — or when capital letters must sort separately from small letters, the full Sort dialog is the right tool. It also shows exactly what will happen before the sort runs.

  1. Click any cell inside the table.
  2. Click the Data tab, then click Sort to open the Sort dialog.
  3. Check My data has headers matches your table, as in Fix 2.
  4. Choose the first column in Sort by, and the order — A to Z, Z to A, smallest to largest, or largest to smallest.
  5. Click Add Level and choose the second column, for rows tied on the first column.
  6. Click Options in the dialog to set Case sensitive sorting, if capitals must sort separately.
  7. Click OK, then check the result row by row for the first few groups.

Levels run in order: the first level decides the main groups, the second level orders rows inside each group, and further levels break deeper ties. Without a second level, tied rows keep whatever order they happened to be in, which looks arbitrary. Case sensitive sorting puts capital letters before small letters, which matters for codes and names where case carries meaning — leave it off for ordinary text, where case should not affect the order. The dialog also offers sorting by cell colour or font colour, useful for tables where colour marks priority. If a PivotTable built on this data shows a stale order after sorting, the report may need a refresh rather than another sort, and our guide to Excel pivot table not refreshing covers that side.

Fix 10: Sort a Copy When Formulas Depend on Row Order

Most formulas survive sorting without trouble, because Excel adjusts their references as rows move. Formulas that depend on row position — running totals, formulas pointing at the row above, and formulas using fixed ranges — can break or change meaning when the rows move. Sorting a copy, with values pasted first, keeps the original safe and the results honest.

  1. Save a copy of the file and work in the copy.
  2. Select the range that holds formulas depending on row order.
  3. Copy it with Ctrl + C.
  4. Right-click the same range and choose Paste Special, then Values, to replace formulas with their results.
  5. Check that the pasted values match the formula results shown before.
  6. Sort the copy and check that the values still make sense row by row.
  7. Keep the original file untouched, with its formulas intact, for future edits.

Paste Special Values freezes the results at sorting time, so the sorted copy shows the values as they were, not recalculated for the new row positions. This is the right approach for a report or a one-time sort. For a living workbook that must keep working after the sort, rewrite the position-dependent formulas to use references that survive sorting — for example, structured references in an Excel Table, or formulas that look up values by key rather than by row position. Dynamic array formulas that spill across cells need extra care: sorting a spill range directly can produce errors, because the spill area is owned by the formula. If a spill error appears, our guide to Excel spill error fix covers the spill side. Never sort the only copy of a formula-heavy workbook without a backup.

Fix 11: Copy the Data to a Fresh Workbook and Update Excel

When sorting misbehaves in one workbook but works in every other, the workbook itself may carry damaged settings, corrupted styles, or a damaged calculation chain. Copying the data to a fresh workbook leaves the damage behind, and updating Excel rules out a version bug.

  1. Open the faulty workbook and select the whole table.
  2. Copy it with Ctrl + C.
  3. Open a new blank workbook and paste with Ctrl + V.
  4. If formatting causes trouble, paste again with Paste Special, then Values, and reapply formatting by hand.
  5. Sort the table in the fresh workbook and check the order.
  6. If the sort works in the fresh workbook, keep the new file and retire the old one.
  7. Update Excel: click File, then Account, then Update Options, then Update Now.

Copying values only, with Paste Special Values, drops formulas, formatting, and hidden settings together, which is the cleanest break from a damaged workbook — but it also drops the formulas, so keep the original file as the formula reference. Copying normally keeps formulas and formatting, and is enough when the damage sits in workbook settings rather than the data. A workbook that opens blank, or shows nothing at all, is a related symptom of workbook damage, and our guide to Excel workbook opens blank covers it. If sorting still misbehaves in a fresh workbook, on clean data, after an update, the cause is the data itself — return to Fixes 3, 4, and 8 and check the values cell by cell.

What to Avoid

  • Do not sort a single column on its own — always expand the selection so whole rows move together.
  • Do not sort the only copy of an important table — make a copy first, so a wrong sort costs nothing.
  • Do not ignore the Sort Warning dialog — the expand option is almost always the right choice.
  • Do not sort with a filter on or rows hidden — clear filters and unhide everything first.
  • Do not leave merged cells in a sort range — unmerge them and use Center Across Selection for headings.
  • Do not use pirated, cracked, or “free premium” Excel copies or repair tools — they are a common source of malware and damaged files.
  • Do not paste website data and sort it at once — clean spaces and convert text numbers first, or the order will mislead you.

Frequently Asked Questions

Why do my numbers sort as 1, 10, 100, 2, 20?

Because the numbers are stored as text, and text sorts character by character: 1 comes before 2, so every value starting with 1 lands before every value starting with 2. Convert the column to real numbers with Fix 3, and the order becomes 1, 2, 10, 20, 100. The green triangle in the corner of the cell is the usual clue, and data pasted from websites or imported from other systems arrives as text most often.

Why does sorting scramble my rows even though I selected the whole table?

The selection probably stopped at a blank row or blank column, so only part of the table sorted, or hidden rows were left behind. Press Ctrl + A inside the table and check that the selection covers every row and column, clear filters, and unhide hidden rows with Fix 5. When excel sorting data incorrectly scrambles rows despite a careful selection, the range guess is the usual culprit, and Fix 6 covers the blank-row cause.

Can I undo a sort that went wrong?

Yes, if the file has not been saved and closed since the sort. Press Ctrl + Z immediately, and the rows return to their previous order. Once the file is saved and closed, the undo history is gone, and the original order can only be restored from a backup copy. This is why every fix in this guide starts with making a copy: a backup turns a scrambled sort from a disaster into a shrug.

Why does the header row end up in the middle of my data?

Because the My data has headers setting is off while the table has a header row, so Excel treats the labels as data and sorts them with everything else. Open the Sort dialog, tick the box, and sort again — Fix 2 walks through it. The reverse mistake freezes the first data row at the top, because Excel treats it as a label. The setting must match the table: headers present means the box ticked, no headers means the box unticked.

Why do dates sort in the wrong order even though they look right?

Because the dates are stored as text, and text sorts alphabetically: month names sort by their first letter, and day numbers sort character by character. Convert the column to real dates with Fix 8, choosing the date order that matches your data, and the sort becomes chronological. Check a date with a day above 12 after converting, because it proves the day-month order was read correctly.

Should I sort data that contains formulas?

Usually yes, but with care. Most formulas adjust their references as rows move and survive sorting fine. Formulas that depend on row position — running totals and references to the row above — can change meaning after a sort. For a one-time sort, work on a copy and paste values first with Fix 10. For a living workbook, use an Excel Table with structured references, which keeps formulas correct as rows move.

Conclusion

When excel sorting data incorrectly scrambles a table, the cause is almost always the selection, the header setting, numbers stored as text, or hidden rows — not a broken spreadsheet. Expand the selection so whole rows move together, match the header setting to the table, convert text numbers and text dates to real values, and clear filters and hidden rows before sorting. Finish by checking the first and last rows of the sorted table against the original copy, with every row still making sense — that is the only proof that counts. Set it, test it, and trust the order.

Helpful Official Resources

2 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *