by Allen Wyatt
(last updated December 21, 2019)
Sometimes when Joan copies or cuts and pastes something into a worksheet, her page fades and tons of exclamation marks in a sign shape pop up. She cannot click on the shapes to see what they are, and Excel is shown as "Not Responding." After a few seconds to several minutes, it finishes pasting and everything returns to normal. But about 10% of the time, Excel is just frozen like that. Joan notes that this started about 8 months ago. She's working with a 45-page workbook that she doesn't want to re-create, so she is searching for ideas as to why this would be happening.
There are many reasons why this could be happening, so anything that might be offered in this tip would simply be suggestions that may help.
The first thing to do is to figure out if this problem gets worse as you are using the workbook. For instance, let's say you paste a block of data into your workbook and it works fine. Does each successive paste into the worksheet become slower and slower, however? If it does, the problem could very well have to do with how memory is being used in your system.
You see, every time you copy and paste something, Excel utilizes some of the RAM in your system. (Makes sense, huh?) The problem is, Windows may not fully release the used RAM after you are done pasting. So, each successive copy and paste reduces the amount of available RAM in your system and, therefore, goes slower and slower.
An easy way to address this—besides getting more memory in your system—is to every so often shut down Excel and restart. This should clear out all RAM usage for the program, and your pastes should happen faster.
If the problem is evident even with your very first copy and paste, then you should use the Windows Task Manager to see how memory is being used on your system. If resources are maxing out, then you may want to seriously consider adding more memory.
If you are still having issues, then you'll want to start looking at your actual workbook. Every time you paste information into a workbook, Excel does an entire recalculation. Depending on the number of formulas and conditional formats you have in the workbook, this can take quite a bit of time. It can take even longer if your conditional formatting rules have unidentified errors in them. Though it may take a while, you would be well served to double-check the rules and make sure they are valid. (Depending on your workbooks, it may be easier to simply delete all the rules and reapply all your conditional formats.)
You should also consider where the workbook is stored. Is it on a network drive? This can slow things down in ways that may not be evident at first. The way to check it though, is to copy the workbook to your local drive and give it a different name there. Then, open the workbooks—one on the network and the other on your local drive—and do a paste operation. You can then easily tell if the network version of the workbook runs slower than the local version.
You should also check to see if any add-ins enabled in Excel may be affecting the speed of pasting. Taking the workbook to a different computer that doesn't have the same add-ins will help to answer if they are an issue to check into further. (If the speed of pasting doesn't decrease on the other machine, then you can start disabling add-ins on your machine.)
Finally, you might consider turning automatic calculations off in Excel. Once all your pasting is done, you can then turn it back on.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13715) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Function keys are used to perform common tasks in Excel. If you want to disable one of the function keys, it's rather ...Discover More
Want to find out exactly what version of Excel you are using? Here's how to get to the info.Discover More
Have you ever recalculated a worksheet, only to notice that not everything calculated as it should? Here's a way you can ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.