In Excel, when you sort data with the SORT option, there might be some situations when you face some problems. And most of these problems have a solution you can apply and then re-use the SORT option.
- Not Saved as Correct Data Type: The SORT option considers the data type before sorting it. If you have numbers saved as text, or dates saved as text, you need to change their data type first (you can also use the VALUE or DATEVALUE functions).
- Having a Hidden Cell or Range: If you have hidden rows or columns in the data, Excel doesn’t move them when you sort, even if the data is in the correct format. So, unhide them before you sort the data.
- Two Different Types of Data: If you have two different types of data in the same column, Excel sorts them in groups. In ascending order, numbers come first, then text, then TRUE/FALSE values, then errors, and blank cells always go to the end.
- Sorting Data with Volatile Formulas: If you have volatile formulas, like RAND, RANDBETWEEN, TODAY, NOW, etc., and when you sort the data, these formulas recalculate themselves. As the values change, it seems like sorting is not working properly. Convert them to values (Paste Special ➜ Values) before you sort.
- Merged Cell in the Data: If one or more cells in the data are merged, Excel shows an error message saying that all the merged cells need to be the same size. Unmerge the cells (or use Center Across Selection instead) and then sort the data.
- Leading and Trailing Spaces: If you have a leading or a trailing space with a number, Excel won’t consider it a number, and a leading space in text puts that value at the top of an A to Z sort. Use TRIM to remove extra spaces, CLEAN to remove non-printing characters, and SUBSTITUTE for non-breaking spaces, and then sort the data.
Below are some of the tips that can help you clean the data so that you can sort it.
Tips to Clean the Data Before Sorting
- Convert numbers stored as text: select the cells, click the warning icon next to them and choose Convert to Number, or use Data ➜ Text to Columns ➜ Finish on the column.
- Remove extra spaces: use the TRIM formula below in a helper column, then copy it and paste it back as values.
- Unhide everything: select the whole sheet (Ctrl + A), then right-click a row header and click Unhide, and do the same for a column header.
- Unmerge cells: select the data and go to Home ➜ Merge & Center ➜ Unmerge Cells.
Here’s the formula that removes extra spaces and non-breaking spaces from cell A2 in one go:
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
And to convert a number stored as text with spaces into a real number, wrap it with VALUE:
=VALUE(TRIM(SUBSTITUTE(A2,CHAR(160)," ")))
Other Common Sorting Mistakes
- Sorting only one column: if you select one column and sort it, the other columns stay where they are and your rows get mixed up. When Excel shows the Sort Warning, choose Expand the selection.
- Header row gets sorted: in the Sort dialog box, tick My data has headers so the first row stays on top.
- Blank rows in the data: Excel treats a blank row as the end of the data when it picks the range, so delete blank rows or select the whole range before sorting.
- Filtered data: when a filter is on, Excel sorts only the visible rows, and hidden rows stay where they are.
Frequently Asked Questions
Why does Excel sort 10 before 2?
Because the numbers are stored as text, and text is sorted character by character, so “10” comes before “2”. Convert them to numbers and sort again.
Why are my dates not sorting correctly?
Some of the dates are stored as text. A real date is aligned to the right by default; if a date is aligned to the left, convert it with DATEVALUE or Text to Columns.
The sheet is probably protected, or you’re editing a cell. Press Esc to leave the cell, or unprotect the sheet (Review ➜ Unprotect Sheet) and try again.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.