[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.

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.

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

Similar Readings

• Auto Sort When Data is Entered in Excel (3 Methods)
• How to Sort IP Address in Excel (6 Methods)
• Add Sort Button in Excel (7 Methods)
• How to Sort Unique List in Excel (10 Useful Methods)
• Use Excel Shortcut to Sort Data (7 Easy Ways)

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 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

• Sort Column by Value in Excel (5 Methods)
• How to Sort by Color in Excel (4 Criteria)
• Sorting Columns in Excel While Keeping Rows Together
• How to Sort Alphabetically in Excel with Multiple Columns (4 Methods)
• Sort by Last Name in Excel (4 Methods)
• How to Sort Excel Sheet by Date (8 Suitable Examples)