===================================== **EDIT: The sorting problem described below isnow resolved with the latest Office update from Microsoft. See here for instructions on how to update Office to Service Pack 1:** http://www.thewindowsclub.com/update-office-2013-manually ====================================== All these problems are occurring on Excel 2013 on a Lenovo ThinkPad W540 with 16GB of RAM running Windows 8.1 Pro (Intel i7-4800Q CPU). Note: I do not have these issues working with the exact same large file on a Lenovo ThinkPad T400 running Windows 7 and Excel 2010, although it is (obviously) much slower. The problems seem to be related to either Windows 8.1 Pro or Excel 2013 ( UPDATE: It seems like Excel 2013 is, in fact, the culprit ). Seems to me that having a more powerful box + updated OS and Office should make it easier to work with large files, as opposed to the other way around. Does anyone have a fix for the following issues? Problem #1: Large XLS FIles Open As Blank When Other XLS Files Are Also Open in Excel 2013 on Windows 8.1 I'm experiencing an issue where very large XLS files open as blank if you try and open them after opening a smaller file. Large files open just fine after opening another smaller one. To reproduce: Open a small file (I'm opening one that is 70KB), then; Open 300MB+ xls file (the one I am opening is 316MB), then; See the following blank screen. All menus do not display and the content also does not display. Note that once I close both files and then try again to open the large file alone, it opens just fine and displays fine (although then experiences problem #2 below). The issue seems to be with having a smaller file open at the same time as the large one. Problem #2: Deleted Columns Continue to Display After Deletion in Large XLS Files in Excel 2013 on Windows 8.1 To reproduce: Delete one column in a xls file with approximately 700,000 rows (again this is a large 300MB+ file), Excel still displays the deleted column after deletion. (although you cannot interact with it -- it's not actually there...it just displays as there) However, if you then save the file, close it, and then open the file back up again, you see that the deleted column is, in fact gone. Is this a display bug? Problem #3: Sorting Large Numbers of Rows Does Not Work in Large XLS Files in Excel 2013 on Windows 8.1. Excel cannot sort large numbers of rows. To reproduce: Open a file containing 700,000 rows with three columns of data Sort by Column A on Values in A to Z order (or any order/values/column combination) Click OK See that the sorting never occurred, and there was no error message Note: If you then select a subset of the 700,000 rows (experimentation has shown me that you must select fewer than 5,000 rows), then sorting will happen successfully.

This is indeed an SP1 bug. It appears the problem only happens on machines with 8 or more processors.

The details Rich provided make it clear this this problem lies in Excel 2013, not Windows. Excel team: Please provide an update on your progress on this issue.

Hi I am experiencing the same issues when sorting large numbers of rows. However my issues start around 180,000 rows. My setup: Dell Precision T5600 with two Intel Xeon ES-2667 processors (total of 24 cores) and 16GB RAM Windows 7 Professional 64-Bit with SP1 and all MS recommended updates. Windows 7 Version 6.1.7601 Service Pack 1 Build 7601 Microsoft Office 2013 64-Bit installed from DVD Microsoft Excel 2013 (15.04615.1000) MSO (15.0.4615.1000) 64-Bit To replicate: Run MS Excel and create a new blank spreadsheet Use RAND() function to fill cells A1 through A200000 with random numbers Copy range A1:A200000 and then Paste-Special Values. So now we have 200,000 random numbers Highlight range A1:A200000. Click on Data ribbon bar. Then click on Sort button Select "ColumnA" Sort by Values and "Smallest to Largest" Click OK See hour glass for a very brief moment but the data is not sorted. Can sort smaller subsets however. Highlight range A1:A175000 Click on Data ribbon bar. Then click on Sort button Select "ColumnA" Sort by Values and "Smallest to Largest" Click OK - Now the first 175,000 rows are sorted fine If I try range A1:A180000 then it fails. Performing the very same set of steps on my laptop with 32-Bit Windows 7 and 32-Bit MS Excel works just fine up to a million rows (as far as I tested) In another thread, someone pointed to one possibility of the number of CPU cores available on the PC. Under File -> Options and then Advanced tab there is a section called "Formulas" that has a check box for "Enable Multi-threaded calculation". By default it seems to have "Use all processors on this computer: 24" on my system. If I change this to "Manual" selection and choose "1", I still get the same problems with sorting. Disabling Multi-threaded calculation has no effect on the problem either. This is a debilitating bug that needs to be addressed very very quickly. I can not imagine how this escaped even the most basic of product testing. p.s. while searching for solutions to my issues, I came across the press release that the "new" Excel will be able to "work with billions of rows". Please fix Excel so it can sort "thousands of rows" first. Thanks ETA: It might be nice if folks who are having this problem report on how many CPU cores they see in the setting "Use all processors on this computer: XX" as I suggested above. What I am thinking is that the bug may have to do with how Excel is trying to split up the work among multiple CPU cores. The hypothesis being: Fewer cores = fewer problems = slightly larger data sets can be sorted.

Resolved: Very Large XLS File Problems in Excel 2013 - Excel Cannot Successfully Sort Files With Large Numbers of Rows

Anonymous

=====================================

**EDIT: The sorting problem described below isnow resolved with the latest Office update from Microsoft. See here for instructions on how to update Office to Service Pack 1:**http://www.thewindowsclub.com/update-office-2013-manually

======================================

