Excel Sort and Filter Not Working: Causes and Fixes

Part of the free Module 4: Sort and Filter · Lesson 11 of 11 · Full Excel course

Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 unless noted.

When Excel sort and filter is not working, the cause is almost always in the data, not in Excel: a blank row that ends the range, merged cells, numbers stored as text, dates stored as text, or a protected sheet. This lesson walks through each cause with its fix, then covers the two Sort Options most people never find: sort left to right and case-sensitive sort.

Table of Contents

How Excel decides what to sort or filter

When you click one cell and run Sort or Filter, Excel selects the current region: every cell connected to the active cell by non-empty cells. It stops at the first completely blank row or column. It then treats the top row as headers if that row looks different from the rest (text above numbers, bold formatting). Most sort and filter problems come from breaking one of these assumptions. Press Ctrl+A once inside your data to see exactly what Excel considers the current region before you sort.

Problem 1: Sort or filter ignores some rows

A blank row or column inside the data splits it into two regions, and Excel only works on the one that holds the active cell. Rows below the gap keep their old order or never get filter arrows.

Press Ctrl+A inside the data and check the selection border. If it stops early, there is a gap.
Select the whole data block by hand (click the first header, then Ctrl+Shift+End), or delete the blank rows: select the ID column, Home > Find & Select > Go To Special > Blanks, then Home > Delete > Delete Sheet Rows.
Sort or filter again.

A permanent fix is to convert the range to an Excel Table with Ctrl+T; the table boundary, not the blank rows, defines the range from then on (see the Excel Table lesson).

Problem 2: “To do this, all the merged cells need to be the same size”

Merged cells anywhere in the sort range stop the sort. Excel cannot move a row that is partly merged with the row above it. Filtering still works, but a merged cell only carries its value in the top-left cell, so every other row of the merge looks blank to the filter.

Select the data and look at Home > Alignment > Merge & Center. If the button is highlighted for any selection, merges exist.
To locate them, press Ctrl+F, click Format, tick Merge cells on the Alignment tab and choose Find All.
Select the whole sheet and click Merge & Center once to unmerge everything.
For headings that must span columns, use Format Cells > Alignment > Horizontal > Center Across Selection, which looks identical and does not block sorting.
Fill the gaps the unmerge leaves: select the column, Go To Special > Blanks, type =, press Up Arrow, then Ctrl+Enter.

Problem 3: Numbers sort as text (10 before 2)

Values imported from a system or typed with an apostrophe are text. Excel sorts all real numbers first, then all text, and inside the text group “10” comes before “2” because it compares the first character. A green triangle in the corner of the cell and left alignment are the usual signs.

Select the column. If a warning icon appears, click it and choose Convert to Number.
Otherwise go to Data > Data Tools > Text to Columns and click Finish straight away. This re-enters every cell as a number.
Alternative: type 1 in a spare cell, copy it, select the column, Paste Special > Multiply.
Sort again. If Excel shows the Sort Warning “Sort anything that looks like a number, as a number”, choose that option.

Dates stored as text behave the same way: 1-Apr sorts before 3-Jan because “1” is less than “3”. Text to Columns with the column data format set to Date fixes them, and the Date Filters submenu reappears in the drop-down once the values are real dates.

Problem 4: Filter or Sort button is greyed out

Symptom	Cause	Fix
Sort & Filter group is disabled	Worksheet is protected	Review > Unprotect Sheet, or ask the owner to allow AutoFilter and Sort in the protection options
Commands disabled on every sheet	Several sheets are grouped (tab names show [Group])	Right-click a sheet tab > Ungroup Sheets
Filter works on one range only	A worksheet allows one AutoFilter range	Convert each data block to a Table (Ctrl+T); each table has its own filter
Cannot sort inside a shared workbook	Legacy Shared Workbook mode limits editing	Review > Share Workbook > untick sharing, or move to co-authoring in OneDrive
Filter arrows appear but the list is empty	Active cell was on a blank row; the header row is not what you expect	Click a cell inside the data and press Ctrl+Shift+L twice

Problem 5: Filter shows the wrong results after edits

AutoFilter is a snapshot. When you change a value in a filtered column, the row does not appear or disappear until you press Ctrl+Alt+L or choose Data > Sort & Filter > Reapply. Rows added below a filtered range are also outside the filter until you reapply; in an Excel Table they are included automatically. Formulas such as SUM ignore the filter entirely and keep adding hidden rows; use SUBTOTAL or AGGREGATE as explained in the SUBTOTAL and AGGREGATE lesson.

Problem 6: Hidden rows and hidden columns

Manually hidden rows are still sorted and can reappear in a new position, which surprises people who hid them to keep them out of the way. Manually hidden columns are sorted with the rest of the row, but a hidden column cannot be chosen in the Sort dialog until you unhide it. When you copy a filtered range, only visible rows are copied; when you copy a range with manually hidden rows, everything is copied unless you press Alt+; (Select Visible Cells) first.

Sort Options: left to right and case-sensitive

The Options button in the Sort dialog (Data > Sort & Filter > Sort) opens the Sort Options box with two settings that solve specific problems.

Sort left to right (sort columns by a row)

Use this when your data runs across the sheet, for example months as column headers and you want them in order, or you want columns arranged by a total in a row.

