Sorting data is one of the easiest and most frequently used operations in MS Excel. What if the sort command doesn’t work properly? This article will rescue you with two easy solutions if the Sort Command is not working properly in your Excel worksheet.
Excel Sort Not Working (2 Solutions)
Solution 1: Delete Same Sheet’s Reference from Formula If Sort is Not Working
If we use the sheet reference in the same worksheet in a function or formula, sorting will give you an incorrect result. Let’s see the problem first.
Problem:
In the following dataset, I have calculated the sum of sales from another sheet by using the SUMIF function for different stores.
We have tried to sort the Sales from Largest to Smallest but have got the following result.
So, our output is not what we expected. Where is the problem?
The reason behind this inconsistency is I have used the same sheet reference in which I am working.
Solution:
The solution is very simple, just delete the sheet name from the formula and just keep the cell reference only.
Now, the sort command will work properly. Just execute the following steps.
Select the data range B5:C8.
Then click- Home > Editing > Sort & Filter > Custom Sort.
Later, select the Sales option from the Sort by box and Largest to Smallest from the Order box.
Press OK.
Now have a look that we have got the accurate sorting result.
Read More:Â [Fixed!] Sort and Filter Not Working in Excel
Solution 2: Using Date Format Or Text to Column Wizard for Date Sorting
In this method, I’ll show how to solve the problem while sorting dates. For that, I have placed some dates in this dataset.
To sort it for Newest to Oldest order, select the date range and click as follows:
Home > Editing > Sort & Filter > Highest to Lowest.
See that it is showing the wrong order.
The reason is that we are using the dates in Text format. Excel counts the dates as numbers and has a well-defined format to show dates. That’s why Excel is giving the wrong output in this case.
We can solve it in two ways, follow the process described below-
First Solution: Re-write the Dates Using Date Format
If we select the Date format for the dates now, it won’t give the correct result either.
So the solution is- you will have to write the dates in date format first, then apply the sort command to get the right output.
Here, I have re-written the dates in Date format again. Now if I sort it by clicking- Data > Highest to Lowest.
Now Excel is giving the correct sorting result.
Second Solution: Use Text to Columns Wizard
Select the dates and click as follows-
Data > Text to Columns
A dialog box will open up. Nothing to do here, just press Next.
Again press Next.
Now from the Column data Format box, mark the Date option and select DMY from the drop-down list.
Then just press Finish.
Now select the dates and sort them again by clicking- Data > Highest to Lowest.
Now have a look that the dates are arranged in the highest to lowest order.
Read More: Excel Not Sorting Numbers Correctly
Precautions to Avoid Sorting Trouble
To avoid any kind of sorting trouble we can take some precautions.
- There should be no hidden rows or columns in your dataset.
- Instead of multiple rows, use a single row for headers. In the case of using multiple headers, use wrap text or apply Alt+Enter to force line breaking.
- Make sure there is no column without a header if you use headers while sorting.
- Make sure the data is the same type toward the whole column.
- There should be no merged cells in your dataset, Sort & Filter doesn’t work for merged cells.
- Before starting to sort the data, create a backup copy of your Excel file. So that you can get it back if anything goes wrong.
- Be sure that there are no blank rows or columns in your dataset while sorting.
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
Conclusion
I hope the procedures described above will be good enough to solve the Excel sort not working problem. Feel free to ask any question in the comment section and please give me feedback.
