Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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: Incomplete and Corrupt Sorting.

Incomplete and Corrupt Sorting

by Allen Wyatt
(last updated March 30, 2015)

7

The sorting capabilities of Excel are quite handy and quite powerful. The different ways you sort information has been covered in quite a few different issues of ExcelTips. What happens, however, if you try to sort something and Excel omits some columns from the sort and otherwise scrambles your data?

The most common cause for this problem is that Excel isn't recognizing all your data. If you select a single cell in the data table, and then click on either the Sort Ascending or Sort Descending tool, Excel makes its best guess as to what data you want sorted. It may not always make a perfect guess, particularly if there are blank columns, blank rows, or large empty ranges in the data.

One way to see if this is the real problem is to press Ctrl+Shift+* (that's an asterisk). This shortcut selects the "region" around the current cell. Essentially, when you start a sort from a single cell, Excel initiates this command before doing the actual sort. If you press Ctrl+Shift+* first, you can get an idea of exactly which columns and rows Excel will sort.

To make sure there is no confusion in what Excel actually sorts, all you need to do is select the range of columns and rows that you want sorted, and then do the sort.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10320) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Incomplete and Corrupt Sorting.

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

Adding Vertical Lines at the Sides of a Word

Vertical lines are even easier to add around a word than are horizontal lines. There are a variety of methods you can use to ...

Discover More

Footnotes within Footnotes

Need to add footnotes to your footnotes? It's actually allowed by some style guides, but Word doesn't make it so easy.

Discover More

Exporting Latitude and Longitude

A handy way to store latitude and longitude values in Excel is to treat them as regular time values. When it comes around to ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Fixing Odd Sorting Behavior

When you sort data that contains both numbers and text, you may not get exactly the result that you expected. To know what is ...

Discover More

Sorting Text as Numbers

When you are sorting by text values, Excel can be very literal, which may not get you the sorting that you want. This tip ...

Discover More

Preventing Jumbled Sorts

Click the Sort tool in Excel, and you may be surprised that the data in your worksheet is jumbled. In order to sort ...

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}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. 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 three more than 5?

2017-01-19 16:27:05

Matt Goddard

I have a large amount of data, numbers, in one column (over 30,000 items)

The items I want to check for have been colored (cell colored) and so I want to sort the large number of items so that all cells that are colored appear at the very top, otherwise it will take me days to scroll through 30,000 items looking for colored cells.

When I go to use the sort function as soon as I choose sort by color, it hangs up and then I have to shut down or restart excel.

Any ideas on how to solve this issue?


2015-09-21 09:19:10

John Doe

Just using sort to eliminate the blanks cells cause the data to be mis-sorted/aligned. This is shocking behaviour, especially when you are sorting a large amount of data, I would expect the rows to remain aligned!


2015-06-14 06:40:10

Fred Brockman

I think I fixed the problem but not sure how applicable the solution is to anyone else. Within the formula
VLOOKUP(Location!D269,'Cat List'!$B$2:$B$75,1,FALSE)
Location! is unnecessary because the formula resides in the Location sheet (this is an artifact from when the formula was in another sheet). Removing Location! allows the formulas to sort normally. No clue why this happened but glad to get it fixed.


2015-06-14 05:34:12

Fred Brockman

Dennis, I came to here with the same problem as you - cell addresses in formulas get scrambled.

=IF(D269="z","(empty)",IF(ISNA(VLOOKUP(Location!D269,'Cat List'!$B$2:$B$75,1,FALSE)),"MISMATCH","match"))

I'm doing data integrity checks here. In the formula the first D269 is correct as the formula is in row 269. However the second D269 gets munged to something like D141.

It looks like in both cases the first argument to vlookup() is going south. I've never had Excel mess up like this - it's pretty disconcerting.


2015-04-18 13:58:05

Barry Kruse

So relieved to hear someone else mention the data scrambling issue. I too have never been able to determine what set of factors causes Excel to get confused. One would think Microsoft would address this in their documentation somewhere. One would think Microsoft would have fixed this. Barring that, I hope someone in this group has a definitive answer.


2015-04-18 01:33:46

Dennis Costello

I run into a different problem - and have for years across multiple versions of Excel. Imagine that column A has a set of values, and column B has formulae such as
=VLOOKUP(A2, 'Other Tab'!Q:Z,2,FALSE)

this formula would be in B2 - B3's formula would refer to A3, B4's to A4, etc. Now sort columns A and B by column B. Often everything is fine - the values in column A are in their right places and the formulae in column B are still pointing to them properly. But sometimes - and often enough that I have to check - the formulae in column B are scrambled. B2's formula points to A17 while the one in B3 points to A2 or A5 or some other seemingly random place. I think in reality they're pointing to the cell where the "A" value was stored before the sort, but I've never done an exhaustive test of this. I just fix it (manually reset the formula in B2 to point to cell A2), fill down, and get on with my day.

But if someone else has seen this - and more importantly if you know how to prevent it - I'd like to hear it.


2015-01-26 10:15:50

Schlatter

You might also make all your data a 'defined name' table. I initially did this to a 'working data set' so that it would automatically include the rows being added in the various lookups. it seems to have the added benefit of included all the data when it resorted after new rows have been added ...


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.