Select the range to sort. Do not include row labels in the first column if they should stay put.
Go to Data > Sort and click Options.
Choose Sort left to right and click OK. The Sort by list now shows row numbers instead of column headers.
Pick the row that holds the values to sort by, set the order and click OK.

In Excel 365 and 2021 the formula equivalent is =SORT(A1:M10,3,-1,TRUE), where the final TRUE sorts by column.

Case-sensitive sort

By default Excel treats “apple” and “Apple” as equal. Tick Case sensitive in Sort Options and lower-case comes before upper-case in an ascending sort, so “apple” sorts above “Apple”. Product codes such as ab12 and AB12 are the usual reason to need this.

Worked example

A monthly summary has months across the top, but they were entered in the order the reports arrived:

	Mar	Jan	Apr	Feb
Sort key	3	1	4	2
Sales	420	510	630	290

Select B1:E3, open Data > Sort > Options, choose Sort left to right, then set Sort by to Row 2, Smallest to Largest, and click OK. The columns rearrange to Jan, Feb, Mar, Apr with the sales figures travelling with their months. Without the helper row you could instead choose Custom List in the Order box and pick the built-in Jan, Feb, Mar list.

Tips and common mistakes

Press Ctrl+A first. If the selection border does not cover the whole data set, fix the gap before sorting or filtering.
Never merge cells inside data. Use Center Across Selection for headings that must span columns.
Convert text numbers and text dates with Text to Columns before any sort; it takes five seconds and fixes filters as well.
Convert to a Table (Ctrl+T) to stop range-detection problems for good and to keep new rows inside the filter.
Reapply after edits. Ctrl+Alt+L refreshes a filter that shows stale results.
Sort left to right needs the Options button; it is not in the quick sort buttons or the filter drop-down.
Undo immediately. Ctrl+Z reverses a bad sort only while the workbook is open; keep a serial number column for anything important.

Errors and how to fix them

Message or symptom	Cause	Fix
“To do this, all the merged cells need to be the same size”	Merged cells in the range	Unmerge with Home > Merge & Center, use Center Across Selection
“The sort reference is not valid”	Sort key column is outside the selection or the selection is not contiguous	Select one block that includes the sort column
Sort Warning: “Sort anything that looks like a number, as a number”	Mixed numbers and text numbers	Choose that option, then convert the column with Text to Columns
“Excel cannot complete this task with available resources”	Sorting a whole-column selection or an enormous range with formulas	Select only the data, or convert formulas to values first
Date Filters missing from the drop-down	Dates stored as text	Text to Columns with Date format, then reapply
Header row gets sorted into the data	Excel did not recognise the header	Tick My data has headers in the Sort dialog

Practice exercise

Open the practice file and try the following:

Insert a blank row in the middle of the data, click above it and press Ctrl+A. Note where the selection stops, then delete the row.
Merge two cells in the Location column, try to sort, read the message, then unmerge and use Center Across Selection instead.
Type an apostrophe before three Sales values, sort Largest to Smallest, observe where they land, then fix them with Text to Columns.
Copy the header row to a new sheet, transpose it with Paste Special > Transpose, and use Sort left to right to arrange the columns alphabetically.
Type “delhi” in one Location cell and run a case-sensitive sort to see where it lands.

Key takeaways

Excel sorts and filters the current region; blank rows, blank columns and merged cells break it.
Numbers and dates stored as text sort and filter wrongly; Text to Columns converts them in one step.
Greyed-out commands mean a protected sheet, grouped sheets or a shared workbook.
AutoFilter does not refresh itself; press Ctrl+Alt+L after edits, or use an Excel Table.
Sort Options holds Sort left to right and Case sensitive, both missing from the quick sort buttons.

Related lessons

Frequently asked questions

Why is my Excel filter not showing all data?

A blank row or column inside the data ends the AutoFilter range, so rows beyond it are left out. Delete the blank rows or select the whole block before pressing Ctrl+Shift+L. If rows were added after the filter was applied, press Ctrl+Alt+L to reapply, or convert the data to a Table so new rows join automatically.

Why can’t I sort in Excel because of merged cells?

Excel moves whole rows when it sorts, and a merged cell that spans two rows cannot be split between two positions. Unmerge the cells with Home > Merge & Center, fill in the values that were in the merged block, and use Center Across Selection for any heading that needs to span columns.

How do I sort left to right in Excel?

Select the range, open Data > Sort, click Options, choose Sort left to right and click OK. The Sort by box then lists row numbers; pick the row that holds the values to sort by and click OK. In Excel 365 the SORT function with its fourth argument set to TRUE does the same job with a formula.

How do I make an Excel sort case sensitive?

Open Data > Sort, click Options and tick Case sensitive. In an ascending sort lower-case letters then come before upper-case, so “abc” sorts above “ABC”. The setting applies only to the current sort and is off again the next time you open the dialog.

Why does Excel sort 1, 10, 11, 2 instead of 1, 2, 10, 11?

The values are text, so Excel compares them one character at a time. Select the column, use Data > Text to Columns > Finish to convert them to real numbers, then sort again. If the column has a green triangle warning, click it and choose Convert to Number for the same result.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.