Sorting puts your list in order: names A to Z, biggest sales first, oldest dates at the top. It takes two clicks. But there’s one common mistake that can quietly mix up your data, so it’s worth knowing how to do it properly.
1. Quick sort by one column
- Click any single cell in the column you want to sort by. Don’t select the whole column.
- Go to the Data tab.
- Click A→Z (smallest to largest) or Z→A (largest to smallest).
Excel works out where your data starts and ends, spots your header row, and keeps each row together as it sorts.
The same buttons work for numbers (smallest to largest) and dates (oldest to newest).
⚠️ The mistake to avoid
If you select just one column and sort it, Excel shows a Sort Warning asking whether to Expand the selection. Always choose Expand the selection.
If you choose Continue with the current selection, only that column is sorted. Names end up next to the wrong phone numbers and the wrong sales figures. If this happens, press Ctrl + Z straight away to undo it.
2. Sort by more than one column
Want to sort by department, and then by surname within each department?
- Click inside your data.
- Go to Data > Sort.
- Make sure My data has headers is ticked (top right).
- Under Sort by, choose Department and pick A to Z.
- Click Add Level.
- Under Then by, choose Surname.
- Click OK.
You can add as many levels as you need. Use the arrows to change their order.
3. Sort in a custom order
Alphabetical isn’t always right. Months sorted A to Z come out as April, August, December…
- Open Data > Sort.
- Under Order, choose Custom List…
- Pick January, February, March… (or the short version).
- Click OK twice.
To create your own order, such as Low, Medium, High, type it into the List entries box in the Custom Lists window and click Add.
4. Sort by colour
If you’ve highlighted rows or used conditional formatting, you can bring the coloured ones to the top:
- Open Data > Sort.
- Choose your column under Sort by.
- Change Sort On to Cell Colour (or Font Colour).
- Pick the colour and choose On Top.
5. Sort left to right
Occasionally your data runs across rows instead of down columns. In the Sort window, click Options… and choose Sort left to right.
6. Sort with a formula
In Microsoft 365 and Excel 2021, =SORT(A2:D100, 3, -1) creates a sorted copy of your data, sorted by the 3rd column, largest first. It updates automatically when the original changes, and your original stays in its original order.
Troubleshooting
My header row got sorted into the data. Open Data > Sort and tick My data has headers.
Only part of my data sorted. There’s a blank row or column in the middle. Excel treats it as the edge of your data. Remove the gap, or select the whole range before sorting.
Numbers sort in a strange order (1, 10, 2…). They’re stored as text. Convert them to numbers first.
Dates sort wrongly. Some of them are text rather than real dates. Check whether they’re left-aligned. See our guide on changing date formats.
Wrapping up
Click one cell and use Data > A→Z for everyday sorting. For anything more complicated, Data > Sort lets you stack levels, use custom orders and sort by colour. If a sort ever goes wrong, Ctrl + Z is your friend.
