Home // Blog & Excel Tips // Why does Excel sort ‘numbers’ incorrectly

Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. What is the shortcut to ONLY select the visible cells? Guess, then click 'See Answer' as it can be done.
2. How do you copy from Excel (see 1 below) and paste into Word (2) WITHOUT seeing the filter button, gridlines, comment notes and more?

Guess and then view answer to see how it can be done!


 

Why does Excel sort ‘numbers’ incorrectly

Comments Closed
Uncategorised

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. Below is a video showing a similar issue where the dates are confused as text and so the sorting is incorrect (as dates are seen as numbers in Excel it has the same issue)

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 .