Excel Sorting Problems: Common Errors and Fixes

David sent me a file last year with a sort that looked completely wrong: numbers out of sequence, rows jumping around with no obvious pattern. He'd run the sort three times. Same broken result every time. I spotted the green triangles in the corner of his number cells within about ten seconds. Numbers stored as text. Excel wasn't malfunctioning. It was sorting exactly what it saw, a column of text strings that happened to look like numbers. In other words, Excel sorted "10" before "2" the same way a dictionary would sort words starting with "1" before words starting with "2."

That's the thing about Excel sorting problems: they almost never mean Excel is broken. They mean your data has something in it that Excel is responding to literally. This guide walks through the four most disruptive causes (numbers stored as text, merged cells, hidden rows, and formula recalculation) and shows you exactly how to fix each one. Most of these I've debugged personally over the past eighteen-plus years, including a decade of sorting and organizing large datasets as a staff accountant and financial analyst, where a wrong sort order in a report had real downstream consequences.

---

What to Check Before You Touch Excel's Sort Options

Before you change anything, select your full data range manually. If you click a single cell and let Excel guess where your data ends, it'll stop at the first blank row it finds. That guess is often wrong, and a partial sort is one of the most confusing Excel sorting problems to diagnose after the fact because the data looks almost right.

This guide covers the four issues that cause the most damage in real files: numbers stored as text sorting alphabetically instead of numerically, merged cells that block sorting entirely, hidden rows that stay pinned in place during a sort, and volatile formula values that change after the sort runs. Once you've confirmed your selection covers the full dataset, you're ready to start diagnosing.

If you're new to sorting in Excel and want a foundation before troubleshooting, the basic guide to sorting data in Excel is worth a quick read first.

---

Step 1: Diagnose Why Your Excel Sort Is Not Working Before You Change Anything

Before applying any fix, run two quick checks. They take thirty seconds and will rule out the two most common blockers.

Check for merged cells first — they block sorting entirely

Merged cells are a sorting disaster. They break formulas, they wreck sorting, and the aesthetic benefit (making a header span across two columns) is not worth what they cost you downstream. If any cell in your sort range is merged, Excel will throw an error and refuse to sort. Full stop.

To fix it: select the affected column or the full range, go to Home → Merge & Center → Unmerge Cells. Then fill in the values that were lost from the merge (usually just copying the value from the first cell down into the newly empty ones). After that, your sort range is clean enough to proceed.

Look for blank rows that silently split your sort range

Blank rows are almost as disruptive, and sneakier, because Excel doesn't throw an error. It just stops your sort range at the blank row and sorts only the data above it, leaving everything below untouched. Excel data not sorting in the correct order is often traced back to a single blank row buried in the middle of a dataset.

Delete the blank rows, or if they're intentional section dividers, fill them with a placeholder value before sorting and remove it after. In Microsoft 365, you can use Go To Special (Ctrl+G → Special → Blanks) to select all blank cells in a range at once.

Always select your full data range manually before sorting. Letting Excel auto-detect the range is one of the most common reasons a sort appears to run but produces a partial or unexpected result.

---

Step 2: Fix Numbers Stored as Text So Excel Sorts Numerically Instead of Alphabetically

Once your range is clean and merged cells aren't blocking you, the next most common culprit is numbers formatted as text. This is the issue David ran into, and it's the reason Excel sorts 1, 10, 11, 2, 20 instead of 1, 2, 10, 11, 20. Excel sorting alphabetically instead of numerically almost always means your number column isn't actually storing numbers — it's storing text strings that look like numbers.

Spot the green triangle warning and convert in bulk

The diagnostic signal is the small green triangle in the top-left corner of the cell. Most people ignore it or don't know what it means. It means Excel has flagged a potential data issue: the cell contains a number formatted as text.

Three ways to fix it:

1. Select the flagged cells, click the yellow warning icon that appears, and choose Convert to Number. Fastest option when the green triangles are visible.
2. Type the number 1 into an empty cell, copy it, select your text-number range, and use Paste Special → Multiply. This forces Excel to treat every value as a number. Works on Windows and Mac.
3. Use =VALUE(A2) in a helper column, then paste the results as values over your original column.

The same problem causes Excel sort by date not working: dates imported from external sources or OneDrive-synced files often land as text strings, and the fix is identical.

Get in the habit of running =TRIM() on lookup values before sorting. A value that looks like "Chicago" but has a leading space sorts separately from "Chicago" — and you'll never see the difference visually.

---

Step 3: Handle Hidden Rows and Formula Values That Make Sorts Look Broken

With data types fixed, there are two more edge cases that rarely get covered anywhere — and both make a sort look broken when it technically ran fine.

Hidden rows

If you've manually hidden rows in your dataset (not filtered — hidden), those rows stay in their original position when you sort. The visible rows reorder correctly, but the hidden ones don't move. The result looks like random data is pinned in the wrong place. Unhide all rows before sorting. On Windows, Ctrl+Shift+9 unhides selected rows. On Mac, it's Command+Shift+9.

Hidden rows not sorting in Excel is a different problem from filter-hidden rows. Rows hidden by an active filter do get included in the sort operation. Manually hidden rows do not.

Formula recalculation

If you sort by a column that contains volatile functions (RAND() being the most common), Excel recalculates those values the moment the sort completes. So the sort ran correctly, but by the time you see the result, the values in that column have already changed. Excel custom sort not working correctly is sometimes exactly this issue. The fix: copy the formula column, paste it as values only, then sort. This locks the values before the sort touches them.

For sorting behavior on a Mac specifically, the guide to sorting and filtering in Excel on Mac covers platform-specific differences worth knowing.

---

Common Mistakes That Cause Excel Sorting Problems

Even after working through the steps above, three mistakes catch people repeatedly.

Always expand the sort selection to the full table. Sorting a single column in isolation scrambles your rows — and there's no obvious undo signal in the data itself.

The first is partial column sorting: selecting only one column in a multi-column table and running the sort. Excel asks whether you want to expand the selection. Always say yes. If you don't, that column reorders while every other column stays put, and your rows are now scrambled across the board.

The second is headers getting sorted into the data. In the Sort dialog, make sure My data has headers is checked. If it's unchecked, your header row gets treated as a data row and sorts right into the middle of your list.

Excel sort options greyed out? Check whether the worksheet has protection enabled under the Review tab. Protection locks sorting and filtering until it's removed or a password is entered.

The third is sorting while a filter is active. When rows are hidden by an active filter, sorting applies only to the visible rows, but those hidden rows are still in the dataset. The sort result can look inconsistent because you're seeing a filtered view of a sorted range. Clear the filter first, sort, then reapply it.

If you take one thing from this article: check your data before blaming the sort. Excel isn't wrong. It's showing you exactly what your data is doing.

---