All these problems are occurring on Excel 2013 on a Lenovo ThinkPad W540 with 16GB of RAM running Windows 8.1 Pro(Intel i7-4800Q CPU). Note: I do not have these issues working with the exact same large file on a Lenovo ThinkPad T400 running Windows 7 and Excel 2010, although it is (obviously) much slower. The problems seem to be related to either Windows 8.1 Pro or Excel 2013 (UPDATE: It seems like Excel 2013 is, in fact, the culprit). Seems to me that having a more powerful box + updated OS and Office should make it easier to work with large files, as opposed to the other way around.

Does anyone have a fix for the following issues?

Problem #1: Large XLS FIles Open As Blank When Other XLS Files Are Also Open in Excel 2013 on Windows 8.1

I'm experiencing an issue where very large XLS files open as blank if you try and open them after opening a smaller file. Large files open just fine after opening another smaller one.

To reproduce:

Open a small file (I'm opening one that is 70KB), then;
Open 300MB+ xls file (the one I am opening is 316MB), then;
See the following blank screen. All menus do not display and the content also does not display.

Note that once I close both files and then try again to open the large file alone, it opens just fine and displays fine (although then experiences problem #2 below). The issue seems to be with having a smaller file open at the same time as the large one.

Problem #2: Deleted Columns Continue to Display After Deletion in Large XLS Files in Excel 2013 on Windows 8.1

To reproduce:

Delete one column in a xls file with approximately 700,000 rows (again this is a large 300MB+ file),
Excel still displays the deleted column after deletion. (although you cannot interact with it -- it's not actually there...it just displays as there)
However, if you then save the file, close it, and then open the file back up again, you see that the deleted column is, in fact gone.

Is this a display bug?

Problem #3: Sorting Large Numbers of Rows Does Not Workin Large XLS Files in Excel 2013 on Windows 8.1. Excel cannot sort large numbers of rows.

To reproduce:

Open a file containing 700,000 rows with three columns of data
Sort by Column A on Values in A to Z order (or any order/values/column combination)
Click OK
See that the sorting never occurred, and there was no error message

Note: If you then select a subset of the 700,000 rows (experimentation has shown me that you must select fewer than 5,000 rows), then sorting will happen successfully.

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

Raju,

BINGO! I found the solution went to File- Account - Office Updates & appended.

There must have been a patch undocumented but I can sort a million rows.

Mukund.

0 comments

40 additional answers

Anonymous

2014-06-17T13:20:46+00:00

Still no updates?
Was this answer helpful?

0 comments No comments
Anonymous

2014-06-04T15:44:26+00:00

This is indeed an SP1 bug. It appears the problem only happens on machines with 8 or more processors.
Was this answer helpful?

0 comments No comments
Anonymous

2014-06-04T13:29:29+00:00

The details Rich provided make it clear this this problem lies in Excel 2013, not Windows.

Excel team: Please provide an update on your progress on this issue.
Was this answer helpful?

0 comments No comments
Anonymous

2014-06-04T12:03:22+00:00
Hi

I am experiencing the same issues when sorting large numbers of rows. However my issues start around 180,000 rows. My setup:

Dell Precision T5600 with two Intel Xeon ES-2667 processors (total of 24 cores) and 16GB RAM

Windows 7 Professional 64-Bit with SP1 and all MS recommended updates.

Windows 7 Version 6.1.7601 Service Pack 1 Build 7601

Microsoft Office 2013 64-Bit installed from DVD

Microsoft Excel 2013 (15.04615.1000) MSO (15.0.4615.1000) 64-Bit

To replicate:
1. Run MS Excel and create a new blank spreadsheet
2. Use RAND() function to fill cells A1 through A200000 with random numbers
3. Copy range A1:A200000 and then Paste-Special Values. So now we have 200,000 random numbers
4. Highlight range A1:A200000.
5. Click on Data ribbon bar. Then click on Sort button
6. Select "ColumnA" Sort by Values and "Smallest to Largest"
7. Click OK
8. See hour glass for a very brief moment but the data is not sorted.
Can sort smaller subsets however.
1. Highlight range A1:A175000
2. Click on Data ribbon bar. Then click on Sort button
3. Select "ColumnA" Sort by Values and "Smallest to Largest"
4. Click OK - Now the first 175,000 rows are sorted fine
If I try range A1:A180000 then it fails.

Performing the very same set of steps on my laptop with 32-Bit Windows 7 and 32-Bit MS Excel works just fine up to a million rows (as far as I tested)

In another thread, someone pointed to one possibility of the number of CPU cores available on the PC. Under File -> Options and then Advanced tab there is a section called "Formulas" that has a check box for "Enable Multi-threaded calculation". By default it seems to have "Use all processors on this computer: 24" on my system. If I change this to "Manual" selection and choose "1", I still get the same problems with sorting.

Disabling Multi-threaded calculation has no effect on the problem either.

This is a debilitating bug that needs to be addressed very very quickly. I can not imagine how this escaped even the most basic of product testing.

p.s. while searching for solutions to my issues, I came across the press release that the "new" Excel will be able to "work with billions of rows". Please fix Excel so it can sort "thousands of rows" first.

Thanks

ETA: It might be nice if folks who are having this problem report on how many CPU cores they see in the setting "Use all processors on this computer: XX" as I suggested above. What I am thinking is that the bug may have to do with how Excel is trying to split up the work among multiple CPU cores. The hypothesis being: Fewer cores = fewer problems = slightly larger data sets can be sorted.
Was this answer helpful?

0 comments No comments