Problems Pasting Information into a Worksheet

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 there was a precipitating event that occurred 8 months ago. Was your system updated? Was new software added? Were software drivers updated? It can be difficult to remember such things from 8 months ago, but it could be crucial to nailing down the cause.

If nothing comes to mind, the first thing to check out is if the problem is related to the data you are trying to paste. The easiest way to figure this out is to use Paste Special to paste as either plain text or some other option that doesn't involve formatting. If it pastes faster, then you should consider using that method of pasting in the future or just paste in smaller chunks of information, which means they should paste quicker.

Next, 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 decreases on the other machine, then you should disable all add-ins on your system and start adding them back in one at a time to figure out which one is the likely culprit.)

The next 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.

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, 2021, 2024, and Excel in Microsoft 365.