Excel Not Sorting Numbers Correctly (4 Reasons with Solutions)

Reason 1 – Numbers Have Non-printable Characters

The sample dataset contains product price data copied from the internet and formatted in Excel Accounting format. The first 4 entries are not formatted correctly due to an unknown issue.

Numbers Have Non-printable Characters and Hence Excel Not Sorting Them Correctly

Let’s try to sort the data.

  • Select all the cells of the Price column.
  • Right-click and choose the Sort option from the Context Menu.
  • Select the Sort A to Z option.

Look at the result.

Numbers Have Non-printable Characters and Hence Excel Not Sorting Them Correctly

The sort isn’t working right. The bottom 4 cells are out of order.


How to Detect the Issue

We need to find out the number of non-printable characters in our desired data. We will use the Excel LEN function to find out the number of objects in each cell. Press Ctrl+Z to undo the previously applied sort operation.

  • Add a column named No. of Char.
  • Go to Cell D5 and enter the following formula.

=LEN(C5)

  • Press Enter and drag the Fill Handle icon downwards.

The new column shows the no. of the characters in each cell. In Cell D6 and D11, we have 2.  That means their corresponding cells C6 and C11 contain 2 characters. But we can see only a single numeric character there. So, there is One (1), non-printable character in cells C6 and C11.


Solution – Remove the Non-printable Characters with CLEAN Function

  • Add a new column named Verified Data on the dataset.
  • Go to Cell E5 and paste the formula below.

=CLEAN(C5)

Numbers Have Non-printable Characters and Hence Excel Not Sorting Them Correctly

  • Press Enter and pull the Fill Handle icon towards the last cell.

Numbers Have Non-printable Characters and Hence Excel Not Sorting Them Correctly

  • Copy the numbers from the Cleaned Data column, click on Cell E5 and type ALT+H+V+V to paste them as values.
  • Click on the Error icon and select the Convert to Number option.

  • Apply the sort operation as shown previously.

After removing all the non-printable characters, data has been sorted successfully.

Read More: [Solved!] Excel Sort Not Working


Reason 2 – Presence of Leading or Trailing Spaces

If the numbers contain leading or trailing spaces in them, then you will face sorting issues with such numbers.

Excel not sorting numbers correctly due to having leading or trailing spaces

Notice that all data are not right-aligned in the Price column.

Try to sort the data in the Price column from the smallest to the largest.

Excel not sorting numbers correctly due to having leading or trailing spaces

The last 3 cells are not sorted correctly.


How to Detect the Issue

If your numeric data has leading or trailing spaces, Excel will see them as text instead of actual numbers. If you fail to sort your numeric data correctly, you can use the Excel ISNUMBER function to check whether they are numeric values.

  • Add a column named Status.
  • Enter the following formula on Cell D5.

=ISNUMBER(C5)

  • Hit Enter and expand to the rest of the cells of that column.

We get TRUE if the object is a number otherwise, we will get FALSE. The last 3 objects of the Price column contain data; those are not pure numbers. There are leading and trailing spaces with the data.


Solution – Get Rid of the Spaces with TRIM Function

Steps:

  • Add another column named Modified Data.
  • Go to Cell E5 and enter the following formula.

=TRIM(C5)

  • Hit Enter and expand to the rest of the cells of that column.

Solution: Excel not sorting numbers correctly due to having leading or trailing spaces

  • Sort the dataset from smallest to largest.

We successfully performed sorting.

Read More: [Fixed!] Sort and Filter Not Working in Excel


Reason 3 – Nume[Fix] Excel Sort by Date Not Workingric Values Accidentally Formatted as Text and Not Being Sorted Properly

In our dataset, we can see all our numbers in the Price column. However, some of the cells contain data that are not in Number format but are in Text format.

Numeric Values Accidentally Formatted as Text and Not Being Sorted Properly

  • Sort the data from the smallest to the largest.

We can see the last 4 cells remain unsorted.


How to Detect the Issue

We need to find out whether they are numbers or not. The ISNUMBER function is used for this.

  • Go to Cell D5 in the Status column.
  • Enter the formula below.

=ISNUMBER(C5)

  • Hit Enter and expand to the rest of the cells of that column.

We can see that FALSE in the last 4 cells. That means those are not numbers. We need to get the numeric value of those text data.


Solution 1 – Convert Text to Numeric Data with VALUE Function

  • Go to Cell E5 and paste the following formula.

=VALUE(C5)

Solution: Numeric Values Accidentally Formatted as Text and Not Being Sorted Properly

  • Hit Enter and expand to the rest of the cells of that column.

Solution: Numeric Values Accidentally Formatted as Text and Not Being Sorted Properly

We get numeric values from the text data.

  • Sort from the smallest to the largest.

The data is now sorted correctly.

Read More: Excel Sort by Cell Color Not Working


Solution 2 – Convert Text into Number Using a Default Button

We have an alternative solution to this problem. That is to convert the text values into numbers and sort them.

  • Press Cell C11 which contains a text value.
  • A warning button will show with multiple options.
  • Choose the Convert to Number option.

Solution: Numeric Values Accidentally Formatted as Text and Not Being Sorted Properly

  • Do this for other cells that contain text values.

  • Now, we have converted all the values to numbers. Sort the number from smallest to largest.

We get the sorted result here.


Reason 4 – Numbers Generated with Excel RAND or RANDBETWEEN Functions

Sometimes we generate random numbers in Excel using  RAND or RANDBETWEEN functions. One issue lies with such numbers- whenever you perform an operation within the range of cells that have random numbers, the numbers frequently change. So when you try to sort such random numbers, it will not give you the proper results.


Solution – Copy the Generated Numbers and Paste Them into the Same Place

  • Select all the cells of the Age column.
  • Copy them by pressing Ctrl+C.
  • Right-click and select Values(V) from the Context Menu.

Solution: Numbers Generated with Excel RAND or RANDBETWEEN Functions Have Issues with Sorting

  • The output will be fixed values. They will not behave like random data from now.

Solution: Numbers Generated with Excel RAND or RANDBETWEEN Functions Have Issues with Sorting

  • Sort the Age data from the smallest to the largest.


Download Practice Workbook


Related Articles


<< Go Back to Excel Sort Not Working | Sort in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

1 Comment
  1. Or u just copy/save all data to csv format sheet – open it in LibreOffice and do the magic one 1 click only the right way. Then u copy values back to crappy excel (if u have the need) ! Without any “vacuum cleaner” twist and quirks ms engineer requirements for one click function that should work out of the box. This only shows how ms has ideas how excel had to become not so 1 click function sheet. Time to ditch it.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo