How to Sort Columns in Excel: Quick Sort, Multi-Level and Formulas 2026
How to sort columns in Excel — quick sort buttons, multi-level Custom Sort, SORT and SORTBY formulas, custom lists and common pitfalls explained.

How to Sort Columns in Excel: The Big Picture
Sorting is one of the most-used Excel operations and one of the most likely to produce subtle errors when done carelessly. The single biggest sorting mistake is sorting one column independently of the rows it belongs to, which scrambles the relationships between values and corrupts the data.
The second is sorting through blank rows that Excel treats as the boundary of your data range, which leaves part of the table unsorted while the rest moves. Knowing the right method for the situation, and understanding how Excel decides what range to sort, makes the difference between a clean reorder and silent data damage.
This guide walks through the major sorting methods in Excel — the quick A-Z and Z-A buttons on the Data tab, the more powerful Custom Sort dialog for multi-level sorts, the modern SORT and SORTBY dynamic array functions in Microsoft 365, and the sorting behaviour built into Excel Tables and filter dropdowns. The aim is to give you confidence about which method fits which situation rather than reaching for the same button regardless of the data shape.
Sorting in Excel has a long history of subtle bugs and edge cases that have shaped current best practice. Early versions had less strong range detection, so users developed habits around manual range selection that persist even though modern Excel is smarter about it. The current behaviour balances the convenience of auto-detection with the safety of warnings when partial-range sorts could damage data, but the underlying lesson — verify the range before committing — remains the single most important habit for safe sorting work.
Microsoft Excel: upcoming dates and official changes
- Jun 30, 2026: Certiport retired the Microsoft Office Specialist 2016 certifications, including MOS Excel 2016, on June 30, 2026. The Microsoft 365 Apps and Office 2019 Excel exams are still available. (certiport.pearsonvue.com)
- Updated Oct 16, 2026: Delivery is now Proctored live-in-the-application exam via Certiport Authorized Testing Centers (in-center or remote-proctored) (previously Proctored live-in-the-application exam via Certiport Authorized Testing Centers).
- Updated Oct 16, 2026: Eligibility is now No formal prerequisites; about 150 hours of instruction and hands-on experience recommended (previously No formal prerequisites; ~150 hours of instruction and hands-on experience recommended (Associate)).
- Updated Oct 16, 2026: Time limit is now 50 minutes (60 minutes seat time) (previously 50 minutes (60 minutes seat time)).
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
- The MOS Excel (Microsoft 365 Apps) exam has 33 to 37 questions. (certiport.pearsonvue.com)
- The MOS Excel Expert (Microsoft 365 Apps) exam has 23 to 27 questions. (certiport.pearsonvue.com)
- Microsoft Office Specialist Excel exams last 50 minutes, with 60 minutes of total seat time. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- The passing score for Microsoft Office Specialist Excel exams is always 700 on Certiport's 1 to 1000 scaled score. (certiport.pearsonvue.com)
- After failing a Microsoft Office Specialist Excel exam, candidates must wait 24 hours before the first retake and 48 hours before each later retake, and there is no annual limit on attempts. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- Microsoft Office Specialist Excel certifications earned on or after September 23, 2025 are valid for five years; those earned earlier do not expire. (certiport.pearsonvue.com)
- The Microsoft Office Specialist Excel exam is proctored and delivered live in the Excel application through Certiport Authorized Testing Centers, either in person or remote-proctored. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- Microsoft recommends that candidates for the MOS Excel Associate (Microsoft 365 Apps) certification have about 150 hours of instruction and hands-on experience with Excel. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- Candidates get their MOS Excel score report as soon as they finish the exam, and it can also be viewed in their Certiport profile. (certiport.pearsonvue.com)
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
Microsoft Excel Exam at a Glance
| Item | Detail | Official source |
|---|---|---|
| Number of questions (Excel Associate (MO-210)) | 33-37 questions | certiport.pearsonvue.com |
| Number of questions (Excel Expert (MO-211)) | 23-27 questions | certiport.pearsonvue.com |
| Time limit | 50 minutes (60 minutes seat time) | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Passing score | 700 on a 1–1000 scale | certiport.pearsonvue.com |
| Retake policy | Wait 24h after 1st fail, 48h before each later retake; no annual limit | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Validity | Valid 5 years (earned on/after Sept 23, 2025) | certiport.pearsonvue.com |
| Delivery | Proctored live-in-the-application exam via Certiport Authorized Testing Centers (in-center or remote-proctored) | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Eligibility | No formal prerequisites; about 150 hours of instruction and hands-on experience recommended | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Score release | Score report at end of exam; also in Certiport profile | certiport.pearsonvue.com |
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
Excel sorting at a glance
Quick sort buttons: Data tab → A-Z (ascending) or Z-A (descending). Custom Sort dialog: Data → Sort for multi-level and custom criteria. Dynamic array formulas: SORT() and SORTBY() in Microsoft 365 produce sorted output without changing source data. Excel Tables (Ctrl+T) handle header detection automatically and preserve row relationships during sort. Common pitfall: sorting one column without expanding the range corrupts associated rows.
Method 1: Quick Sort Buttons
The fastest way to sort a single column is the A-Z and Z-A buttons on the Data tab. Click any cell inside the column you want to sort, then click Data → A-Z to sort ascending or Z-A to sort descending. Excel automatically detects the contiguous range of data around the active cell and sorts the entire range using the selected column as the sort key. This is the right method when you want to sort a clean tabular dataset by one column with the row relationships preserved.
The detection of the data range matters. If your data has blank rows separating sections, Excel treats each section as a separate range. Sorting a cell in the top section sorts only the top section. Sorting a cell in the bottom section sorts only the bottom.
The fix is either to remove the blank rows before sorting or to manually select the full range you want to sort before clicking the sort button. The same caution applies to entirely blank columns inside your data — Excel may treat the data on either side as separate ranges, with unpredictable results when you sort across the gap.
The auto-detect behaviour also handles header rows intelligently in most cases. Excel infers that the top row is a header when its formatting differs from the data rows, when its values are text and the column below is numeric, or when the row contains the word in the active column header position. The detection is correct most of the time but occasionally treats a real data row as a header or vice versa. The Custom Sort dialog has an explicit My Data Has Headers checkbox to override the detection when needed.

