How to solve If Excel Sort Is Not Working (2 Solutions)

Solution 1: Delete the Same Sheet’s Reference from the Formula If ‘Sort’ is Not Working

Problem:

In the following dataset, we calculated the sales sum from another sheet using the SUMIF function for different stores.

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

Our output is not what we expected. Where is the problem?

This inconsistency is because we have used the same sheet reference in which I am working.

Solution:

Delete the sheet name from the formula and just keep the cell reference only.

Steps:

Select the data range B5:C8.
Click- Home > Editing > Sort & Filter > Custom Sort.
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.

Solution 2: Using Date Format Or Text to Column Wizard for Date Sorting

To sort it from Newest to Oldest order, select the date range and click as follows: Home > Editing > Sort & Filter > Highest to Lowest.

Problem:

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 for showing them. That’s why Excel is giving the wrong output in this case.

We can solve it in two ways:

Solution One: Re-write the Dates Using Date Format

Steps:

Select Date format for the dates. It won’t give the correct result.
You must 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.

Sort it by clicking- Data > Highest to Lowest.

Now Excel is giving the correct sorting result.

Solution Two: Use Text to Columns Wizard

Steps:

Select the dates and click as follows- Data > Text to Columns

A dialog box will open up. Press Next.

Press Next.

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

Select the dates and sort them again by clicking- Data > Highest to Lowest.

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. If you use multiple headers, use wrap text or apply Alt+Enter to force line breaking.
Ensure there is no column without a header if you use headers while sorting.
Make sure the data is the same type for the whole column.
There should be no merged cells in your dataset, Sort & Filter doesn’t work for merged cells.
Before sorting the data, create a backup copy of your Excel file so that you can retrieve it if anything goes wrong.
Ensure there are no blank rows or columns in your dataset while sorting.