Using Sort Order in Excel Spreadsheets

Excel Sort Order
Excel Sort Order.

Sorting is the process of arranging objects in a certain sequence or sort order according to specific rules.

In spreadsheet programs such as Excel and Google Spreadsheets, there are a number of different sort orders available depending on the type of data being sorted.

Ascending vs Descending Sort Order

For text or numeric values, the two sort order options are ascending and descending.

Depending upon the type of data in the selected range, these sort orders will sort data the following ways:

For ascending sorts:

  • Sort A to Z - for text data
  • Sort Smallest to Largest - for number data
  • Sort Oldest to Newest - for date or time data

For descending sorts:

  • Sort A to Z - for text data
  • Sort Largest to Smallest - for number data
  • Sort Newest to Oldest - for date or time data

Hidden Rows and Columns and Sorting

Hidden rows and columns of data are not moved during sorting, so they need to be unhidden before the sort takes place.

For example, if row 7 is hidden, and it is part of a range of data that is sorted, it will remain as row 7 rather than be moved to its correct location as a result of the sort.

The same goes for columns of data.  Sorting by rows involves reordering columns of data, but if Column B is hidden before the sort, it will remain as Column B and not be reordered with the other columns in the sorted range.

Sorting by Color and Sort Orders

In addition to sorting by values, such as text or numbers, Excel has custom sorts options that permit sorting by color for:

Since there is no ascending or descending order for colors, the user defines the color sort order in the Sort dialog box.

Sort Order Defaults

Source: Default sort orders

Most spreadsheet programs use the following default sort orders for different types of data.

Blank Cells: In both ascending and descending sort orders, blank cells are always placed last.

Numbers: Negative numbers are considered the smallest values, so the largest negative number always comes first in an ascending sort order and last in descending order, such as:
Ascending Order: -3,-2,-1,0,1,2,3
Descending Order: 3,2,1,0,-1,-2,-3

Dates: The oldest date is considered to be of lesser value or smaller than most recent or newest date.
Ascending Order (oldest to most recent): 1/5/2000, 2/5/2000, 1/5/2010, 1/5/2012
Descending Order (most recent to oldest): 1/5/2012, 1/5/2010, 2/5/2000, 1/5/2000

Alphanumeric Data: A combination of letters and numbers, alphanumeric data is treated as text data and each character is sorted from left to right on a character by character basis.

For alphanumeric data, numbers are considered to be of lesser value than letter characters.

For the following data, 123A, A12, 12AW, and AW12 the ascending sort order is:

123A
12AW
A12
AW12

Descending sort order is:

AW12
A12
12AW
123A

In the article How to correctly sort alphanumeric data in Excel, located on the Microsoft.com website, the following sort order is given for characters found in alphanumeric data:

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

Logical or Boolean Data: TRUE or FALSE values only, and FALSE is considered lesser in value than TRUE.

For the following data, TRUE, FALSE, TRUE, and FALSE the ascending sort order is:

FALSE
FALSE
TRUE
TRUE

Descending sort order is:

        TRUE

TRUE FALSE FALSE

Was this page helpful?