Methods to Sort in Excel
Data tab → A-Z (ascending) or Z-A (descending). Sorts the contiguous range around the active cell using the active column as sort key. Fast and simple for single-column sorts but offers no multi-level or custom options.
Data → Sort. Opens a dialog with full sort options including multi-level (sort by Region then by Date), case-sensitive sorting, sort by color or icon, sort left to right, and use of custom lists. The right tool for any sort beyond a single column.
Microsoft 365 dynamic array function. =SORT(A2:C100) returns a sorted copy of the source range without altering the original. Add arguments for sort column index and direction. Updates automatically when source data changes.
Sort one range by values from another range. =SORTBY(A2:A100, B2:B100, -1) sorts column A by descending values in column B. Useful when sorted output should be ordered by a column that does not appear in the result.
Click the dropdown arrow on a filtered column header. Sort options appear at the top of the menu. Combines sort with the existing filter, particularly useful when you have already narrowed the data and want to see the survivors in a specific order.
Tables (Ctrl+T) include built-in sort dropdown arrows on every column. Sort behaviour respects table boundaries and headers automatically. Strongest fit for ongoing datasets where structure is more important than speed of one-off sort actions.
Method 2: Custom Sort for Multi-Level and Special Cases
The Custom Sort dialog handles every sort beyond the simplest single-column case. Click Data → Sort to open the dialog. Excel pre-populates the first sort level with the column nearest your active cell. Click Add Level to add additional sort criteria — sort by Region first, then by Sales Rep within Region, then by Sale Date within Sales Rep. Each level can use a different sort direction independently. The dialog also allows sorting by font colour, fill colour or conditional formatting icon, which is useful when colour-coded data needs to group by colour rather than by value.
The Options button inside the Custom Sort dialog reveals further controls. Case-sensitive sorting puts capital letters before lowercase letters of the same character. Orientation can be switched from top-to-bottom (the default) to left-to-right, which sorts data laid out in rows rather than columns. The latter is rare but useful when a header row needs to be reordered without affecting the data below. Most practical Excel sorting uses the standard top-to-bottom orientation, but knowing the option exists prevents stuck moments when the data does not match the assumed shape.
Multi-level sorting matches the structure of how readers naturally think about ordered data. A sales report often needs to show data grouped by region first, with each region's rows sorted by sales rep within the region, and each rep's rows sorted by sale date within the rep. The Custom Sort dialog supports this hierarchy directly through Add Level — three sort criteria captured in a single sort operation. Trying to achieve the same result with three separate single-column sorts produces incorrect output because each sort disrupts the order of the previous sort.
Step-by-Step Sorting Procedures
1. Click any cell in the column you want to sort.
2. Click Data on the ribbon.
3. Click A-Z to sort ascending or Z-A to sort descending.
4. Excel sorts the contiguous range using your active column. Verify all rows moved together — if not, blank rows or columns broke the range.
SORT and SORTBY: The Modern Alternative
The SORT and SORTBY dynamic array functions arrived with Microsoft 365 and changed how many users approach sorted output. Instead of physically rearranging the source data, these functions return a sorted copy of the data into a destination range. The source remains untouched. The destination updates automatically whenever the source changes. The combination is useful for dashboards that need a sorted view of an underlying data table without disrupting the table itself, particularly when the dashboard pulls data via Power Query or external connections that should not be edited directly.
SORT takes the source range, an optional sort column index, and an optional sort direction. =SORT(A2:C100) sorts by the first column ascending. =SORT(A2:C100, 2, -1) sorts by the second column descending. SORTBY is more flexible because it can sort one range by values from another range. =SORTBY(A2:A100, B2:B100, -1, C2:C100, 1) sorts column A using B descending as the primary key and C ascending as the secondary key. The two functions compose well with FILTER, UNIQUE and other dynamic array functions to produce powerful sorted, filtered, deduplicated output in a single formula.
One useful pattern is the SORT-FILTER-UNIQUE composition for building dynamic top-N or filtered-and-sorted views. =SORT(FILTER(A2:C100, B2:B100>1000), 2, -1) returns rows where column B exceeds 1000, sorted by column 2 descending. Adding UNIQUE to the chain produces deduplicated views: =SORT(UNIQUE(FILTER(A2:A100, B2:B100>1000))). Each function takes the output of the previous one, building a powerful dynamic view in a single composite formula. Modern Excel users routinely use these compositions instead of pivot tables for many ad-hoc analytics tasks.

