Sort Warning?!

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.

Excel Tips - Sort Warning 1.PNGThat’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…Excel Tips - Sort Warning 3.PNG

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:

Excel Tips - Sort Warning 2.PNG(Note: 2 and 4 are “Number stored as text“)

When you click “Sort A to Z”, you will get this:Excel Tips - Sort Warning 4

When you click “Sort Z to A”, you will get this:Excel Tips - Sort Warning 5

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”:

Excel Tips - Sort Warning 6.PNG

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

Excel Tips - Sort Warning 7.PNG

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“,

Excel Tips - Sort Warning 8.PNG

Miracle happens:

Excel Tips - Sort Warning 9.PNG

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.

Excel Tips - Sort Warning 10.PNG

 

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.

excel-tips-sorting-dates1

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.

Advertisements
This entry was posted in Excel Tips and tagged . Bookmark the permalink.

One Response to Sort Warning?!

  1. Pingback: #Excel Super Links #30 – shared by David Hager | Excel For You

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s