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)


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. ...


Printing Summary Information

Word automatically maintains a number of properties for each document you create. As part of those properties you can ...

Discover More

Turning Off Window Drop-Shadows

Windows adds its own bells and whistles to what you see on your desktop. One of those flourishes is a drop-shadow added ...

Discover More

Jumping To a Specific Page

Want to jump to a specific printed page within a worksheet? It's not as easy as you might think, but here's some ...

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)

Performing Complex Sorts

One way you can easily work with data in a worksheet is to sort it into whatever order you find most helpful. Excel ...

Discover More

Sorting by the Last Digits in Variable Length Data

Excel is great at sorting information in a worksheet. Sometimes it can be a bit confusing as to how to set up the sort ...

Discover More

Incorrect Links after Sorting Hyperlinks

When you sort your data you should always check to see if the sort was done correctly. What if sorting messes up ...

Discover More

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

View most recent newsletter.


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 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 1 + 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


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

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.