The classic data-corruption mistake is selecting one column, clicking sort, and reordering only that column while leaving the rest of the rows in place. The result is a workbook where row associations are scrambled — customer names no longer match their addresses, dates no longer match the events they describe. Excel usually warns when it detects you have selected one column inside a wider data range, but the warning is easy to dismiss accidentally. The safer pattern is to click any cell inside the data and let Excel auto-detect the full range, or to manually select the full range before sorting.
Custom Lists for Non-Alphabetic Sorts
Many real-world sorts need an order other than alphabetical or numeric. Days of the week sort logically as Mon, Tue, Wed, Thu, Fri, Sat, Sun rather than alphabetically as Fri, Mon, Sat, Sun, Thu, Tue, Wed. Months sort as Jan through Dec, not alphabetically. Priority levels sort as High, Medium, Low. Rank levels sort as Senior, Mid, Junior. Excel handles these through Custom Lists, which are user-defined or built-in sequences that the Custom Sort dialog can use as sort orders.
Excel ships with built-in custom lists for days of the week (full and abbreviated) and months. To add your own, click File → Options → Advanced → Edit Custom Lists. Type the list in the order you want or import it from a worksheet range. Save the list, and it appears in the Custom Sort dialog Order dropdown the next time you sort.
The same custom lists also drive autofill — typing High in one cell and dragging the fill handle produces High, Medium, Low if the list is configured. Investing a few minutes in custom lists for the categorical orders you use frequently saves the manual sort setup every time afterwards.
Sorting Best Practices
- ✓Save the workbook before any major sort so you can roll back if needed
- ✓Click any cell inside the data and let Excel auto-detect the range
- ✓Confirm Excel selected the full range before clicking sort
- ✓Remove blank rows or columns inside your data before sorting
- ✓Convert your data range to an Excel Table (Ctrl+T) for ongoing sorting workflows
- ✓Add a row-number helper column before sorting if you want to restore original order
- ✓Use Custom Sort for multi-level criteria — single-column sorts cannot express priority order
- ✓Define custom lists for categorical orders you sort frequently
- ✓Use SORT() formulas for sorted views that should update with source data
- ✓Verify the sort produced the expected result before saving over the source data
Common Sorting Pitfalls
The most common pitfall is sorting numbers stored as text. Excel sorts text alphabetically — 1, 10, 11, 100, 2, 20, 200 — rather than numerically. The cause is usually data imported from external systems where Excel stored the values as text rather than numbers. The fix is to convert the column to numbers via the Data tab → Text to Columns wizard, or by multiplying the entire column by 1, or by paste-special with multiplication on a 1 cell. Once the values are real numbers, sorting works correctly.
The second common pitfall is merged cells. Sorting fails immediately when the active range contains merged cells with a generic error message that does not point clearly at the merge as the cause. Unmerge the cells (Home → Merge & Center → Unmerge Cells) before sorting. The third pitfall is invisible whitespace in text data.
Cells that look identical sometimes differ by a trailing space or a non-breaking space character, producing sorts that put apparently identical values in unexpected positions. The TRIM function and CLEAN function both remove unwanted whitespace, and applying TRIM to the source column before sorting prevents the issue.
The fourth pitfall is sorting tables that have formulas referring to specific row positions. Formulas like =A2+B2 sort cleanly because they reference cells in the row being moved. Formulas like =SUMIF(A:A, criteria) sort cleanly because they reference whole columns. Formulas that cross-reference specific rows in other parts of the workbook can break when sorting moves rows around because the references become misaligned. Reviewing dependent formulas before sorting large tables prevents silent damage to other parts of the workbook.
One subtle case worth knowing is sorting cells with leading apostrophes. An apostrophe at the start of a cell value forces Excel to treat the value as text even when it looks numeric. The apostrophe is invisible in the cell but appears in the formula bar. Sorts treat the apostrophe-prefixed values alphabetically rather than numerically, sometimes producing unexpected order. The fix is removing the apostrophes via Find and Replace or by re-entering the values without the prefix.
Many readers pair this guide with the excel download free to check their readiness before exam day.
Many readers pair this guide with the microsoft excel exam practice test to check their readiness before exam day.
Many readers pair this guide with the excel new to check their readiness before exam day.
Restoring Original Order After Sorting
Sometimes you need to sort data temporarily, do some analysis, and then restore the original row order. Excel does not automatically remember the pre-sort order, so the standard technique is to add a helper column with row numbers before sorting. The helper column captures the original sequence, and re-sorting by the helper column ascending restores the original order. The pattern is simple but routinely overlooked by users who try to undo a sort hours or days later and discover that Excel's undo history does not extend that far back.
If you forgot to add the helper column and the original order matters, the only options are to recover the file from version history, undo the sort if it was the most recent action, or rebuild the original order through other means (a primary key column, a date column, an order-of-entry indicator). Cloud-saved Excel files (OneDrive, SharePoint) usually have version history that allows recovery of the pre-sort version, although the granularity depends on how often the file was saved. Local-only files without version control rely entirely on the user's discipline around backups and helper columns.
One reliable habit is to add an Order column at the very start of any worksheet you plan to sort, populated with row numbers via =ROW() or =ROW()-1 to start from 1. The column lives quietly in column A and is rarely visible in normal use because most workflows hide column A. Whenever a sort needs to be reversed, sort the Order column ascending and the data returns to original sequence. The discipline costs nothing and prevents the loss of original order that otherwise requires version-history rescue.

