Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Controlling Sorting Order.

Controlling Sorting Order

Written by Allen Wyatt (last updated February 24, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


1

If you have used Excel for more than a week, you probably already know how to sort information in your worksheet. You can use sorting to arrange a list in either ascending or descending order. Ascending order for the English language is from 0—9, A—Z; descending order is the reverse. The sorting order for other languages will be different. To specify the sorting order you simply choose the Ascending or Descending radio buttons in the Sort dialog box or, in Excel 2007, choose A to Z or Z to A in the Order drop-down list.

You can also affect sort order by indicating whether Excel should pay attention to the case of the text in your cells. If a sort is case sensitive, then items are sorted A-Z and then a-z for ascending sorts. If a sort does not pay attention to case, then uppercase and lowercase letters are treated the same.

To specify whether Excel should pay attention to case, follow these steps:

  1. Select the cells containing the information to be sorted.
  2. Display the Data tab of the ribbon.
  3. Click the Sort tool. Excel displays the Sort dialog box. (See Figure 1.)
  4. Figure 1. The Sort dialog box.

  5. Using the controls in the dialog box, indicate the way in which the cells should be sorted.
  6. Click on the Options button. Excel displays the Sort Options dialog box. (See Figure 2.)
  7. Figure 2. The Sort Options dialog box.

  8. Use the Case Sensitive check box to indicate how Excel should perform the sort. If the check box is selected, the sort is case sensitive; if clear, then case is ignored.
  9. Click on OK to close the Sort Options dialog box.
  10. Click on the OK button to perform your sort.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12575) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Controlling Sorting Order.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Duplicate Workbooks Opening

If you ever open a workbook and always see two workbooks instead of one, chances are good the reason is because of the ...

Discover More

Generating Double-Digit Random Numbers

Normally you use the RAND and RANDBETWEEN functions to generate random numbers. What if you want to generate random ...

Discover More

Formatting Issues with Indexing Levels

When you insert an index in a document, Word automatically takes care of formatting that index. What if the index levels ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Creating a Sort Order

Excel is very flexible in how it can sort your data. You can even create your own custom sort order that is helpful when ...

Discover More

Taking Bold Text into Account in a Sort

If you have some data in a worksheet that is bold, you may want to sort that data based on the text attribute. This is ...

Discover More

Separating Cells Based on Text Color

If the font color used for the data in your worksheet is critical, you may at some time want to move cells that use a ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is one more than 7?

2024-02-29 09:34:39

J. Woolley

It should be noted that the Windows version of Excel does NOT sort text characters in ANSI/ASCII/Unicode order. (Ditto for other Windows apps like Explorer.) The sort order is illustrated in the following table (see Figure 1 below) which is divided into three sections for convenient display. Each section has the following four columns:
1. The code value in ANSI/ASCII/Unicode order
2. The character for that code value: CHAR(col.1)
3. Column 2 sorted by Excel BEFORE dividing into three sections: SORT(col.2)
4. The ANSI code value of the character in column 3: CODE(col.3)
In column 3 notice that Excel sorts apostrophe and hyphen before space, all special characters are before numerals, and upper/lower case letters are together (A a B b C c ...) instead of separate (A B C ... a b c ...) as in ANSI/ASCII/Unicode.
With respect to letters, case sensitive Excel order moves each lower case before its upper case (a A b B c C ...) while case sensitive ANSI/ASCII/Unicode order keeps all upper case before lower case (A B C ... a b c ...).
With respect to VBA:
    Option Compare Text or StrComp(s1, s2, vbTextCompare)
        corresponds to Excel order with case ignored.
    Option Compare Binary or StrComp(s1, s2, vbBinaryCompare)
        corresponds to case sensitive ANSI/ASCII/Unicode order.
Excel's sort order becomes more curious when special characters are combined with letters like X'X or X-X or X:X or X?X or even XxX etc. In this case, the combinations with apostrophe or hyphen are sorted LAST when other things are equal (except perhaps the total number of characters).
My Excel Toolbox includes the SortANSI macro to sort all rows of a selected range by a specified column in ascending or descending order. The text portion of that column will be sorted in ANSI/ASCII/Unicode order, either case ignored or case sensitive, instead of Excel order. Undo (Ctrl+Z) is supported. SortANSI will not sort a range that has formulas because the results can be confusing; be careful when using Excel to sort cells containing formulas.
My Excel Toolbox also includes the following function:
    =SortPlus(RangeArray, [SortIndex], [SortOrder], [LeftRight],
            [HasHeader], [CaseSensitive], [CodeOrder],
            [CustomOrder], [NumberAsText], [TextAsNumber])
The first four arguments match the SORT function available in Excel 2021 or later; the remaining six are added features. All but the first are optional.
RangeArray can be a cell range (contiguous) or an array constant or the result of an array function.
SortIndex is the base-1 index of a column or row to sort by; default is 1.
SortOrder is 1 (default) for ascending or -1 for descending.
LeftRight is FALSE (default) to sort rows or TRUE to sort columns.
HasHeader is FALSE (default) to sort the first row/column or TRUE to skip the first row (if LeftRight is FALSE) or first column (if LeftRight is TRUE).
CaseSensitive is FALSE (default) to sort text with case ignored or TRUE for a case sensitive sort.
CodeOrder is FALSE (default) to sort text in Excel order or TRUE to sort text in ANSI/ASCII/Unicode order; TRUE applies only if CustomOrder and TextAsNumber are both FALSE.
CustomOrder is FALSE (default) to ignore, or a custom list number (like 1 for Sun, Mon, Tue,...), or a temporary custom list as comma separated text (like "N, S, E, W"). Backslash (\) can be used to escape comma in CustomOrder comma separated text.
NumberAsText is FALSE (default) to ignore; if TRUE and RangeArray is a cell range, then numeric cell values displayed as text are considered text.
TextAsNumber is FALSE (default) to sort numeric text separate from numbers or TRUE to treat numeric text as numbers.
The following example returns a case sensitive sort in ANSI/ASCII/Unicode order of a multi-column range with no header row by column 3 descending:
    =SortPlus(D1:H99,3,-1,,,TRUE,TRUE)
The next example sorts an array function result that includes a header first by column 1 then by column 2, both ascending:
    =SortPlus(SortPlus(ListDocProperties(),1,,,TRUE),2,,,TRUE)
SortPlus is slower but more capable than Excel's SORT and does not require Excel 2021+. If used in a cell formula, it returns a dynamic array. When using earlier versions of Excel without support for dynamic arrays, review the PDF file UseSpillArray.pdf.
See https://sites.google.com/view/MyExcelToolbox

Figure 1. 


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.