Sorting numbers in Excel looks easy but there’s traps if you’re not careful. Let’s look at an example of sorting gone wrong, how it’s easy to do but also fix. Finally how modern Excel has several features to warn you not to fall into a common mistake.
This table popped up on Twitter (original source unknown) … obviously the ‘sort by population’ is wrong. Can you figure out why?
The population numbers are text not numbers. Then sorted in reverse alphabetical order! Commas are ignored.
It’s easy to do when importing or converting data. In fact, it’s so common that Microsoft Excel has various internal checks to warn about treating numbers as text, see below.
Of course, sometimes what looks like a number has to be treated like text. For example, serial numbers, product (UPC) codes etc.
We dropped the table above into Excel (thanks to the ‘Data from Picture’ feature) feature.
When Excel sorts alphabetically a column of values, there’s a warning (see below).
As you can see, the sort order is different to the original image. That’s because the cell text has comma separators. The same ‘values’ will sort differently with or without commas. Here’s how Excel sorts the Population column without commas (Column E), giving the same order as in the original image.
(The original image has a population column showing comma separators but the underlying sort using ‘no commas’).
Excel’s protections against treating numbers as text
Mistaking values for text has been a problem for as long as spreadsheets have existed. Peter Deegan remembers seeing a worksheet bug back in 1986 when there were no warnings that a single cell was text while the rest of the columns were numbers. Finding that bug took hours!
These days Excel’s various import features such as PowerQuery, Text Import Wizard and Data from Picture should convert text to number cells.
Mistakes can happen, especially if there’s non-digit content detected (e.g. OCR errors like 12S45 32I98 )
ALWAYS check that Excel has correctly converted text to numbers.
Background Error Checking should also notice that there’s text that should be numbers. Little ‘Audit’ flags will appear next to cells like this “Number Stored as Text”.
Choose ‘Convert to Number’ or ‘Ignore Error’.
If you try to sort text that seem to be numbers, Excel will again warn with options for the sort.
Sort Warning:
“The following sort key may not sort as expected because it contains some numbers formatted as text: What would you like to do?
- Sort anything that looks like a number, as a number
- Sort numbers and numbers stored as text separately “
Does the result make sense?
The final check for any Excel worksheet is YOU …
Look at the results.
Do they make sense?
That applies to any calculation, not just sorting.
Quick Excel list sorting and filter buttons
Converting Text with month and year into Excel dates
Better text conversion options coming to Excel
Five ways to turn text into Excel dates
