Article 39 – Sorting in Excel

First thing first – Excel doesn’t sort as per ASCII character codes which we generally expect. It has its own sorting logic which is detailed below for Ascending sort (reverse the same for Descending sort).

The order followed in an Ascending sort is Number > Text > Alphanumeric text > Logical values > Error values > Blanks

(Note –Blank cells will always be at the end whether it is an Ascending sort or a Descending sort)

 Priority 1 – Number has first priority in sorting. Numbers are sorted from the smallest negative number to the largest positive number

Priority 2 – Text has second priority. There is no difference between lowercase and uppercase. Whatever case comes first in case of a tie, gets placed first (FIFO – First In First Out sorting). Hence, sorting “a,A,B,c,D,b,C” will give “a,A,B,b,c,C,D”

In Text / Alphanumeric sort, Sort compares left to right, character by character between two strings. For example, “sd, sar” when ascending sorted will be “sar, ad”.

Priority 3 – Alphanumeric text (text that includes numbers and other characters) has third priority. For example “monkey123” gets sorted before “monkeyed” whereas “mon9keyed” will get sorted before “monkey123”.

The sorting order for alphanumeric text is –

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

Apostrophes (') and hyphens (-) are ignored, with one exception: If two text strings are the same except for a hyphen, the text with the hyphen is sorted last.

Priority 4 – Logical Values – FALSE is sorted before TRUE.

Priority 5 – Error Values – No difference between error values. Hence, whatever comes first, gets placed first (FIFO sorting). Hence. “#DIV/0!, #N/A” will give the result “#DIV/0!, #N/A” whereas “#N/A, #DIV/0!” will give the result “#N/A, #DIV/0!”.

Below is Excel sort for ASCII codes. (The same can be downloaded in Excel format – Excel Sort – ASCII)