Using the Sort Order Feature in Excel Spreadsheets

Documents getting exchanged from files
Learn to sort your data with ease. Westend61 / Getty Images

Sorting is the process of arranging objects in a certain sequence or order according to specific rules. In spreadsheet programs such as Excel and Google Spreadsheets, there are several different sort orders available depending on the type of data you're sorting.

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 in 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 Z to A — for text data
  • Sort Largest to Smallest — for number data
  • Sort Newest to Oldest — for date or time data
Screenshot of Excel showing sorted numbers

Sorting by Color and Sort Orders

In addition to sorting by values, such as text or numbers, Excel has custom sorting options that permit sorting by color for text/font color, cell color, and conditional formatting icons.

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

Most spreadsheet programs, such as Microsoft Excel and Google Sheets, use the following default sort orders for different types of data, but options can still vary between application versions:

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, such as:

  • 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
Screenshot of Excel showing the sorting of alphanumeric data

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.

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

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 from your spreadsheet, and it is part of a range of data that is sorted, it will remain as is rather than be moved to its correct location as a result of the sort.

Keeping rows and columns hidden can significantly hinder the accuracy of your data sort. Be sure to unhide any rows or columns that should be sorted with your data.

The same idea follows for hidden columns of data. When sorting by columns, 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.