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 May 27, 2021)


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


Creating a Table of Contents from Heading Levels

If your document is any length at all, adding a table of contents is a nice touch. This tip demonstrates how easy it is ...

Discover More

Understanding the Normalize Text Command

Word includes tons of internal commands that you can access as you customize your system. One of these is the Normalize ...

Discover More

Changes in Font Size when Copying

Have you ever copied information from one worksheet to another, only to have the information you paste not look the way ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Can't Sort Imported Data

Import information from an external database, and you'll no doubt want to use Excel's simple tools to manipulate that ...

Discover More

Sorting for a Walking Tour

Want to sort addresses by even and odd numbers? By using a formula and doing a little sorting, Excel can return the ...

Discover More

Controlling Sorting Order

When you sort information in a worksheet, you have control over the order in which that information is sorted. Here's a ...

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 8 - 8?

2021-05-30 06:27:33


I stay away from Tables in Excel because it prevents specific functionalities that I find much more useful (such as "Custom Views" ... don't work if data are converted to Tables).

A simple way to make sure your sorting is always consistent, is making sure that all columns you want to sort are actually set up in the same "Auto-filter" ... that way you can easily do sorting just by using the filter arrow contextual menus, and there's no need to do any other fancy thing.

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.