Why does Excel sort ‘numbers’ incorrectly

Excel number sort order problems

Occasionally you may try and sort some numbers in Excel and instead of getting this

1, 2, 3, 4, 5, 10, 11, 12, 13, 14 (which is smallest to largest)

it gives you something like

1, 10, 11, 12, 13, 14, 2, 3, 4, 5

The reason this happens is because Excel has decided that the ‘numbers’ are actually text and so it is sorting the ‘text’. So in much the same way that words sort based on there letters, the numbers sort on the digits instead of the value.

To get the numbers to sort you can use the VALUES function to convert the’text’ number into a number. In newer versions of Excel, you will be asked whether you want to sort the numbers as numbers or text.

Related: This is one of the many Data Cleanup issues that you will experience in Excel. Understanding how to change numbers and dates so that Excel actually sees them as numbers and dates is critical. You can learn all these tricks in the  Excel Data Cleanup Course .