[Solved!] Excel Sort Not Working (2 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.


Download Practice Workbook

You can download the free Excel template from here and practice on your own.


2 Solutions to Sort-Not-Working Problem in Excel

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.

Related Content: How to Merge Cells Using Excel Formula (8 Simple Ways)


Similar Readings:


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 now arranged in the highest to lowest order.

Read More: How to Sort by Date in Excel (8 Suitable Ways)


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.

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.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo