Sorting in Excel sounds simple until you actually try to do it on a real spreadsheet. You hit the Sort A to Z button, and suddenly half your rows are misaligned, the totals row jumped into the middle, and the dates went from oldest to newest when you wanted the opposite. We've all been there. The truth is, the little sort icon on the Home tab is the smallest tool in Excel's sort kit, and most workbooks need much more than that.
This guide walks through the heavy-duty sort techniques most users never touch. We'll build a multi-level sort that orders rows by department first, then by hire date. We'll pull a last name out of a "First Last" column and sort by it. We'll teach Excel that January, February, March is the right month order, not April, August, December. We'll cover the difference between sorting a table and sorting a range, and we'll get into SORT() and SORTBY() — the dynamic array functions in Microsoft 365 and Excel 2021 that return sorted results without touching your original data.
If you only need basic A to Z sorting, our sibling tutorial on how to sort alphabetically in Excel covers the one-column case in depth. This article is for everything that comes after.
Forget the quick A-Z button for a moment. Click anywhere inside your data, head to the Data tab, and press Sort. That opens the Sort dialog box — the one window that handles every advanced sort scenario Excel can throw at you. Three things matter here: the column you're sorting by, what you're sorting on (values, cell color, font color, or icon), and the order (A to Z, smallest to largest, custom list).
Before clicking anything, check the box in the top-right corner that says My data has headers. If you skip this and your header row ends up sorted into row 47, that's why. Excel can usually detect headers automatically, but it gets confused when your first row contains numbers or short text that looks like data. Tick the box manually and move on.
One more thing to set early: click Options inside the Sort dialog. By default Excel sorts top to bottom, which is what 99% of spreadsheets need. But if your data is laid out across columns — say, monthly figures spread horizontally — flip it to left to right. People miss this option for years and never realize it exists.
Press Ctrl + T on any data range and you get a real Excel Table. Why does that matter for sorting? Tables keep related columns locked together automatically. They show filter dropdowns in every header, they grow when you add new rows, and any formula written against the table updates itself when rows move. If you sort a plain range and one column has a formula that points to a fixed cell, you're going to get garbage. Tables eliminate that entire category of bug.
This is the question that brings most people to this article. You want rows ordered by Department first, and within each department, ordered by Salary highest to lowest. That's a two-level sort, and it's where Excel actually shines.
Open the Sort dialog (Data → Sort). The first row tells Excel the primary sort: pick Department in the Column field, leave Cell Values under Sort On, and choose A to Z. Now click Add Level. A second row appears. Set Column to Salary, Sort On stays at Cell Values, and Order becomes Largest to Smallest. Click OK.
Excel walks down your list, groups every row with the same department together, and inside each group it ranks people from highest salary to lowest. You can stack up to 64 levels this way — though if you need more than four or five, your data probably wants a PivotTable instead of a sort.
The order of levels matters. Level 1 is the outermost grouping; Level 2 is the tiebreaker inside Level 1. Mix them up and you'll get the opposite of what you wanted. Use the up and down arrows next to Add Level to rearrange them without retyping.
The column you want as the primary grouping (Department, Region, Category). Every row with the same value here stays together as a block.
How you want rows ordered inside each Level 1 block (Salary, Date, Score). Different orders allowed per level — alpha for one, numeric for the next.
Rarely needed beyond three levels. If you stack five or more, consider a PivotTable with collapsible groupings instead — it's easier to audit and faster to refresh.
Excel's sort is stable: ties keep their original order. So if Level 2 produces a tie, the tied rows stay in whatever order they were in before you sorted. Handy when you want to layer sorts.
Here's a common headache. Your roster has names like Maria Gonzalez, James O'Brien, Linh Tran — all in one column. You sort A to Z, and you've actually sorted by first name. Excel doesn't know where the first name ends and the last name begins. You have to tell it.
The clean approach is a helper column. In an empty column next to your names, drop this formula:
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))It looks intimidating, but it's doing one thing: replacing every space with 100 spaces, then grabbing the rightmost 100 characters, then trimming the leading whitespace. The result is whatever word came last in the cell — your last name, even if the person has a middle name in there. Copy the formula down, then sort by your helper column. After sorting, you can hide or delete the helper if you want a clean output.
For Microsoft 365 users, there's a sleeker version using TEXTAFTER:
=TEXTAFTER(A2," ",-1)That negative -1 tells Excel to find the last space, not the first. Much shorter, but it only works in Microsoft 365 and Excel 2024+. If you're sharing the workbook with someone on Excel 2019, stick with the SUBSTITUTE approach for compatibility.
A range is just selected cells with no table formatting. Click any cell inside the data, press Data → Sort, configure your levels, click OK. Excel sorts the contiguous block it detects. Risk: if there's a blank column or row inside your data, Excel may stop at the gap and only sort half your records. Always check the selection range Excel proposes before hitting OK.
A real Excel Table (Ctrl + T) gives every header a dropdown arrow. Click the arrow on any column header, choose Sort A to Z, Sort Z to A, or Sort by Color. Multi-level sorts still go through Data → Sort, but the table keeps formulas, totals row, and slicer connections intact. Tables are almost always the better choice.
The SORT function in Microsoft 365 returns a sorted copy without changing the original. Syntax: =SORT(A2:C100, 2, -1) sorts the range by column 2, descending. Because it spills, you point one formula at the top-left of an empty area and Excel fills the rest. Edit the source data and the sorted view updates instantly.
SORTBY is the more flexible cousin. =SORTBY(A2:C100, C2:C100, -1, B2:B100, 1) sorts the range by column C descending, then by column B ascending. The sort keys don't have to be inside the returned range, so you can sort visible columns by hidden calculation columns. Available in Microsoft 365 and Excel 2021+.
By default Excel sorts text alphabetically. So if you have a column with month names — January, February, March — sorting A to Z gives you April, August, December, February, January… which is nonsense for a report. The fix is a custom list.
Excel ships with four built-in custom lists: short months (Jan, Feb, Mar), long months (January, February), short weekdays (Sun, Mon, Tue), and long weekdays (Sunday, Monday). To use one, open the Sort dialog, click the Order dropdown, and choose Custom List. Pick months from the list on the right, click OK, and Excel sorts chronologically: January through December instead of alphabetically.
You can also build your own. Go to File → Options → Advanced, scroll to General, and click Edit Custom Lists. Type your sequence — Small, Medium, Large, XL — one item per line, press Add, and OK. Now that sequence shows up in the Sort dialog's Order dropdown forever, in every workbook on this PC. Perfect for product sizes, priority levels (Low, Medium, High, Critical), regions in your company's preferred order, or anything else where alphabetical is just wrong.
Two cases here, and they're different. If your month column stores real dates (1/15/2026, 2/14/2026), just sort by that column oldest to newest — Excel handles the chronology automatically. But if your column stores month names as text (January, February), you need the custom list approach above. Open Sort, set the column, change Order to Custom List, pick Long Months or Short Months, OK. Done.
Static sorting — clicking Data → Sort — is destructive. Excel rearranges the rows in place, and if your source data is going to change tomorrow, you'll have to sort again. The dynamic array functions SORT and SORTBY, introduced in Microsoft 365 and backported to Excel 2021, fix that completely.
Drop =SORT(B2:D200, 3, -1) into an empty cell. Excel spills a sorted copy of B2:D200 into the cells below your formula, ordered by the third column descending. The original data doesn't move. Edit any cell in B2:D200, and the sorted spill recalculates instantly. Add a row to the source, the sorted range grows. This is how spreadsheets should have worked from the beginning.
SORTBY is even more powerful because the sort keys can sit outside the returned range. Want to display only the Name and Score columns but sort by a hidden Date column? =SORTBY(A2:B200, D2:D200, -1) does exactly that. You can chain multiple keys: =SORTBY(A2:C100, C2:C100, -1, A2:A100, 1) sorts by column C descending, then by column A ascending — same as a two-level Sort dialog, but live and reactive.
One catch: if any cell where the spill wants to land has existing content, you get a #SPILL! error. Clear the target area first. And don't combine dynamic SORT with a real Excel Table on the same data — pick one approach per range or you'll fight yourself.
If you've used conditional formatting to color cells — red for overdue, green for done — you can sort by that color. Open the Sort dialog, change Sort On from Cell Values to Cell Color (or Font Color, or Conditional Formatting Icon). Then in the Order column, pick the specific color you want at the top. Add levels for additional colors. This is how you get all the red rows clustered at the top without writing a single formula.
If your table has a Total Row (the dark band at the bottom that shows SUM, AVERAGE, etc.), Excel automatically excludes that row from any sort. Same for grouped Subtotals created via Data → Subtotal — they stay anchored. You can sort confidently without worrying about a SUM jumping to the middle of your data. This is one of the under-advertised reasons to use real Tables instead of plain ranges.
Need to flip from Z-A back to A-Z? Don't open the Sort dialog again — just press Ctrl + Z. Excel undoes the sort and restores the original row order. This only works if you haven't done anything else after the sort, so undo immediately when you realize the sort was wrong.
If sorting puts 100 before 11 before 2, your "numbers" are actually text. Select the column, click the warning icon that appears, and choose Convert to Number. Or use Data → Text to Columns → Finish to force a conversion in one click. After that, sorts behave numerically.
The right sort strategy depends on what your data is doing. For a one-off cleanup of a static export, the Sort dialog with one or two levels is fastest. For an ongoing report where the source data updates daily, build it with SORT or SORTBY so the view stays current automatically. For anything that involves grouping — department, region, category — convert to a Table first and then sort. And when alphabetical isn't the right order (months, sizes, priority levels), reach for Custom Lists every single time.
One last tip that saves more spreadsheets than any other: before you sort a workbook you didn't create, save a copy. Sorting is reversible with Ctrl + Z only while the file is open. Once you close and reopen, that undo history is gone. Five seconds of preparation prevents a panicked recovery call later.
Worth mentioning too: keyboard shortcuts. Alt + A + S + S opens the Sort dialog from anywhere in Excel without clicking through ribbons. Alt + A + T toggles AutoFilter, which gives you the per-column sort dropdowns. And if your hands live on the keyboard, Alt + Down on any filtered header opens the same sort and filter menu mouse users get from the dropdown arrow. Power users rarely touch the ribbon for sort tasks — it's all Alt-key sequences and Ctrl + Z.
Use the basic A-Z button when you have a clean one-column sort and the data has no formulas. Use Data → Sort with multiple levels when you need grouping plus tiebreakers. Reach for a helper column whenever the value you want to sort by isn't in its own cell — last names hidden inside full names, file extensions inside filenames, area codes inside phone numbers.
Use Custom Lists for any sort where alphabetical produces nonsense. And switch to SORT or SORTBY the moment your data starts updating regularly, because a one-time manual sort that has to be redone weekly is a tiny disaster waiting to happen.
If you're getting into the broader skill of working with Excel data — filtering, deduplicating, pivoting — sorting is the foundation everything else sits on. Master the multi-level sort and the custom list, and you'll handle 95% of the real-world cases a corporate spreadsheet throws at you. The remaining 5% is what the dynamic functions and the helper-column tricks above are for.
You can apply a sort while a filter is active, and it only sorts the visible rows in the order you pick. Hidden rows stay put. That sounds useful, but it's actually one of the easiest ways to create a confusing spreadsheet — somebody clears the filter later and now the data is half-sorted, half-original, with no obvious pattern. Best practice: clear filters before sorting, sort the whole dataset cleanly, then reapply the filters. Future you will thank present you.
And if you're sharing a workbook where colleagues might re-sort it, leave a note in cell A1 or build a small instruction tab. There's no way to lock the sort order of an Excel range short of converting it to a static PDF, so social conventions and clear documentation do the work that the software won't. A two-line comment saying "Sort by Department then Salary descending — see Sort dialog" prevents the inevitable email that starts with "why does this look different now?"