Sorting and Filtering Together in Excel (2 Methods)

Your sort disappears the moment you apply a filter. You sorted a column ascending, added a filter to narrow the rows, and now the order looks completely wrong — or just random. It's not a bug. It's an order-of-operations problem, and once you understand how Excel processes sorting and filtering together, it stops happening.

There are two ways to solve this depending on your version of Excel. One works everywhere. The other — using nested formulas like SORT(FILTER()) — only works in Microsoft 365 and Excel 2021, and it produces results that update automatically every time your source data changes. This guide covers both, flags where each one breaks, and gives you the specific fixes for real datasets.

---

What You'll Be Able to Build — and Which Excel Version Determines How You Combine Sorting and Filtering Together

By the end of this guide, you'll know how to sort and filter data in a single workflow without losing your sort order, and how to do it with one formula if your version supports it. The manual UI method works in Excel 2013, 2016, 2019, and every version of Microsoft 365. The formula method — using the FILTER function and SORT function in Excel — requires Microsoft 365 or Excel 2021. Those are the only versions that support dynamic array formulas, which let a single formula spill results across a range automatically.

The strategic difference matters. Manual sorting rearranges your actual data. A dynamic array formula doesn't touch your source data at all: it outputs a sorted, filtered view in a separate range, and that view refreshes whenever the underlying data changes. For anyone managing a weekly report or a live operations dashboard, that distinction saves a lot of repeated work.

Not sure which version you have? Go to File → Account and check under "Product Information." If you see "Microsoft 365," both methods are available to you. If you see "Excel 2019" or earlier, use the manual approach in Step 1.

---

Step 1 — Sort Filtered Data in Excel Using the Manual UI Method (Works in Every Version)

Before anything else, convert your data range to an Excel Table. Press Ctrl+T with any cell inside your data selected, confirm the range, and make sure "My table has headers" is checked. This is the single most important structural decision you'll make. A named Table expands automatically when rows are added, keeps AutoFilter headers locked in place, and prevents the blank-row problem that silently breaks both sorts and filters. Excel stops reading data at the first blank row, so everything below it disappears from your results without any error message.

Once your Table is set up, follow this order of operations:

1. Apply your filter first. Click the dropdown arrow in the column header and set your filter criteria. For example, filter a Status column to show only "Open" rows.
2. Open the Custom Sort dialog. With the filter active, go to Data → Sort. Add your sort levels here (department ascending, then date descending, whatever the report requires).
3. Click OK. Excel sorts only the visible (filtered) rows.

The mistake that comes up constantly is doing this in reverse: sorting first, then filtering. It feels logical but produces wrong results. Sorting the full dataset first and then filtering can make your sort order appear to fall apart, because the filtered rows are no longer contiguous. Filter first, sort second — every time.

The shortcut Ctrl+Shift+L toggles AutoFilter on and off. If your filter dropdowns disappear unexpectedly, that's almost always the reason — someone hit it by accident.

If you're working on a Mac, keyboard shortcuts differ slightly. The guide to sorting and filtering in Excel on Mac covers the exact equivalents.

---

Step 2 — Nest SORT Inside FILTER to Combine Both in One Formula (Microsoft 365 and Excel 2021)

The manual method works, but it's a process you repeat. If your data updates daily or weekly, you're reapplying the same filter and sort every time. The formula approach eliminates that entirely.

How to Nest SORT Inside FILTER for a Single Sort Column

The syntax wraps FILTER inside SORT. FILTER returns an array of matching rows, and SORT orders that array before spilling it to the sheet.

=SORT(FILTER(A2:D100, C2:C100="Open"), 2, 1)

Breaking that down: FILTER(A2:D100, C2:C100="Open") returns only rows where column C equals "Open." SORT(..., 2, 1) then sorts those results by the second column ascending. The 1 means ascending; use -1 for descending. The results spill into whatever range is available below the formula cell — that's dynamic array spill range behavior, and it's automatic.

Because the FILTER function doesn't disturb your source data, your original table stays intact. The output is a live view, not a copy.

How to Use SORTBY and FILTER Together When You Need to Sort by an External Column

The SORT function sorts by a column within the returned array. The SORTBY function is more flexible: it lets you sort by a column that isn't included in the output, or by a column that exists entirely outside the filtered range.

=SORTBY(FILTER(A2:C100, C2:C100="Open"), B2:B100, 1)

Here, FILTER returns columns A through C for "Open" rows, and SORTBY orders the results by column B — even if B isn't the column you're displaying prominently. This is the SORTBY and FILTER combination that most tutorials skip, and it's genuinely useful when your sort key is a helper column or an adjacent reference column outside your output range.

For more complex multi-column sorts, the multi-level sorting guide covers how to stack sort levels both manually and within formulas.

---

Common Mistakes When You Combine Sorting and Filtering Together in Excel — and How to Fix Them

Four problems come up repeatedly.

Sorting before filtering in the UI. Covered above, but worth repeating: filter first, then sort. The reverse produces results that look correct until you compare them against the unfiltered data.

#SPILL! errors happen when the cells below your formula aren't empty. Excel needs clear space to spill the array output. Delete anything in the spill range — including what look like empty cells but might contain spaces or invisible characters. A trailing space in a single cell is enough to trigger it.

Using SORT or FILTER in Excel 2019 or earlier. Both functions are unavailable in Office 2019 and older. If you type =FILTER( and Excel doesn't recognize it, that's your version. Use the manual UI method from Step 1.

Confusing SORT with SORTBY. SORT orders by a position within the returned range. SORTBY sorts by any array you hand it, including columns outside your output. If your sort key isn't part of the filtered output columns, SORT will return a #VALUE! error — switch to SORTBY.

If you're newer to Excel and want a foundation before working through the formula approach, the Excel for Beginners starter guide is a good place to start.

---