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

Moving a Table Row

Want to move a row in a table very easily? You can do so by using the same editing techniques you are already using.

Discover More

Determining If a Value is Out of Limits

Need to figure out if a value is outside of some arbitrary limit related to a different value? There are a number of ways you ...

Discover More

Applying Formatting in Lists

If you want to change the formatting applied to numbers or bullets in your lists, you'll appreciate the information in this ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Too Many Formats when Sorting

Sorting is one of the basic operations done in a worksheet. If your sorting won't work and you instead get an error message, ...

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

Discover More

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
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 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 6 - 0?

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.