Excel Sort Quick Reference
When to Use Which Sort Method
Quick A-Z or Z-A button on Data tab. Fastest for ad-hoc sorting of clean tabular data. Excel auto-detects the range so a single click does the work.
Data → Sort to open the Custom Sort dialog. Add levels for hierarchical sorting (Region then Date), or sort by colour, or use custom list orders for categorical data.
SORT() or SORTBY() dynamic array function. Source remains untouched; the formula produces a sorted copy that updates automatically when source data changes.
Convert to Excel Table (Ctrl+T) for built-in column dropdowns with sort options. Tables expand automatically as new rows are added and preserve row relationships during sorts.
Sort options at the top of the filter dropdown for the column. Combines well with active filters when you want to see the surviving rows in a specific order.
When the sort needs to apply repeatedly to imported data on every refresh, use Power Query's sort step rather than worksheet-level sorting. The sort becomes part of the data preparation pipeline.
Sorting in Excel for the Web and Mobile
Excel for the Web supports the basic A-Z and Z-A buttons on the Data tab and the Custom Sort dialog. Multi-level sorting works the same way as in desktop. Custom lists are not directly editable in the web client but custom lists defined in desktop Excel are honoured when the file is opened in the web client. Dynamic array SORT and SORTBY functions work identically in Excel for the Web, making sorted-view dashboards portable between desktop and web access without modification.
Excel mobile apps for iOS and Android offer simplified sort controls. Tap the cell, tap Sort in the toolbar, choose the column and direction. Multi-level sort is available through a similar workflow but the dialog is condensed for the small screen. The mobile experience works well for occasional sort actions on the go but the desktop or web experience is significantly better for any complex multi-level sorting work. Most practical workflow patterns use mobile for data entry and viewing, with structural changes like sorting handled on a larger screen when convenient.
One quirk of mobile sorting is that the column headers can be hard to see on small screens, particularly when columns are narrow or when the table extends beyond the visible width. Tapping the wrong column for a sort and not noticing immediately produces confusing results. The mobile workaround is to scroll horizontally first to verify which column you intend to sort, then commit to the sort once the column is clearly identified. The desktop and web experiences avoid this problem because the full table is usually visible.
Quick Sort vs Custom Sort vs SORT() Formula
- +Quick A-Z and Z-A is fastest for single-column ad-hoc sorting
- +Custom Sort handles multi-level, colour and case-sensitive sorts
- +SORT() formulas produce sorted views without altering source data
- +All methods preserve row relationships when used correctly
- +Excel Tables make sorting safer by detecting headers automatically
- −Quick sort can scramble data if range detection misses parts of the table
- −Custom Sort dialog has no undo within the dialog — wrong levels need restart
- −SORT() formulas require Microsoft 365 — not available in older versions
- −Sorting destroys original order unless a helper column was added beforehand
- −Sort errors are sometimes silent — verify results before saving over source data
Excel Questions and Answers
Written and edited by Nathan Beja, founder of Practice Test Geeks, with AI assistance. Exam facts are checked against the official sources linked below. Editorial policy
Sources & references
Exam facts on this page (fees, format, dates, passing scores) are checked against these official sources; the date is our last check. Editorial policy
- store.certiport.com — Fees, retake fee · verified Nov 2026
- certiport.pearsonvue.com — Renewal, score validity, score release, exam format · verified Nov 2026
- learn.microsoft.com — Test delivery, time limit, eligibility · verified Nov 2026