Excel Tutorials
Sorting is two clicks when it goes right and a scrambled spreadsheet when it goes wrong, and the difference comes down to what you select before you start. This lesson covers alphabetical sorting, multi-level custom sorts, custom lists, sorting by date, sorting by colour, sorting left to right by column, and exactly what can and cannot be locked in place while you do it. It uses the Event Planner workbook from Excel Foundations, free to download below.
Follow along in the same file used in the video. No email required.
Sorting reorders the rows of your data based on the values in one or more columns. Alphabetically by name, chronologically by date, largest to smallest by amount. The rows themselves move, and every column moves with them, so each record stays intact.
That last part is the whole game. A row in a spreadsheet is one record: this attendee, this order, this invoice. When sorting works correctly, the name, the date, the amount and the status all travel together. When it goes wrong, one column gets reordered while the rest stay put, and every row now holds a mixture of different records. There is no error message, nothing looks broken, and the damage is usually only discovered much later.
Almost every case of this comes from selecting a column before sorting instead of clicking a single cell. Click one cell and Excel works out the boundaries of your data and moves whole rows. Select a column and you have told Excel that column is all you care about. The rest of this page covers that, plus multi-level sorts, custom orders, dates, colours, sorting left to right, and how to get your original order back.
Step-by-step, matching the video above.
This is the most important step on the page. Click one cell in the column you want to sort by, and nothing else. Do not select the column. Excel automatically detects the block of data around that cell and moves complete rows together, which keeps every record intact.
There are two routes to the same thing. On the Data tab, the Sort & Filter group has A→Z, Z→A and the full Sort dialog. On the Home tab, the Sort & Filter dropdown at the far right holds the same options in a shorter menu.
With a cell in a text column selected, A→Z sorts ascending and Z→A descending. Excel places numbers before letters, and blank cells always sort to the bottom regardless of direction.
If Excel thinks you have selected too narrow a range it shows a dialog offering Expand the selection or Continue with the current selection. Expand the selection is nearly always right, because it sorts whole rows. Continue with the current selection sorts only the highlighted cells and detaches them from their rows, which is exactly how data gets scrambled.
In the Sort dialog, My data has headers should be ticked when row 1 holds column names. If it is unticked, your header row gets sorted into the data as though it were a record.
Data > Sort opens a dialog with four dropdowns: Column (what to sort by), Sort On (values, cell colour, font colour or icon), and Order (A to Z, Z to A, or a custom list).
Click Add Level for each additional column. The top level sorts first and each level below only breaks ties in the one above. Sorting by Region then Date groups every region together and orders each group by date. Use the arrows to reorder levels, since the sequence changes the result completely.
With a cell in a real date column selected, the A→Z button relabels itself Sort Oldest to Newest. If your dates refuse to sort correctly, they are text rather than dates, covered in its own section below.
Ctrl+T turns the range into an Excel Table. Every column gets a sort dropdown in its header, rows are locked together permanently, and the Sort Warning never appears again because Excel always knows where the data starts and ends.
The default sort, and the rules that govern how Excel orders text.
A→Z is ascending, Z→A is descending. On the Data tab they are the two buttons beside Sort; on the Home tab they are the first two items in the Sort & Filter dropdown.
Numbers come before text, then text A to Z, then logical values, then error values, and blanks always land at the very bottom whichever direction you sort. Blanks going last in both directions surprises people expecting them to flip.
A space is a character that ranks before any letter, so an entry typed as Smith sorts above Adams. Run the column through TRIM, or use Find and Replace, before sorting anything imported.
apple and Apple are treated identically. To change that, open the Sort dialog, click Options and tick Case sensitive, which then places lowercase before uppercase.
You get 1, 10, 11, 2, 20, 3 instead of numerical order, because each value is compared character by character. The green triangle in the corner of the cell is the giveaway. Convert them with Text to Columns, clicking Finish without changing anything.
For the many cases where alphabetical order is the wrong order.
The Custom Sort dialog is the same dialog as Sort. It is called custom sorting because you define the levels and criteria rather than accepting a single-column A to Z.
Change Sort On from Cell Values to Cell Color, Font Color or Conditional Formatting Icon. You then choose a colour and whether it goes On Top or On Bottom. Add one level per colour to stack them in a specific order, since Excel sorts one colour per level.
Priority levels are the classic case: alphabetically you get High, Low, Medium, which is meaningless. Set Order to Custom List, choose NEW LIST, and type High, Medium, Low with each on its own line or separated by commas. Excel then sorts in exactly that sequence.
Excel ships with day and month lists, so Mon, Tue, Wed and Jan, Feb, Mar sort chronologically rather than alphabetically. They appear automatically in the Custom List dialog.
File > Options > Advanced, scroll to General, and click Edit Custom Lists. A list saved here is available in every workbook on that machine, and doubles as a Fill Handle series.
With Region as level one and Date as level two, Excel groups by region first and orders dates only within each region. Reversing the levels produces a completely different sheet, so check the order before clicking OK.
Dates sort perfectly when they are dates, and nonsensically when they are text.
Excel stores a date as a number counting from 1900, so sorting by date is really sorting by that number. Oldest to Newest and Newest to Oldest replace the A to Z labels automatically when the column contains dates.
A text date sorts as a string, so everything beginning 01 groups together and the years scatter. You get 01/03/2024 next to 01/03/2026 and February landing before the previous December.
Numbers and dates right-align by default; text left-aligns. A column of left-aligned dates is text. Selecting the column and checking whether the status bar shows a Sum or Count is the other quick test.
Data > Text to Columns, click Next twice, then in step 3 choose Date and pick the order that matches your data, such as DMY for European formatting. Click Finish and the whole column becomes real dates.
If your column holds month names rather than dates, alphabetical order gives April, August, December. Use Order > Custom List and pick the built-in month list to get chronological order.
To sort by year, or by month across several years, add a helper column with =YEAR(C2) or =MONTH(C2) and add that as a sort level above the date itself.
The option almost nobody knows exists, hidden behind a button in the Sort dialog.
Open Data > Sort, click Options in the top area of the dialog, and choose Sort left to right instead of the default Sort top to bottom.
It reorders columns based on the values in a chosen row, rather than reordering rows based on a column. The Column dropdown changes to Row, and you pick which row drives the ordering.
Data laid out with months or categories running across the top instead of down the side. Sorting a row of monthly totals to find the biggest month, or rearranging columns into a specific sequence, both need this.
Excel cannot auto-detect a header column the way it detects a header row, so exclude column A from your selection manually if it holds row labels you want left in place.
Orientation resets to top to bottom every time you reopen the dialog, so it has to be set each time you want a left-to-right sort.
| What You Want | How to Do It | Result |
|---|---|---|
| Names A to Z | Click a cell in column A, press A→Z | Whole rows reordered |
| Region, then date within it | Sort dialog, Add Level | Grouped, then chronological |
| High, Medium, Low order | Order → Custom List | Not alphabetical |
| Flagged rows at the top | Sort On → Cell Color, On Top | Red rows first |
| Newest bookings first | Sort Newest to Oldest | Latest dates at top |
| Months across the top | Options → Sort left to right | Columns reordered |
| Get the original order back | Sort by the index column | Restored |
The last row is the one to build a habit around. Before any large sort, add a column numbered 1, 2, 3 straight down, because it is the only reliable way back to the original sequence once the file has been saved and closed. Undo covers you inside the current session and nothing covers you after that. Row three is the other one worth internalising: alphabetically, priority sorts as High, Low, Medium, which reads as an error to anyone looking at the report.
What can be held in place, what cannot, and how to protect yourself either way.
Sorting moves entire rows within the range, by design, because a row is one record. There is no setting that holds one column still while the others move. Anything that appears to do this has broken the relationship between your columns.
Ctrl+T is the real answer. A Table has explicit boundaries, so Excel never guesses the range, the Sort Warning never appears, and rows are permanently bound together no matter how someone sorts.
View > Freeze Panes > Freeze Top Row keeps the header on screen while you scroll. It has no effect on sorting at all, and it is not what stops your header being sorted into the data, that is the My data has headers checkbox.
Number a spare column 1 through however many rows before sorting anything. Sorting by that column restores the original sequence at any point in the future, which nothing else will do once the file has been closed.
Review > Protect Sheet disables sorting unless you tick Sort in the permissions list. Note that sorting still requires the cells themselves to be unlocked, so on a protected sheet users can only sort ranges they are allowed to edit.
Excel refuses to sort a range containing merged cells and shows an error saying it needs all merged cells to be the same size. Unmerge them, and use Center Across Selection instead if you only wanted the visual effect.
Relative references travel with their rows and stay correct. A formula referring to a fixed cell elsewhere, or an absolute reference like $D$5, keeps pointing at the same address after the rows beneath it have moved.
Sorting ignores rows hidden by a filter, so only the visible subset gets reordered. Clear all filters before sorting, or you end up with a sheet that is partly sorted in a way that is very hard to spot.
The single most destructive mistake in Excel. Only that column moves, every other column stays where it was, and every row now holds a mixture of different records. Click one cell instead, and choose Expand the selection if prompted.
The Sort Warning offers this as the second option and it is almost never the right one. It sorts the highlighted cells in isolation and breaks them away from their rows.
Excel auto-detects the range by looking for empty rows and columns, so a blank row means only the block above it gets sorted. Delete stray blank rows, or convert to a Table.
Text dates sort alphabetically, grouping everything that starts with the same digits regardless of year. Check the alignment first, then convert with Text to Columns.
Same cause, same fix. If the numbers are left-aligned or show a green triangle, they are text and will not sort numerically.
With it unticked, row one is treated as data and gets sorted into the middle of your list, which is confusing to spot afterwards.
Ctrl+Z works within the session only. Once the file is saved and closed there is no way back unless you added an index column first.
Hidden rows are skipped entirely, leaving the visible rows sorted and the hidden ones untouched, which is genuinely difficult to notice.
Excel refuses to run and shows an error about merged cells needing to be the same size. Unmerge and use Center Across Selection for the same look.
In the Sort dialog the top level dominates. Region above Date groups by region; Date above Region orders by date and scatters the regions.
Sorting a list to find what matters means nothing told you when it started to matter.
Free 14-day trial. No credit card required.
Click any single cell in the column you want to sort, then use Sort A to Z on the Data tab or under Sort and Filter on the Home tab. Excel detects the surrounding data and moves whole rows together.
You almost certainly selected one column before sorting, so only that column moved while every other column stayed put. Click a single cell instead of selecting a column, and choose Expand the selection if Excel asks.
Select just one cell inside the data rather than a column, and if the Sort Warning appears choose Expand the selection. Converting the range to a Table with Ctrl+T makes this automatic and permanent.
Open the Sort dialog from the Data tab and click Add Level. The top level sorts first and each level below breaks ties in the one above it.
Click a cell in the date column and choose Sort Oldest to Newest. If it sorts in the wrong order, your dates are stored as text rather than real dates and need converting first.
They are text, not dates. Text dates sort character by character, so every entry starting with 01 groups together regardless of year. Right-aligned values are dates, left-aligned ones are text.
Open the Sort dialog, click Options, and choose Sort left to right. You can then sort columns by the values in a chosen row instead of sorting rows.
A custom sort lets you set several sort levels at once and choose what to sort on, such as cell colour or font colour, rather than just A to Z on one column.
In the Sort dialog set the Order dropdown to Custom List, then type your sequence separated by commas. Excel then sorts in that order instead of alphabetically.
In the Sort dialog change Sort On from Cell Values to Cell Color, then pick the colour and whether it goes on top or on bottom. Font Color and Conditional Formatting Icon work the same way.
No. Sorting always moves entire rows within the sorted range, and there is no way to hold one column still. If you need the original order back, add a numbered index column before sorting.
Only if you planned ahead. Add a column numbered 1, 2, 3 down the rows before you sort, and you can always sort by it to restore the original sequence. Undo works too, but only before the file is saved and closed.
The sheet is probably protected without sorting permission, or you are in cell edit mode. Merged cells in the range will also block sorting with an error message.
No. Rows hidden by a filter are excluded from the sort, which can leave your data in a state you did not expect. Clear filters before sorting the whole set.
They are stored as text, so Excel sorts them character by character rather than numerically. Convert the column to real numbers with Text to Columns.
Formulas using relative references move with their rows and stay correct. Formulas pointing at fixed cells outside the sorted range can end up referring to the wrong row, so check them after a large sort.
Not by default. Open the Sort dialog, click Options and tick Case sensitive if you need uppercase and lowercase treated separately.
Every business starts with a spreadsheet. Updoot is where you scale past it, records link themselves automatically.
Start Your Free Trial →