Contents hide
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 a text, or dates saved as text, you need to change its data type first (you can also use the VALUE or DATEVALUE functions).
- Having a Hidden Cell or Range: If you have a hidden cell or multiple hidden cells in the data. In that case, it won’t sort it, even if the data is in the correct format.
- Two Different Types of Data: If in the same column, you have two different types of data, in that case, Excel will sort the column considering different sorting orders. Number order for numbers and text order for text values.
- Sorting Data with Volatile Formulas: If you have volatile formulas, like RAND, RANDBETWEEN, TODAY, NOW, etc., and when you sort the data, these formulas will recalculate itself. Because values change and it seems like sorting is not working properly.
- Merged Cell in the Data: If one or more cells in the column are merged, it’s hard for Excel to consider the data type. It will show you an error message to remove merged cells to 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. It will help if you use a function like TRIM, CLEAN, or SUBSTITUTE to remove those spaces first and then sort the data.
Below are some of the tips that can help you to clean so that you can sort it.