Sorting,  Some notes on sorting in Excel
Home page:
[View without Frames]

Material on backups has been moved to it's own page.

This page contains or refers to some text pages containing VBA macros and User Defined Functions.  If you need assistance to install or to use a macro or function please refer to my  «Getting Started with Macros« or delve into it deeper on my Install  page.

Choosing the Sort Region (#region)

It is best to choose your region first

For instance select    A2:H200
Note when you sort there is a box in lower left corner of the sort dialog box to indicate whether your selection includes headers (column/rows).

Sorting on a column does not automatically include keeping the row data with the column data.  If you select Column A and then sort Col A that is all you sort.

Excel has a little trick that if you select a single cell, and only a single cell, Excel will extend the range to the current region.
    Select the current region    CTRL+SHIFT+* The current region has boundaries at edges of worksheet or up to a blank column or row boundary (MS definition of ISBLANK). To include all of the data that is next to another cell with data next to a selection.  You hardly ever want to use the current region when doing a sort, so read the next topic to avoid risking your data.

If you don't choose your region first

It is best for you to select the region yourself.  You may select the entire spread sheet, or entire columns, only data in the used range will be sorted.  Note if you were writing a macro or use someone else's macro you want to make sure that processing is limited to the used area, as opposed to every cell in the spreadsheet, which may mean that you have to be careful with what you select, but the sort will always restrict itself to the used area.

Sorting on the current region (Ctrl+SHIFT+*) which is defaulted by a single cell selection is one of the worst implementations ever devised by Microsoft and indiscriminant use will eventually result in the destruction of your data and you may not notice the problem until days later when you discover you have jumbled data.  It gets worse if you have Excel 2003 and much worse in Excel 2007 (see Ctrl+A below).

Sorting by rows (#rows)

Normally sorting is by column, but you can sort by rows with the option button, be careful to put it back if you experiment.

Select All cells on Sheet before Sorting (#ctrlA)

To avoid such problems for most worksheets where you want all columns to be included (move with) the sorted data, simply select the entire sheet  (Ctrl+A).  In fact the best use of the Sort icons can be done by selecting any cell in the column you want to sort on, then using Ctrl+A, then the sort icon button.  If you have Excel 2003 your data is at more risk and the risk gets worse in Excel 2007 see shortcut keys foobar rather than risk your data using default Ctrl+A.

But wouldn't one always want to include the entire row

You should specify the entire range, and no, one does not always want to include everything on a row with the sorted columns.  There could be distinct areas on the spreadsheet for different types of data.  Here are some common things.

Sort toolbar buttons     (#icons)

If you only want to sort on one column and include all of the associated row the easiest way is to select a cell in the sort column, then select the region, Ctrl+A, then you can use the ascending sort (A-Z) button, the descending sort (Z-A) button, or use the Data, Sort menus.  Starting with Excel 2003 simply using Ctrl+A is not good enough and it gets worse in Excel 2007 -- your data is at severe risk, see foobar, rather than risk your data using default Ctrl+A.

Selecting a cell then Ctrl+A is the only way that I would recommend using the ascending or decending sort toolbar buttons. I am glad I found a safe way of using the sort toolbar buttons because it is easier than going through the menus if you only need to sort on a single column (with associated data).  One additional item is that in order to prevent the first row from being sorted make sure that it is bold and the 2nd row is not entirely bold this will usually meet the criteria used for automatic determination of headers in the sort.  Also helps to make sure that every column has a header label.  If it messes up you can use the UNDO button (or better Ctrl+Z).  If you have Excel 2003 your have a problem and it gets worse in Excel 2007 see shortcut keys foobar rather than risk your data using default Ctrl+A.

If you really want control of headers or no headers, then you can install macros and assign them to a button of your choice or design.

Sub SortAscending_NoHeader()
  Cells.Sort Key1:=ActiveCell,
  Order1:=xlAscending, Header:=xlNo, _
  OrderCustom:=1, MatchCase:=False, _
End Sub
    Sub SortAscending_Header()
  Cells.Sort Key1:=ActiveCell, _
  Order1:=xlAscending, Header:=xlYes, _
  OrderCustom:=1, MatchCase:=False, _
End Sub

Sorting with DoubleClick data has headers (#dclick)

Since the sort buttons can not be assumed to include or omit headers, you would have to consider their use to be unreliable.  The following example would allow you to double-click on any cell and you would sort on that column.  Since this is a worksheet Event macro you can modify it to tailor it to your specific sheet, such as adding a KEY2, or not including the last row of data (determined by data in Column A) in the sort.  Like sorting with the buttons if you want to sort on two columns, you can sort the most minor first up to the major sort.

To install the following event macro, right click on the sheet name, view code, and place the following code within -- only one Option Explicit statement and it would be first.

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim LRow As Long    '-- D.McRitchie, 2006-04-01 double-click on column to sort
  'Find last row in Column A with content
   LRow = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).row
   Rows("2:" & LRow).Sort Key1:=Cells(2, ActiveCell.Column), _
      Order1:=xlAscending, Header:=xlNo, _
      OrderCustom:=1, MatchCase:=False, _
End Sub
'If you want to also omit the last row from the sort (based on Column A) use instead
     LRow = Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0).row

'To include a minor sort, then also include code for a secondary key, in this case will assume that is Col A (col 1) such as for a date, but you can hard code a different secondary sort for your Key2.  After all this is specific to a sheet.
     Sort Key2:=Cells(2, 1), _

Sort Sequences

Sort sequences (#seq)

A frequent posting involves sorting that appears to be not working correctly according to what the poster expects.  Usually the posting is strictly a matter that what appears to be a number is not a number but is text.  Text may be because the field was formatted as text, it has a quote in front to make it text, or it is mixed numbers and letters.

Numbers are sorted first, when in ascending order.

The ASCII collating (sort order) sequence places digits before letters.  In Excel and most PC software upper and lowercase letters are treated identically for sorting purposes.

Another aspect of sorting in Excel is that Blank cells are sorted at the end both in ascending and in descending sorts.  A Blank cell in Excel is what would normally be called a null cell.   A cell which has not content is a Blank cell.  Putting in a space or any character make it no longer a blank cell.  A cell may become blank again by using Edit --> Clear,  it cannot be made blank with the Del key.

Cells consisting of a different number of spaces will not sort as equal.  See   Default sort orders.

Default sort orders (#order)

Microsoft Excel uses specific sort orders to arrange data according to the value, not the format, of the data. In an ascending sort, Microsoft Excel uses this order:

In a descending sort, Microsoft Excel reverses the order of everything except blank cells, which are always sorted last.

Note   Microsoft Excel will use any custom sort order you select instead of the default sort order. -OR- so it says but appeared to work only for lists.

For more comprehensive information on sorting suggest the following:
      HELP --> Answer Wizard -->  sort  sorting

Sorting mixed alpha and numeric cells (#mixed)

Sorting on column C  yields  9110 9150 0005 1243 1243
when the expected results were 0005 1243 1243 9110 9150

Excel puts cells which are numeric in front of cells which are text.  Excel extends this a bit further in saying that fields that cells that are numeric will be sorted before cells that are not numeric.  What you think is numeric is not necessarily what Excel thinks is numeric.  For instance any *number* beginning with a zero is text.  Text cells are are sorted in ascii collating (sorting) sequence left to right (spaces included), and numeric cells are sorted as a group numerically before the grouping of text cells.

The "0005"  appears to be text because for a number to appear that way you would have to format it.  The others are not so obvious but they two are text rather than numbers.

Blank cells is the name Excel uses rather inappropriately (IMHO)  for empty (null or never used cells).  Blank  cells are sorted to the end.  A formerly used cell can be made blank using Edit->Clear but cannot be made blank simply
using the Del key.

There is a difference between a cell with one space and a cell with two spaces -- you can use LEN Worksheet Function to see the actual length.

So I expect that you probably have single quotes in front of those that don't begin with "9"; otherwise, you should see TEXT as the format.

Problem: want to sort mixed text and numbers as if text
Solution:  Create a helper column, use to following formula and fill down, then sort on the helper column.
  =IF(ISBLANK(A1),CHAR(255), IF(ISTEXT(A1),A1,TEXT(A1,"@")))

For a macro solution, format the column as text then run the Convert_Numeric_Constants_to_Text_in_Selection macro.  Afterwards your entires will all text text by your formatting.

Extract Numbers for Sorting mixed numbers and alpha, numeric prefix (#extract)

Mixed numbers and alpha codes sorted numerically using CellValue. 

A suggestion from John Walkenbach.  The table at right has been sorted by column B then column A.

CellValue extracts the numeric portion.

In actual usage the original Column A would probably be aligned left or right for consistency.

Function CellValue(c) As Double
    'John Walkenbach 2001-04-25
    '      misc returns number part
    CellValue = Val(c)
End Function
Anything beginning with nonumeric will yield 0, resulting in those without numbers appearing first if sorted as described.
211 =cellvalue(A2)
31a1 =cellvalue(A3)
422 =cellvalue(A4)
52c2 =cellvalue(A5)
62cde2 =cellvalue(A6)
733 =cellvalue(A7)
833 =cellvalue(A8)
924 24=cellvalue(A9)
1030 30=cellvalue(A10)
11100 100=cellvalue(A11)
12100.a100 =cellvalue(A12)
13100a100 =cellvalue(A13)
14100.1 100.1=cellvalue(A14)
15200abc200 =cellvalue(A15)
16200.1 200.1=cellvalue(A16)
17200.1a3  200.1 =cellvalue(A17)

Sorting product code with alpha prefix and numeric suffix (#pcdigits)

Want to sort Product Codes with alpha prefixes numerically after their alpha prefix.

The following table is sorted on Product#
  But is wanted as follows:
The following solution was posted by Tom Ogilvy on 7JUN1999 and the results can be seen in the table following.  Column B & C can be deleted after sorting or kept as you wish.  Column D & E are included strictly to show formulas in use.  Failure to have both alpha and numeric portions may result in unexpected values.
Create two dummy columns.  Put your text portion in one column and your numeric portion in the other.  This formula will get the text portion from cell A2:
It is an array formula, so enter it with Ctrl+shift+enter

This formula will get the numeric portion and uses the results of the of the above formula:   The multiplication by 1 forces entry to a number instead of text.

Adjust these to address your first row of data.  Then select both cells and double click in the lower right corner of the rightmost cell to have the formulas fill down the column.

Then sort on these columns.  Columns B and C, in the following example.

Also, in the first formula, you can replace the hard coded arrays with row(indirect("1:6"))    As coded up to 6 characters may precede the numeric portion.

1 Part# ALPHA
=GetFormulaI(b2) =GetFormula(c2)
2 ab2 ab 2 {=LEFT(A2,MIN(IF(ISNUMBER(MID(A2,{1,2,3,4,5,6},1)*1),{1,2,3,4,5,6},7))-1)} =RIGHT(A2,LEN(A2)-LEN(B2))*1
3 ab14 ab 14 {=LEFT(A3,MIN(IF(ISNUMBER(MID(A3,{1,2,3,4,5,6},1)*1),{1,2,3,4,5,6},7))-1)} =RIGHT(A3,LEN(A3)-LEN(B3))*1
4 OP1 OP 1 {=LEFT(A4,MIN(IF(ISNUMBER(MID(A4,{1,2,3,4,5,6},1)*1),{1,2,3,4,5,6},7))-1)} =RIGHT(A4,LEN(A4)-LEN(B4))*1
5 OP12 OP 12 {=LEFT(A5,MIN(IF(ISNUMBER(MID(A5,{1,2,3,4,5,6},1)*1),{1,2,3,4,5,6},7))-1)} =RIGHT(A5,LEN(A5)-LEN(B5))*1
6 OP30 OP 30 {=LEFT(A6,MIN(IF(ISNUMBER(MID(A6,{1,2,3,4,5,6},1)*1),{1,2,3,4,5,6},7))-1)} =RIGHT(A6,LEN(A6)-LEN(B6))*1
7 OP31 OP 31 {=LEFT(A7,MIN(IF(ISNUMBER(MID(A7,{1,2,3,4,5,6},1)*1),{1,2,3,4,5,6},7))-1)} =RIGHT(A7,LEN(A7)-LEN(B7))*1
8 OP1221 OP 1221 {=LEFT(A8,MIN(IF(ISNUMBER(MID(A8,{1,2,3,4,5,6},1)*1),{1,2,3,4,5,6},7))-1)} =RIGHT(A8,LEN(A8)-LEN(B8))*1

Alternative using ALPHA_N formula to create a single string

On another page I have a formula that will create a single string for sorting with the alpha portion on left, the numeric portion on the right and sufficient zeroes in between to fill out the string to the specified number of characters.  See Sorting strings with alpha on left and digits on right

Regular Expressions to help with sorting (#regexpr)

Extraction of a Group of Digits and Dashes, from postings by Harlan Grove, Regular Expressions.

Generating a Sort Macro, Recording a Macro (#record)
You can generate a sort macro by recording a macro to see what is generated.  You will see something like this:
Sub Macro39()
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
End Sub
Recording a Macro hardly ever gets you exactly what you want.  Generally you use it to get an idea of what statements you need to use, and then look at those commands (methods) in your VBE HELP.  In this case the coding generated is pretty close to what you want.  So only very minor modifications were necessary.
Sub SortA_then_B()
    Cells.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2"), _
        Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom
End Sub
Reminder, if not familiar with installing and using macros see Getting Started with Macros and User Defined Functions

You can assign a shortcut key to the macro, you can assign it to a toolbar button, or to a shape on the worksheet.

Don't put all your macros in one module as it will use up a lot of memory that way.  Try to put macros that are likely to be used together in the same module.  You can rename your modules (F4) so you can tell where you got them i.e. (McRitchie_Sorting), which is of more importance in your personal.xls file with a lot of modules than in your current workbook.

Sorting triggered by an Event Macro (#activate)

Event macros are triggered by an Event and are installed differently from regular macros.  These Worksheet Event macros apply only to the sheet they are installed in.

The sort will be invoked upon sheet activation and on double click by use of Event macros.

As coded below the macro will sort the rows between the top row (header row), and the last row that has something in column A.  The technique to lookup from the bottom is also used on one of my toolbar buttons
    (see my macros for my toolbar buttons)

To install these worksheet event macros. Right click on the sheetname, View code, insert the following:

 1 Who  -B- -C- -D-  Qty -F-
 2  Guy 2 1  30  14   6  39 
 3  Guy 4 44  14  94   8  77 
 4  Guy 1 23  67  58   9  16 
 5  Guy 3 65  56  68   96  64 
 6  Totals  133  167   234  119  196 
Private Sub Worksheet_Activate()
  Dim LRow As Long    '-- SORT on Col E then A
  'Find row before last row in Column A with content
   LRow = Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0).Row
   Rows("2:" & LRow).Sort Key1:=Range("E2"), _
      Order1:=xlAscending, Key2:=Range("A2"), _
      Order2:=xlAscending, Header:=xlNo, _
      OrderCustom:=1, MatchCase:=False, _
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
      Cancel As Boolean)
  Cancel = True   'get out of edit mode from DoubleClick
End Sub

Sorting Formatted cells After Before (#borders)

Borders are left where they are but other cell formatting remains with the cells when sorted.  The initial example on left can be restored by selecting any cell in col 1, Ctrl+A, sort A-Z; the after by selecting any cell in col E, Ctrl+A, sort A-Z.

Sorting messed up cell references on other sheets(#vlookup)

Sorting data will not change cell references, so you might want to consider using VLOOKUP with the “exact match” option so that you can still reference the same data from another sheet to a changed location. 

Sorting messed up, where's that backup (#messedup)

Sorted and can't put file back to how it was.

For next time, if you won't be able to sort file back a few choices include:  1). Create another column that can be sorted, a series of numbers from 1 up created with the fill handles -- incrementing previous cell will do no good.  2). Create a duplicate spreadsheet using  Edit->Copy Sheet and sort it instead.  3). It would be wise to create backups, one form of backups would be to copy using the DOS  XCOPY command; and of course, you should create a real backup and store it at some other location in case of real disaster.

Sorting as if EBCDIC as on a mainframe (#ebcdic)

How to sort with the appearance of mainframe sorting which uses EBCDIC characters.

    Comment: See table at top which describes sorting of characters in Excel, also see my symbols page for an ascii and an historic ebcdic table (with BCD codes).  The following Function is not intended to handle all characters just the alphabet, numbers, equal sign(=), minus sign(-), and forward slash(/).  To use create a column next to source column and use formula such as   =SortBCD(A1)   then sort data on the new column.  Once sorted remove the new column which is nonsensible gibberish used to help sorting and is no longer useful once sorted.  If you make changes to the formula don't forget to hit F9 for recalculation before resorting.

Unlike most other languages I've worked with Excel and VBA do not provide a TRANSLATE instruction.  In COBOL it would be TRANSFORM, others are more obvious like XLAT, XR, XRT.  In Excel the closest thing is a single character substitution using REPLACE or SUBSTITUTE, the SORTBCD macro has to to it's own character by character replacement due to a lack of builtins.


This column appears
in simulated
EBCDIC order
because this
column was
created using
and then sorted
on this column.

Function SortBCD(aaa)
   'David McRitchie  1999-04-07
   '  see sorting in
   'Sort letters before numbers as in BCD and in EBCDIC.
   'FromSTR is comprised of characters to be sorted must in EBCDIC sequence
   'Sortstr any characters but must be in EXCEL sequence
   'Please note EXCEL sequence differs from ASCII
   'Must be at least as long as FromSTR
   L = Len(aaa)
   capsaaa = UCase(aaa)   'bonus: will treat LCase same as UCase
   For i = 1 To Len(capsaaa)
       For j = 1 To Len(FromSTR)
          If Mid(capsaaa, i, 1) = Mid(FromSTR, j, 1) Then
             SortBCD = SortBCD & Mid(SortSTR, j, 1)
             GoTo nextI
          End If
       Next j
   Next i
End Function

Sorting on Street Name, then House Number (#street)

Use a helper column to put house number after the streetname.
=IF(LEN(A14)=0,"",MID(A14,FIND(" ", A14&" ")+1,99)) & RIGHT("00000" & LEFT(A14,FIND(" ",A14&" ")-1),5)
Also see example using REPT Worksheet Function on join.htm page.

Sorting Titles aphabetically with a helper column (#titles)

To remove “The ” as the first word in a helper column to be sorted
   A2:   'The Cat and the Cradle
   B2:   =IF(LEFT(UPPER(A2),4) = "THE ",MID(A2,5,200),A2)

Sorting letter codes non alphabetically (#codeletter)

Use a helper column to assign a digit to the code letter (a=active (1), w=work in progress(2), f=finshed(3)).  The formula below assigns single digit code of 1, 2, or 3 not semi-colon is the list separator.  For an excample of a 2 column array contained in a VLOOKUP formula see gradebook example on VLOOKUP page.

Show a different number

Poster wanted to show 1,2,3 instead of 4,8,12 nothing else is supposed to be present.  David R., whoever that is, answered 2004-05-05.

If you don't want any message if not 4,8,12, try

Some Specialized Sorting (#special)

Sorting a range by Columns

Tom Ogilvy's solution will sort all data for each column separately for columns A through G regardless of whether range is A2:G100, A3:G4

Both examples, use default Header:=xlNo

 Sub sortEachColumn()
 'Tom Ogilvy, 2001-03-24, Programming
 Dim col As Range
 For Each col In Range("a2:g100").Columns
    col.Sort key1:=col, Order1:=xlAscending
 End Sub
Just for comparison and a more literal interpretation:
This variation will  only affect data within the selection
area,  sorting each column individually. 
Sub sortEachColumn2()
Dim col As Range
' --- Range("a2:g100").Select
For Each col In Selection.Columns
   Intersect(col, Selection, _
      ActiveSheet.UsedRange). _
      Sort key1:=col, Order1:=xlAscending
End Sub

Sorting a selection by Rows, with each Row being independent of the other rows

The following is based on Tom Ogilvy's example but has been made a bit more generic to process a Selection instead of a specific Range.  Example shows a single selection area, modified to distinguish rows.

Sub sortEachRow()
 'based on Tom Ogilvy, 2001-03-24, Programming
 Dim rw As Range
 If Selection.Columns.Count = 1 Then
   MsgBox "your selection must involve more" _
     & " than one cell  or column"
   Exit Sub
 End If
 For Each rw In Selection.Rows
    rw.Sort key1:=rw, Order1:=xlAscending, _
      Header:=xlNo, OrderCustom:=1, _
      MatchCase:=False, _
 End Sub
 1 98   76  54  79  39  99 
 2 73   87  54  67  58  87 
 3 10   58  32   48  4  62 
 4 82   37  47   30  10  78 
 5 69   53  3   46  54  60 
 6 24   78  95   49  38  13 
 7 83   37  54   56  5  33 
 8 95   69  88   46  94  81 
 9 49   6  17  29  78  19 
10 3   25  51  40  39  40 
 1 98   76  54  79  39  99 
 2 73   54  58  67  87  87 
 3 10   4  32   48  58  62 
 4 82   10  30   37  47  78 
 5 69   3  46   53  54  60 
 6 24   38  49   78  95  13 
 7 83   5  37   54  56  33 
 8 95   46  69   88  94  81 
 9 49   6  17  29  78  19 
10 3   25  51  40  39  40 

A more generalized version (#normdigits)

In this more generalized function, the numbers will be normalized at 3 digits, which is also the default.  All non digits will be passed through directly.

NormDigits   Example can also be used for TCP/IP addresses (#normdigits)  =personal.xls!NormDigits(A1)  =personal.xls!NormDigits(A2)

ChemNDigits   Example with Chemical Nomenclature (#ChemNDigits)

 CHNO C-001H-001N-001O-001  =personal.xls!ChemNDigits(A11)
 C3H2 C-003H-002 =personal.xls!ChemNDigits(A12)
 C6H12NO2 C-006H-012N-001O-002  =personal.xls!ChemNDigits(A13)
 C10H5F3 C-010H-005F-003  =personal.xls!ChemNDigits(A14)
 C12H6FO2 C-012H-006F-001O-002  =personal.xls!ChemNDigits(A15)
 C12H6F3 C-012H-006F-003  =personal.xls!ChemNDigits(A16)
 Cs4H20 Cs004H-020 =personal.xls!ChemNDigits(A17)

For code and more examples including sorting on chemical nomenclature see Sort TCP/IP page.
To print Cs4H20 as Cs4H20 you can use a subroutine posted by David Hagar, programming, 2002-01-22

For a simpler way of entering subscript and superscripts than provided in Excel see J-Walk SuperSub,

Wide range of answers to misunderstood question (#interesting)

Varied responses to slightly ambiguous problem posted

A non explicit request from Hans Knudsen (Google Usenet Archives 464836683) on 1999-04-10 produced several answers.  What was really wanted was to sort every single cell in the range down one column and continue down the next within range.

I have data in cells A1:E35, and seek the easiest way to sort these data ascending?
Produced several answers including 3 that assumed sort was on a single column.  Most were specific to range, but the range can be easily changed to a nonspecific range.  A problem Hans encountered in trying out solutions was that Outlook includes a char(160) into output that looks like space.

Selection of a current region (Ctrl+ SHIFT+ * ) may occur automatically when a sort is invoked.  Leo's solution is coded to perform in a similar fashion to use the current region when only a single cell was selected.

Problems   (#problems)

Most problems with sorting involve

Related Information - Sorting, Collating Sequence (#related)

Learn more about BookMarklets click on these two links:  highlight links",  and "int/ext links"   to really make links show up.  Click on both links on the left to see the change and differentiation between internal or external links.  Your browser's Reset button (F5) will reload the original unmodified web page. 

Material on backups has been moved to it's own page.

You are one of many distinguished visitors who have visited my site here or in a previous location  since this page was created on October 6, 1998. 

Visit [my Excel home page]   [Index page]   [Excel Onsite Search]   [top of this page]

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved