Share via

Excel Sort Limit

Anonymous
2017-09-27T17:24:38+00:00

I have a spreadsheet with 100 rows of data.  The sort was not working, so after much trial and error, I discovered that I can sort up to 48 rows.  When I go to 49 it stops sorting.  Because of header rows, I am trying to sort rows 3 to 102.  Rows 3 through 51 will sort.  When I add row 52 it will no longer sort.  My Excel version before 365 appeared to work fine.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2017-09-28T18:32:26+00:00

You are quite welcome, glad I could help.

As for the old workbook, I wouldn't bother as long as you have what you need.  Files can become corrupt (well, not corrupt in the traditional sense, but odd rules start to apply to older files that don't make sense or apply to new files), and you never know what is attached to a workbook that you can't see.  The "copy all sheets into new workbook," trick is one I use from time to time just to deal with an excel file that is unusually large or slow compared to how it should be.  I don't know exactly what is happening in that situation, I just know the fix ;)

If there was some formula that was broken by doing that trick or you lost some data then it might be worth going back, but...

Was this answer helpful?

2 people found this answer helpful.
0 comments

8 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-09-27T19:52:22+00:00

    I am not sure, but I suspect this is a memory issue.  Microsoft likes to add bells and whistles for the Marketing types not realizing that Excel is used by the Accounting types and we hate the bells and whistles, especially when they stop us from doing our jobs.  They also assume that each time they put out a new product your computer power X10.

    To find out if this is the case, we can look to see if you are doing other things using a lot of memory.  Common ones in my experience are Vlookups over a large range, conditional formatting over a large range, or a random cell like XXA876583 having a space instead of being blank, which means that not only does Excel store the space in cell XXA876583, but it stores in memory that every other cell above and left of that cell is blank (well, and it stores the data you care about too).

    The third is the easiest to fix and won't be a bother for you, just go to the lower right corner of your data and see if your scroll bars are at the bottom left.  If they are not, scroll left to the edge of the scroll bar, then delete all columns between where you now are and your data.  do the same thing with rows from the bottom of the scroll bar to the bottom of your data.

    For conditional formatting, well...ouch...turn it off.

    For vlookups or other complex formulas, well...ouch, do a copy-paste special-values and replace your formulas with numbers.

    If that still doesn't solve your problem maybe post the file?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2017-09-27T18:16:40+00:00

    The built in sort in the filter didn't work either.  It would not expand to try to sort all the columns.  I had to use the sort in the data tab  at the top of the page.  Then Excel crashed.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2017-09-27T18:06:34+00:00

    When I apply a filter and try to sort 49 rows Excel crashes and restarts.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2017-09-27T17:32:13+00:00

    Huh, what happens if you apply a filter to your data and then use the built in sort in the filter?

    Was this answer helpful?

    0 comments