Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Fixing Odd Sorting Behavior

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Fixing Odd Sorting Behavior.

Michael runs a karaoke company and uses Excel to create his song books. The worksheet contains three columns for song number, song title, and artist. Michael runs into odd behavior when sorting the song book by either artist or title.

For instance, when he sorts by artist the group 311 will come up in two different spots—four of their songs are placed right after the band 112 and before 702, and then it sorts the rest right after 3 of Hearts and before 38 Special. Then, when sorting by song, George Strait's song "True" always ends up as the last song in the list.

This obviously isn't want Michael wants to see happen when sorting. The reason it is happening, however, is due to the way that Excel interprets the information in each cell. When you enter information in a cell, Excel tries to parse that information and determine if it is a number, a date, or text. It just so happens that Excel is "guessing wrong" when it comes to some group and song titles.

When you enter the group 311, Excel considers that a number, so it treats it as a number. Similarly, when you enter the song title "True," Excel considers that a Boolean value—a number. (It would do the same thing if you had a song named "False.")

When performing a sort, Excel first sorts by the data type and then within the data type. 112 and 702 are numbers. 3 of Hearts and 38 Special are text because they don't consist of only digits. When sorting by artist, the group 311 shows up in two different places because the group name was parsed by Excel in some instances as a number and in other instances as text.

To understand how to correct the odd behavior, it is important to understand that the behavior isn't really odd; it is the logic Excel uses. If you want different results, you have to work with your data to make sure it is not parsed incorrectly by Excel.

First, if you sort in ascending order, the values in your cells will be sorted in these data types:

  • Numbers in increasing value (1, 2, 3, etc.)
  • Text in alphabetic order (a, b, c, etc.). If the text begins with a number (as in 38 Special), then the 3, as text, appears before the ABCs.
  • Logical values (False, True)
  • Error values (#DIV/0!, #N/A, etc.)
  • Blanks

If you sort in descending order, then the order is the reverse of what is shown here, except that blanks still appear as the last data type sorted.

You can better see the data types that Excel assigns to various cells by removing any explicit alignment in the cells. By default the text values are left-justified, numbers right-justified, and Boolean and error values centered.

To get things to sort the way you want, you just need to make sure that all the cells in a column contain the same type of data. In the case of both artist and song title, this would be text. In the cells being sorted as numbers (like 311), edit the cell to place an apostrophe before the first digit in the number. This tells Excel you want the cell's value treated as text. You can also do the same thing with "True."

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3362) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Fixing Odd Sorting Behavior.

Related Tips:

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!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Willy Vanhaelen    10 Nov 2015, 06:16
@Mikail

You will probably find inspiration in this tip (Excel pre 2007):

http://excel.tips.net/T009005_Automatically_Sorting_as_You_Enter_Information.html

Or for Excel >= 2007:
http://excelribbon.tips.net/T009006_Automatically_Sorting_as_You_Enter_Information.html

Especially look in the 'Comments for this tip'.
Mikail     09 Nov 2015, 22:27
I have been looking forever to find out if it is possible to have rows of your data sorted automatically based on the time displayed in one of the columns. The time is entered through a Userform and once added to the worksheet using a command button I need it to automatically sort all rows starting with the earliest time in the column and ending with the latest time in the column... I think it's impossible :(
Helen    09 Nov 2015, 06:08
We are updating our Karaoke books - A=Titles, B=Artists, C=# codes. When we sort on Artists, we want all of that artists songs in alpha order also, and they USUALLY are but not all the time. Do you know how to fix this? Thank you!
Joe    02 Oct 2015, 17:30
I get some bizarre errors with excel 2013 every once in a while. The alphabetize will not correctly alphabetize a cell. Turns out all I had to do was hard save the file. For the file to update correctly and close/exit and re-open. I guess Microsoft 10 has some kind of weird buffering system? That didn't correctly update entries for microsoft excel 2013 in real time. I don't know if I'm missing a patch. But I think I'm all up to date.
*shrug I don't know.
Jeffery C. Niemuth    04 Dec 2014, 04:43
I still have something odd going on with my data with Excel 2013 under Windows 7 Ultimate.

I have a 225 row collection of DVR episode data. If I do a reverse sort on DVR_Date, then DVR_Time and then Episode_Name, everything is as expected EXCEPT two sets of 2 rows are switched. I can get the correct order by fudging the DVR_Time values by say 1 minute.

The 3rd tier "tie breaker" in the sort specification appears not in play: The result is (top to bottom): MOP_4_<xxx> [correct], then MOP_2_<yyy>, MAM_3_<zzz>, MOP_1_<www>, MAM_2_<vvv> [correct], the <...> are episode titles after the A/N prefix codes which are obviously not being considered.

This problem is repeatable and reversible with the DVR_Time kludge mentioned above. All of the DVR_Date, DVR_Time, title fields, etc were manually entered.

The problem persists even when changing MOP_5 thru MOP_1 to be alpha instead of numeric, e.g., MOP_e to MOP_a. So I feel pretty sure the problem is in how the DVR_Time field is being handled, specifically values of 3:00 PM and 2:00 PM. I noticed one other anomaly in this sorted list where two consecutive records with identical DVR_Date values and DVR_Time = 3:00 PM also reversed. My sample is small so I have no other occurrences with these times.

Ah, I made another duplicate 3:00 PM pair and the sort anomaly appeared there too! It is DVR_Time = 3:00 PM that is the culprit, somehow. Probably also 2:00 PM!

Any ideas?
Alison    02 Nov 2014, 10:52
Thank you!! I could not figure out why code "MAR" was sorting to the top of the list, ahead of "ARE". Apparently it was thinking MAR is a date(March). Though the apostrophe doesn't solve it, at least I know why! I will just use another code.
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.