Excel can't handle big data

[Eric Hernlund]

We are trying to organize a project with ten sheets on one file and fourteen on another and Excel won't let me do something as simple as copying and pasting columns from one file to another. Maybe they shouldn't let me add more sheets if the software is inept at handling that much data. All add-ons turned off, completely updated, extremely fast computer with tons of space and memory makes ZERO difference. Crashes every time. It's not like we have 10,000 rows of biologic data...half of it is qualitative text but there is no reason Exccel shouldn't carry this data. Why? About to ask for a refund on 365 as it is wasting my time and making me late for deadlines.

[#2]

Sorry to hear about your bad experience. I don't think it should make much of a difference, but have you tried using power pivot? Power Pivot allows you to use more rows than the maximum allowed in Excel, it may be worth giving it a try in this case.

[#3]

Hi Eric,

Dozen of sheets and few thousands of rows is not big data for Excel, if only you don't have few megs of text in each cell. Most probably the problem is not with clipboard buffer size, something else which could be found only with real data in actual environment. That could be related to Windows search index (try to rebuild it), temporary files (remove all), hardware acceleration (on laptops, switch off), network shared files, whatever.

First questions here are do you have same effect on all available computers or you didn't try; is the same effect for safe mode (Win+R=>excel /safe) or not; how your resources are consuming during copy/paste based on at least Task manager or better Resource monitor,...

[#4]

Hi Zachary,

I didn't catch the idea, how Power Pivot could help with copy/paste from one worksheet to another?

[#5]

My suggestion was referring to the fact that PowerPivot can handle up to 1,999,999,997 rows, as opposed to Excel's limit of 1,048,576. This can be seen here: https://msdn.microsoft.com/en-us/library/gg413465(v=sql.110).aspx

I don't think that Powerpivot was the solution to copy and paste, but was giving him a second option to see if it might work better. In my experience, Powerpivot is leaner than Excel and can thus work faster. Since his computer was working very slowly, I though it may be worth giving it a try.

[#6]

Yes, but Power Pivot is analysis only tool which is not instead of Excel sheet, it's on the top of it. As for the performance it very depends on how the data model is built.

[#7]

Thanks for clarifying