Getting Wrong Results from a Simple Sort

Reinhold has a worksheet that he uses for appointment times (column A) and names (column B). When he sorts by time (primary) and name (secondary), he's unable to get the correctly ordered results. He wonders what could be causing the issue.

This sort of problem can be difficult to diagnose without actually seeing the data that is being sorted. In general, though, there are only a few things that could be affecting the sort.

Unknown Settings

When you sort information in Excel and don't get the results you expect, think back to whether you've done previous sorts in your current session with the program. If so, and if at least one of those earlier attempts was correct, then the problem could be related to some of the sort settings being incorrect for your current sort.

You see, when you sort something, Excel remembers any settings you make. When you later sort (in the same session), Excel may use the earlier settings if you don't explicitly make changes in those settings for the new sort. The bottom line is that you need to make sure you check all the settings in the Sort and Sort Options dialog boxes to make sure the settings are consistent with how you want to perform the current sort.

Data Range

If you select a cell within the range of cells you want to sort, when you actually perform the sort, Excel expands the range automatically to include everything it thinks should be included in the sort. This means that Excel expands the range in all four directions until it reaches an empty column (left or right) or an empty row (up or down).

This means that if your data includes blank rows or columns, you won't get the correct results from the sort. The easiest way to ensure that all your data is sorted is to manually select the data range before starting the sort. The next best thing is to press Ctrl+Shift+8 (also written as Ctrl+*). Excel expands the range from the current single cell, allowing you to ensure that all your rows and columns are included in what Excel will sort.

Another data-range issue is presented by merged cells. If you have merged cells in the data range you are trying to sort, then all bets are off. Excel can do some pretty funky things when trying to work with merged cells. The best bet? Remove all merged cells from any data that you want to sort.

Data Type

When sorting your data, Excel takes into account whether the data is stored internally as a number, a date/time, or as text. The biggest data-type-related sorting errors occur when the user thinks the data is stored as a number or as date/time, but it is actually text. When it comes to dates and times, as in Reinhold's situation, the information in this ExcelTip can be helpful:

https://tips.net/T5678

The bottom line is that you need to ensure that all the data in a column is of the same type. This can take some sleuthing to determine and some work to correct.

Data Inconsistency

One rather human shortcoming in relation to Excel is the fact that we look at the data in a worksheet and assume that what we see is what is actually stored in the worksheet. For instance, we may see the following in a cell:

10:15

This looks like a simple time, but it is not. How Excel displays information and how it stores that information internally is two different things. You can see this if you select the cell and look at the Formula bar. Chances are good that you'll see this there:

10:15:00 AM

That's just the tip of the iceberg, so to speak. A time displayed in a worksheet is actually stored internally as a date/time. You can see this if you select the cell and format it using a custom format of "m/d/yyyy h:mm". What you end up with is the following displayed in the cell:

1/0/1900 10:15

In other words, Excel stores a date (albeit invalid in this case) with the time. It is this date/time that Excel uses with sorting, even if the date is not displayed in the worksheet. If the times in a column don't all share the same date, then they will sort in a way that may be confusing. This potential problem is compounded if the worksheet uses times entered on a Mac version of Excel where a different date/time base year (1904) may be used than on a PC (1900).

The only solution to this issue is to ensure that all the times in a column share the same date, even if the date isn't displayed. A quick formula to do this is the following, assuming a time is displayed in cell A1:

=A1-INT(A1)

This subtracts, from the value in A1, anything to the left of the decimal point. This may sound confusing when dealing with date/time values, until you remember that anything to the left of a decimal point represents a date and anything to the right represents a time. Making sure the value to the left of a decimal point is 0, as the above formula does, ensures that all date/time values are referring to times on the same date. They will, therefore, sort properly.

There is one other potential for data inconstancy when it comes to date/times. That is confusion between AM and PM dates. If you are displaying times without an AM/PM indicator, it is possible that sorted times will look wrong when, in fact, they are correct because Excel always takes into account whether the time is AM or PM. The easiest way to check if this is a potential problem is to either format times to show in a 24-hour format—i.e., using 13:15 for 1:15 PM—or make sure that the format includes the AM/PM indicator.

Extraneous Characters

Typically, extraneous characters are only a problem if you are sorting text values. In Reinhold's case, this could definitely come into play in column B, which includes names. If there are leading spaces in a name, they won't show up in the display, but they will affect the sort. There is a similar potential of mis-sorting if there are multiple spaces within the text, such as two spaces between a first and last name.

The simplest solution to this potential issue is to use the TRIM function. For instance, if the name is in cell B1, then the following will remove spaces:

=TRIM(B1)

The TRIM function removes any leading spaces, any trailing spaces, and any instances of multiple spaces.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13385) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.