Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. 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, 2013, 2016, 2019, and Excel in Office 365. 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. ...


Finding the Directory Name

Need to know the directory (folder) in which a workbook was saved? You can create a formula that will return this ...

Discover More

Word 2007 Graphics (Table of Contents)

One way to enhance your documents is with Word's powerful graphics capabilities. Discover how to best utilize graphics ...

Discover More

Getting Rid of Your Windows 7 Password

When you get rid of your Windows login password, you accomplish two things. First, you make it easier to log onto your ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Forcing Excel to Sort Cells as Text

If you have a mixture of numbers and text in a column and you want to sort based upon that column, the results may not be ...

Discover More

Separating Cells Based on Text Color

If the font color used for the data in your worksheet is critical, you may at some time want to move cells that use a ...

Discover More

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

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 2 + 2?

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.