Excel Sort Not Working? Causes and Quick Fixes

When Excel sort is not working, the sort itself is almost never broken. The problem is in the data: numbers stored as text, spaces you can't see, merged cells, or a blank row that cuts the table in two. This guide gives a quick checklist, then the fix for each cause, so you can sort with confidence in a report or a job test.

Quick checklist: why is Excel sort not working?#

Match what you see to the likely cause, then jump to the fix:

Numbers and dates sort in the wrong order#

Numbers stored as text sort like words: character by character, from the left. So "120" comes before "45", because 1 comes before 4. Text numbers usually sit on the left of the cell and may show a small green triangle in the corner.

To fix them, select the column, click the warning icon and choose Convert to Number. Or use a helper column with =VALUE(B2) and sort by that. Excel's Sort dialog may also warn that a column contains numbers formatted as text and offer to sort anything that looks like a number as a number. Converting the data is the safer long-term fix. The VALUE function guide and the article on numbers stored as text cover every method.

Dates have the same problem. A text date like "05/10/2026" sorts by its first characters, so all the 01s come first, whatever the month or year. Convert them with DATEVALUE or Text to Columns; the guide to converting text to dates walks through both.

Spaces and hidden characters push rows to the top#

A space sorts before any letter or digit. So " Zara", with a leading space, lands above "Adam" in an A to Z sort, and it looks like the sort failed. Data pasted from websites, PDFs and other systems often brings these spaces with it.

Clean the column in a helper column:

=TRIM(A2)

TRIM removes spaces at the start and end and turns double spaces into single ones. For line breaks and other non-printing characters, use =TRIM(CLEAN(A2)). Then paste the results as values over the original and sort again. Web data sometimes contains a non-breaking space (character 160) that TRIM can't remove; replace it first with SUBSTITUTE(A2,CHAR(160)," "). The TRIM function guide has more examples.

Rows get mixed up or only part of the data sorts#

If you select a single column and sort it, only that column moves. Names end up next to the wrong salaries, and the damage is easy to miss. Excel usually shows a Sort Warning asking whether to expand the selection. Always choose Expand the selection. If you've already sorted the wrong way, undo straight away with Ctrl+Z (Mac: ⌘Z).

The safest habit is to click one cell inside the table and then sort. Excel selects the whole block of data around it. That block stops at the first completely blank row or column, so a blank row in the middle means only the top half gets sorted. Delete the blank rows, or select the full range yourself before sorting.

Two more checks:

• Header row sorted into the data. In Data > Sort, tick My data has headers.
• Formulas that change after sorting. Formulas that point at cells in other rows, or at other sheets, can give new answers once the rows move. Paste them as values before sorting if the results must stay put.

Sort is greyed out or shows a merged cells error#

If the Sort buttons are greyed out, check three things. You may still be editing a cell (press Esc). The sheet may be protected (Review > Unprotect Sheet). Or several sheets may be grouped, which shows "Group" in the title bar (right-click a sheet tab > Ungroup Sheets).

If Excel complains that merged cells must all be the same size, the range has merged cells of different sizes. Select the range, click Home > Merge & Center to unmerge, and fill in any blanks. If you only wanted a centred title, use Center Across Selection instead. The article on Merge and Center explains why merged cells cause so much trouble.

Months or sizes sort alphabetically#

When a column holds month names as text, A to Z gives April, August, December. That's correct alphabetical order, just not what you want. Real dates formatted to show the month sort correctly. For text such as Small, Medium, Large, use a custom list: Data > Sort > Order > Custom List. The guide to custom sort orders shows the steps, and how to sort data in Excel covers multi-column sorts.

Sort messy data with a formula instead#

In Excel 2021 and Microsoft 365, SORT and SORTBY return a sorted copy of the data in new cells and leave the original alone. SORTBY can also clean the sort key on the fly, so messy text prices still sort correctly:

The prices are text with stray spaces. A normal Z to A sort on column B puts Webcam first, because "9" is the highest first character. This formula sorts by the real numbers instead:

=SORTBY(A2:A7,VALUE(TRIM(B2:B7)),-1)

TRIM removes the spaces, VALUE turns the text into numbers, and -1 means largest first. The result spills down: Monitor, Office chair, Headset, Webcam, Desk lamp, Keyboard. The SORT function guide covers the simpler SORT, which sorts by a column of the same range.

Practise it#

Sorting and cleaning go together in almost every data task, and job tests often hide text numbers or extra spaces in the data on purpose. Practise the SORT function in a live spreadsheet: you type the formula and it checks it instantly. Then clean data in the cleaning text lesson and fix text numbers in the text and VALUE lesson, or try the exercise on this page first.