How much can Excel do? I hit a limit on a project over the weekend.
I’m not sure what imposed the limit. But, I know I was able to copy 800K cells without much trouble, but that copying around 1,700K cells … didn’t work.*
Also note that the previous post about how to clear your Windows Clipboard is essential. If you do one huge copy, you’ll probably need to do this before doing another one, or even before saving your file.
I was running Excel 2010 on a Windows 7 machine (vintage about 18 months ago). I’m not sure how much RAM I have (does anyone keep track of that any more?).
In my spreadsheet, I had an array that was 16,931 rows long. A lot of trial and error showed that it wouldn’t work to copy 110 or more columns that size.
But, gladly, some trial and error also showed that I could go up to 52 columns with no problem (some blue circling from Windows, but the whole copy was done in well under a minute.
One sign that you’re pushing the limits is that the animated dashed line that surrounds a selected range in Excel stops moving. But, this doesn’t mean your stuck yet. You may also get the blue circle to appear and reappear a few times. Again, this is not a signal that you’ve killed Excel.
Unfortunately, I observed no symptoms when I crossed the threshold from possible to not possible. All I know is that the time to complete a big copy goes up steadily until that threshold is reached. After that, the time goes up very rapidly. In short, lack of responsiveness for more than 5 minutes means you should probably close Excel in Windows Task Manager, and start over.
* Why was I copying so much? I have a big data set in a single worksheet, and I had to create a large number of variations on the data series using various Excel functions. But, what a stats program really wants is just the numbers, and not the functions. So I was copying the finished array full of functions, and doing a paste special to values in a clean workbook. The paste special itself didn’t seem to bother Excel at all. If it can get the chunk that needs copying into memory, it can paste it.