Sort Data
Click a single cell inside the data, then Data > Sort — never select just the column you want sorted. With one cell selected Excel detects the whole block and keeps every row intact; with one column selected it offers to "Continue with the current selection", and accepting that moves that column's values while the rest of each row stays put, scrambling the data with no warning and no error. Inside the dialog, add one level per column, most significant first, and switch Sort On to Cell Color or Font Color when colour order is what you want. Sorting rewrites the sheet in place and there is no unsort, so add a plain 1, 2, 3 index column beforehand if the original order might ever be needed.
The formula
=SORT(A2:C500, 2, 1) a sorted COPY that spills elsewhere; 2 = by the second column, 1 = ascending
=SORT(A2:C500, 2, -1) the same, descending
=SORTBY(A2:C500, C2:C500, -1, A2:A500, 1) sort by columns that need not appear in the output, in priority orderA worked example
A2:D200 lists staff — Department in A, Surname in B, Hire date in C, Salary in D. Column C was pasted from a web report, so part of it is text that merely looks like dates.
Click A2 > Data > Sort > Sort by Department A to Z > Add Level > Then by Hire date Oldest to Newest
Rows group by department and, inside each department, the genuine dates order correctly — but every text date collects in a separate block, ordered alphabetically, above or below the real ones. Excel sometimes offers a Sort Warning about values stored as text; taking the "sort separately" option only formalises the split. Converting column C to real dates first is the fix that lasts.
Which one do I need?
| If you want to… | Use |
|---|---|
| One column A to Z with the rest of each row following it | Click one cell in that column, then the A→Z button on the Data tab |
| Department first, then hire date within each department | Data > Sort > Add Level, most significant level at the top |
| Dates come out in the wrong order | They are text, not dates — convert the column, then sort |
| Sorting people by surname when the cell holds "Ada Lovelace" | Split the name into its own column first with Text to Columns or Flash Fill, then sort on that column |
| Sort by fill colour, font colour or a conditional-formatting icon | Data > Sort > Sort On: Cell Color, choose the colour and whether it goes On Top — one level per colour |
| The data runs across rather than down | Data > Sort > Options > Sort left to right, which reorders columns by the values in one chosen row |
| You need the original order back | Only possible if you planned for it: an index column of 1, 2, 3 lets you sort back to it. Ctrl+Z works only before the file is closed |
| The source must stay exactly as it is | =SORT(range, n, 1) spills a sorted copy that updates itself, on Excel 365 and 2021 |
| Only one column moved and every row is now wrong | Ctrl+Z immediately — that is the "Continue with the current selection" trap; redo it with a single cell selected |
Frequently asked questions
Why does Excel sort my dates in the wrong order?
Those cells hold text, not dates, so they sort alphabetically — 01/12 lands next to 01/02 regardless of year. Left-aligned dates in a column you never formatted are the giveaway. This happens most often when a file written on a m/d/y machine is opened on a d/m/y one: the unambiguous dates convert and the rest stay as text.
How do I undo a sort after saving the file?
You cannot. Sorting rewrites the rows in place, Ctrl+Z is gone once the workbook has been closed, and nothing in the file records the previous order. The only protection is preventive: a column numbered 1, 2, 3 down the side before any sorting means you can always sort back to it.
Why did sorting scramble my rows?
A single column was selected rather than a single cell, and Excel's prompt to expand the selection was answered with "Continue with the current selection". That sorts the highlighted column alone and leaves every other column where it was, so names now sit against the wrong numbers. Undo at once and repeat with just one cell selected.
Can Excel sort by colour?
Yes — Data > Sort, then set Sort On to Cell Color, Font Color or Conditional Formatting Icon. Colours have no inherent order, so you add one level per colour and choose whether each goes On Top or On Bottom, which effectively builds the ranking by hand.
New guides and tools, once a month
DE + EN · double opt-in · no spam