[Solved!] Excel Sort Not Working (2 Solutions)

Get FREE Advanced Excel Exercises with Solutions!

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.

By Fixing the Referring Same Sheet Problem for Sort Not Working in Excel

We have tried to sort the Sales from Largest to Smallest but have got the following result.

By Fixing the Referring Same Sheet Problem for Sort Not Working in Excel

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.

By Fixing the Referring Same Sheet Problem for Sort Not Working in Excel

Solution:

The solution is very simple, just delete the sheet name from the formula and just keep the cell reference only.

By Fixing the Referring Same Sheet Problem for Sort Not Working in Excel

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.

By Fixing the Referring Same Sheet Problem for Sort Not Working in Excel

Now have a look that we have got the accurate sorting result.


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.

Using Date Format Or Text to Column Wizard for Date Sort Not Working in Excel

See that it is showing the wrong order.

Using Date Format Or Text to Column Wizard for Date Sort Not Working in Excel

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.

Using Date Format Or Text to Column Wizard for Date Sort Not Working in Excel

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.

Using Date Format Or Text to Column Wizard for Date Sort Not Working in Excel

Now Excel is giving the correct sorting result.

Using Date Format Or Text to Column Wizard for Date Sort Not Working in Excel

Second Solution: Use Text to Columns Wizard

Select the dates and click as follows-

Data > Text to Columns

Using Date Format Or Text to Column Wizard for Date Sort Not Working in Excel

A dialog box will open up. Nothing to do here, just press Next.

Using Date Format Or Text to Column Wizard for Date Sorting

Again press Next.

Using Date Format Or Text to Column Wizard for Date Sorting

Now from the Column data Format box, mark the Date option and select DMY from the drop-down list.

Then just press Finish.

Using Date Format Or Text to Column Wizard for Date Sorting

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.


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.


Excel Sort Not Working: Knowledge Hub


<< Go Back to Sort in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo