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

Raju, I changed the recommended settings & have a single coloumn of 994,148 unformatted data it didn't help. BTW - I can do it on my older 2010 excel running on Windows 7 Professional. I can't on windows 8.1 64 bit HP laptop processor with installed RAM of 8 Gigs. I believe this is a solvable situation please help. With regards, Mukund.

Thanks for reporting a solution! Let's hope that making sure their copy of Office/Excel is up to date on patches at this time helps the others that had this problem. It would seem that perhaps sometimes Microsoft does peek in to see what's going on in these forums.

I just discovered a far more insidious bug, one that has screwed up some major analysis I am doing in a court case. I have Office 360 running (with all the latest updates) on a new laptop (Toshiba Satellite S70-B), which itself is running Win7Pro 64 SP1. Processor is i7-4710HQ (2.50GHz); 16 GB of RAM. I have a spreadsheet of just over 500,000 rows -- no formulas, just text and numbers. I sorted it on columns A and B (last name, first name). I saved it out as CSV, since I needed to do programmatic parsing of the data. I ran into problems doing so -- and discovered that while the data as presented in Excel looked sorted -- though I had occasional strange blank lines -- the actual CSV file was not well-sorted at all. Here's a text example straight from a "sorted" Excel CSV file: CEPARANO,BRUCE,149 DARNELL LN,STATEN ISLAND,NY CEO FWABAVQSJXW-UABB 8009980244 CENZANO,HEATHER,(212)924-6618,(917)749-2563 CENZANO,LAUREN,(631)462-1457,(631)374-4481 KAUFMAN,FENIGSTEIN, CENTRONE,ANTHONY,420 E 79TH ST APT 5B,NEW YORK,NY CENTRONE,FRANK,763 CORRELL AVE,STATEN ISLAND,NY CENTRO,ROBERT,******@HOTMAIL.COM,PO BOX 333,SODUS POINT CENTRILLS,VIRGINIA,16037 13TH AVE,WHITESTONE,NY CENTREVILLE,CENTREVILLE,22182,NULL CENTREVILLE,ASD;FJKASDF;JK,VIRGINIA,20120,703 000 0000 JHGJF,XHXHFGJHX, OF,GASHO,JAPAN,OFFICE, CENTRAL PARK CTJSH1NVUYESIQBP 8009980244 CENTRAL PARK FWABAVNVUS1XNQAP 8009980244 CENTRAL PARK FWABAVNRASSEMWBI 8009980244 CENTRAL FALLS,DALOMBA,RHODE ISLAND,2863,NULL CENTRAL FALLS,DALOMBA,RHODE ISLAND,2863,NULL BOX,PO,345, CENTRA,RICHARD,1446 78TH ST,BROOKLYN,NY What's more, Excel doesn't show this -- it doesn't present any data that is actually out of order. I only found this out because of programmatically processing the file and suddenly running into out-of-order data. I have likely lost several days' work (billable hours, which I'll have to forfeit) due to this major, nasty bug. As per the comments here, I'm going to install Office 2010 on another machine and see if it works correctly, But this is, frankly, terrifying in terms of data accuracy and integrity.

What is the size of the Excel file which contains so much of data in it? Sorting should work as Excel is one among the great application which helps you to work with large files.

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-07-25T23:55:19+00:00

Raju,

I changed the recommended settings & have a single coloumn of 994,148 unformatted data it didn't help.

BTW - I can do it on my older 2010 excel running on Windows 7 Professional.

I can't on windows 8.1 64 bit HP laptop processor with installed RAM of 8 Gigs.

I believe this is a solvable situation please help.

With regards,

Mukund.
Was this answer helpful?

0 comments No comments
Anonymous

2014-07-26T08:05:42+00:00

Thanks for reporting a solution! Let's hope that making sure their copy of Office/Excel is up to date on patches at this time helps the others that had this problem. It would seem that perhaps sometimes Microsoft does peek in to see what's going on in these forums.
Was this answer helpful?

0 comments No comments
Anonymous

2015-01-12T05:23:32+00:00

I just discovered a far more insidious bug, one that has screwed up some major analysis I am doing in a court case. I have Office 360 running (with all the latest updates) on a new laptop (Toshiba Satellite S70-B), which itself is running Win7Pro 64 SP1. Processor is i7-4710HQ (2.50GHz); 16 GB of RAM.

I have a spreadsheet of just over 500,000 rows -- no formulas, just text and numbers. I sorted it on columns A and B (last name, first name). I saved it out as CSV, since I needed to do programmatic parsing of the data. I ran into problems doing so -- and discovered that while the data as presented in Excel looked sorted -- though I had occasional strange blank lines -- the actual CSV file was not well-sorted at all. Here's a text example straight from a "sorted" Excel CSV file:

CEPARANO,BRUCE,149 DARNELL LN,STATEN ISLAND,NY

CEO FWABAVQSJXW-UABB 8009980244

CENZANO,HEATHER,(212)924-6618,(917)749-2563

CENZANO,LAUREN,(631)462-1457,(631)374-4481

KAUFMAN,FENIGSTEIN,

CENTRONE,ANTHONY,420 E 79TH ST APT 5B,NEW YORK,NY

CENTRONE,FRANK,763 CORRELL AVE,STATEN ISLAND,NY

CENTRO,ROBERT,******@HOTMAIL.COM,PO BOX 333,SODUS POINT

CENTRILLS,VIRGINIA,16037 13TH AVE,WHITESTONE,NY

CENTREVILLE,CENTREVILLE,22182,NULL

CENTREVILLE,ASD;FJKASDF;JK,VIRGINIA,20120,703 000 0000

JHGJF,XHXHFGJHX,

OF,GASHO,JAPAN,OFFICE,

CENTRAL PARK CTJSH1NVUYESIQBP 8009980244

CENTRAL PARK FWABAVNVUS1XNQAP 8009980244

CENTRAL PARK FWABAVNRASSEMWBI 8009980244

CENTRAL FALLS,DALOMBA,RHODE ISLAND,2863,NULL

CENTRAL FALLS,DALOMBA,RHODE ISLAND,2863,NULL

BOX,PO,345,

CENTRA,RICHARD,1446 78TH ST,BROOKLYN,NY

What's more, Excel doesn't show this -- it doesn't present any data that is actually out of order. I only found this out because of programmatically processing the file and suddenly running into out-of-order data. I have likely lost several days' work (billable hours, which I'll have to forfeit) due to this major, nasty bug. As per the comments here, I'm going to install Office 2010 on another machine and see if it works correctly, But this is, frankly, terrifying in terms of data accuracy and integrity.
Was this answer helpful?

0 comments No comments
Anonymous

2015-01-26T16:23:31+00:00

What is the size of the Excel file which contains so much of data in it?

Sorting should work as Excel is one among the great application which helps you to work with large files.
Was this answer helpful?

0 comments No comments