How to copy and paste quickly in Excel 2011 for Mac.
January 20, 2011 10:41 PM Subscribe
In Excel 2011 for Mac, everything slows to a crawl if I cut or copy a large number of cells. Is there any way to fix this? If not, is there a different spreadsheet program that could do this quickly?
As part of my work, I often have to cut/copy and paste a relatively large number of cells---e.g., a 500x500 square of cells---in Excel. When I do this on a windows machine, the process is instantaneous, regardless of the OS or the version of Excel. Unfortunately, when I try the same procedure in Excel 2011 on my new Macbook pro, the program hangs for a few seconds as I watch the spinning beach-ball. This is particularly frustrating because I often need to repeat the copy/paste procedure a few dozen times.
I have two questions: First, is there any way to tweak Excel 2011 for Mac to speed up the copy/paste procedure? I can instantaneously copy and paste this much data in any other program and every other feature in the 2011 Office suite runs without hesitation.
Second, if there's no way to get around the slowdown, are there any spreadsheet programs for Mac that are both faster and can handle data with a thousand or so columns (most spreadsheet software seems to max out at 256 columns). I already use databases for large data-files and do all of my statistical work in R, but it would be great to have access to a reasonably fast spreadsheet program as well.
As part of my work, I often have to cut/copy and paste a relatively large number of cells---e.g., a 500x500 square of cells---in Excel. When I do this on a windows machine, the process is instantaneous, regardless of the OS or the version of Excel. Unfortunately, when I try the same procedure in Excel 2011 on my new Macbook pro, the program hangs for a few seconds as I watch the spinning beach-ball. This is particularly frustrating because I often need to repeat the copy/paste procedure a few dozen times.
I have two questions: First, is there any way to tweak Excel 2011 for Mac to speed up the copy/paste procedure? I can instantaneously copy and paste this much data in any other program and every other feature in the 2011 Office suite runs without hesitation.
Second, if there's no way to get around the slowdown, are there any spreadsheet programs for Mac that are both faster and can handle data with a thousand or so columns (most spreadsheet software seems to max out at 256 columns). I already use databases for large data-files and do all of my statistical work in R, but it would be great to have access to a reasonably fast spreadsheet program as well.
How much RAM do you have? I have XL11, too, and have not had this problem.
posted by dfriedman at 10:46 PM on January 20, 2011
posted by dfriedman at 10:46 PM on January 20, 2011
Response by poster: dfriedman: I have 4 gigs of RAM and a 2.5 GHz i5 processor.
posted by eisenkr at 12:19 AM on January 21, 2011
posted by eisenkr at 12:19 AM on January 21, 2011
Its probably related to the machine swapping virtual memory. I'm on Office 2011 as well and due to speed I investigated and noticed (using activity viewer) that just launching the program takes up almost half a gigabyte of virtual memory. If I do anything sizeable (like your example) I too see the spinning beach ball which is damn annoying.
I can pick up a little speed by insuring I'm not running other Office programs (i.e., Word, PowerPoint) or other programs that have a large memory footprint. Use activity viewer to fiture out whats going on.
The only Macintosh alternative I'm aware of - Open Office - has a 256 column / 1024 row limit in version 2.4, apparently 3.3, currently distributed as a realise candidate, expands this to 1024 columns but I haven't tried if.
Some folks have recompiled Open Office to push past these limits, but other problems (e.g., lack of Excel binary compatibility, etc) arise so you'd have to think carefully about this.
If its any consolation Power Point on Office 2011 is a real dog at some points, especially if you're trying to change the colour of a font; I've timed the beach ball at almost ten minutes before I regain control.
posted by Mutant at 12:30 AM on January 21, 2011
I can pick up a little speed by insuring I'm not running other Office programs (i.e., Word, PowerPoint) or other programs that have a large memory footprint. Use activity viewer to fiture out whats going on.
The only Macintosh alternative I'm aware of - Open Office - has a 256 column / 1024 row limit in version 2.4, apparently 3.3, currently distributed as a realise candidate, expands this to 1024 columns but I haven't tried if.
Some folks have recompiled Open Office to push past these limits, but other problems (e.g., lack of Excel binary compatibility, etc) arise so you'd have to think carefully about this.
If its any consolation Power Point on Office 2011 is a real dog at some points, especially if you're trying to change the colour of a font; I've timed the beach ball at almost ten minutes before I regain control.
posted by Mutant at 12:30 AM on January 21, 2011
yeah, definitely sounds like a virtual memory issue, you might try tweaking your settings...it could also be somewhat related in that laptops generally use slower hard drives (to save power), so the slowdown could be related to that, which might be harder to fix without swapping hardware...the easier of those options would be getting more ram and turning off virtual memory...
posted by sexyrobot at 1:05 AM on January 21, 2011
posted by sexyrobot at 1:05 AM on January 21, 2011
Odd, I don't have a 500x500 block, but the 5000x50 block I had copied nearly instantaneously on my Early 2008 MacBook Pro with 4GB RAM. I did it a couple of times and once the beach ball made a brief appearance. Overally it seemed as zippy as Office 2008 Mac or Office 2007 Win.
I like the improvements to Office 2011 overall, but it is at 14.0.2 right now and it seems MS takes a while to squish some bugs. In Word the other day the font color dropdown from the ribbon was blank (just a gray box). Quitting and restarting Word fixed it.
Bless the Mac Business Unit, but it just seems like their products are a little doughy in the middle when they come out. It doesn't help you now,
Snow Leopard by default encrypts virtual memory. Perhaps turning that off might speed things up for you. Then again, my 3 year old system doesn't present this problem. However, like many things on apps ticking and unticking boxes might fix things.
posted by birdherder at 1:45 AM on January 21, 2011
I like the improvements to Office 2011 overall, but it is at 14.0.2 right now and it seems MS takes a while to squish some bugs. In Word the other day the font color dropdown from the ribbon was blank (just a gray box). Quitting and restarting Word fixed it.
Bless the Mac Business Unit, but it just seems like their products are a little doughy in the middle when they come out. It doesn't help you now,
Snow Leopard by default encrypts virtual memory. Perhaps turning that off might speed things up for you. Then again, my 3 year old system doesn't present this problem. However, like many things on apps ticking and unticking boxes might fix things.
posted by birdherder at 1:45 AM on January 21, 2011
I have the exact same problem. I had a 2gb Macbook Air, and it really crawled when pasting in Excel. I went to the app store and remotely downloaded the same document to test on a 4gb MAc Book Pro, and had the same probem, no difference at all, so I don't think it's a RAM or CPU issue, but rather just poor porting.
I ended up installing VMware and Windows 7 and can run Excel on a 2gb air, very quickly. Though RAM is an issue for me so I upgraded to 4GB, but really even a large Excel doc shouldn't use more than 500Mb at teh very most of RAM, so I'm not convinced vittual memory is the problem here. (unless I'm confused about what virtual memory is - that is when your RAM runs out?)
posted by choppyes at 2:48 AM on January 21, 2011
I ended up installing VMware and Windows 7 and can run Excel on a 2gb air, very quickly. Though RAM is an issue for me so I upgraded to 4GB, but really even a large Excel doc shouldn't use more than 500Mb at teh very most of RAM, so I'm not convinced vittual memory is the problem here. (unless I'm confused about what virtual memory is - that is when your RAM runs out?)
posted by choppyes at 2:48 AM on January 21, 2011
Years ago, I had a similar issue and one thing that helped was to turn off the function that automatically recalculates formulas and turn it to manual. No idea if that will help you in this case, but it's a pretty easy thing to try and see if it helps.
posted by CathyG at 6:35 AM on January 21, 2011
posted by CathyG at 6:35 AM on January 21, 2011
Response by poster: Thanks for all of your thoughts and suggestions so far. Depending on how frustrating this gets, I could see doing a hardware swap to correct the virtual memory problem. Would the virtual memory bottle-neck get cleared if I replace the hard disk with an SSD?
posted by eisenkr at 7:52 AM on January 21, 2011
posted by eisenkr at 7:52 AM on January 21, 2011
I tested the same document on a late 2010 Air with SSD and an early 2010 Pro and noticed no difference at all. Looking at activity monitor, it is the CPU that spikes, which is not related to virutal memory and RAM isn't even dented. So no I don't think SSD will make any difference.
My guess is that hardware is not the issue here.
One thing might help is using native Mac fonts (surprisingly effective)
posted by choppyes at 8:26 AM on January 21, 2011
My guess is that hardware is not the issue here.
One thing might help is using native Mac fonts (surprisingly effective)
posted by choppyes at 8:26 AM on January 21, 2011
Response by poster: Now that you point it out, Choppyes, I see the CPU spike as well. I'd hate to see what horrific and expensive processing they think absolutely needs to get done every time somebody hits copy. I'm going to see if there's some way to only "copy values," but something tells me I'm out of luck.
posted by eisenkr at 9:34 AM on January 21, 2011
posted by eisenkr at 9:34 AM on January 21, 2011
Best answer: Weird to answer my own question, but the new version of Libre Office can handle a huge number of rows and columns and still manages to copy and pastes instantly! Problem solved.
posted by eisenkr at 12:07 AM on January 26, 2011
posted by eisenkr at 12:07 AM on January 26, 2011
I've seen this in both Office 2008 & 2011. I'm not even looking at pasting data -- just copying it! It takes tangible seconds to put data into the copy buffer so I can paste it into another program, even with the most recent updates Office 2011 on a quad-core i7 MBP with 8 GB ram. Yes, the spreadsheet I'm pulling from has 35k rows and 200 columns, but I'm copying a single cell that's being displayed on the screen.
It doesn't seem like it should even be a matter of loading some code module from disk; I copied 10 cells in a row out, one at a time, to paste into a bug report, and I had the lag on each copy. I would paste into the web browser, see the old value, undo, wait a bit, re-paste, and repeat until I saw the new value from Excel.
I'm going to ask on the Ars Technica mac forum and see if the local MacBU guy knows anything.
posted by mhannemann at 8:30 AM on April 28, 2011
It doesn't seem like it should even be a matter of loading some code module from disk; I copied 10 cells in a row out, one at a time, to paste into a bug report, and I had the lag on each copy. I would paste into the web browser, see the old value, undo, wait a bit, re-paste, and repeat until I saw the new value from Excel.
I'm going to ask on the Ars Technica mac forum and see if the local MacBU guy knows anything.
posted by mhannemann at 8:30 AM on April 28, 2011
Please let me know if you work this one out - it drives me crazy!
posted by choppyes at 4:30 PM on May 2, 2011
posted by choppyes at 4:30 PM on May 2, 2011
This thread is closed to new comments.
posted by eisenkr at 10:42 PM on January 20, 2011