Sorting is one of the basic features in Excel.  I believe most Excel users, if not all, know how to sort in Excel.  Without any scientific research, I think most users would click the “Sort A to Z” (ascending order) or “Sort Z to A” (descending order) icon directly.

That’s the case for myself unless I need to do multilevel sorting or Sort by row.

When we do single level sorting, i.e. sort based on one column only, we don’t have the motivation to click on the “Sort” as it initiates another dialogue box which requires more actions to perform the same task…

A rational lazy people like me won’t do that. 😛

It was not until one day I read the comments from Sabine and Michael (Micky) Avidan to the post Copy and Paste can do so much.

Think about the situation below:

(Note: 2 and 4 are “Number stored as text“)

When you click “Sort A to Z”, you will get this:

When you click “Sort Z to A”, you will get this:

You may think that Excel does not sort correctly… but indeed Excel does consider that the correct sort order, as Excel sorts number before text.

The following is the sorting order documented at support.microsoft.com:

0 1 2 3 4 5 6 7 8 9 (space) ! ” # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

You may find more details on how Excel sorts here.

If you need Excel to sort from 1 to 5 (“A to Z“), or 5 to 1 (“Z to A“), you may need to convert the “number store as text”, i.e. 2 and 4 in our example, to number first.  You may find different ways for this conversion in my earlier post.

BUT, there is a very hidden “Sort Warning (Setting?)” to allow you to sort data mixed with numbers and “numbers stored as text” in the way as if they are all numbers.

Most setting resides on Excel Options, Advanced.  However this “Sort Setting” is not, probably because this is indeed a “Warning“.  To initiate this “Sort Warning”, we need the cumbersome way to “Sort”:

Let’s assume I want to sort “Z to A”…. When I click OK, I got the “Sort Warning”:

By default, it’s set as “Sort numbers and numbers stored as text separately“.  That’s why we got the sorting results above when we click “Sort A to Z” or “Sort Z to A” directly.

If we change the setting to “Sort anything that looks like a number, as a number“,

Miracle happens:

The data is sorted “correctly” while the original values retained.  Number is still numbers, “number stored as text” is still “number stored as text”.

What’s even better?

Excel remembers this setting for you.  Try in a new workbook and type the 1 to 5 with 2 and 4 as “number stored as text”.  This time, you may do the lazy way: Click the “Z to A” directly and get the desired result.

Tip: Excel also remembers if the “My data has headers” is checked or not.

With this setting, you can sort dates (stored as text) directly without converting them to  numeric dates, as discussed here.

Treasure is well hidden.  You will need persistence and sometimes luck to find it.

Thanks Sabine and Micky again for the inspiration of this post.