Problems Pasting Information into a Worksheet

by Allen Wyatt
(last updated December 21, 2019)

8

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Changing Revision Bar Thickness

Ever wonder how to customize the way the Track Changes feature displays revision bars at the side of changed material? ...

Discover More

Changing Shading when a Column Value Changes

If you have a data table in a worksheet, and you want to shade various rows based on whatever is in the first column, ...

Discover More

Creating Labels

Using Word to create and print labels is a snap. All you need to do is provide the text you want on the labels, pick a ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Message about a Problem with the Clipboard

Imagine this: You are working along just fine in Excel, then you try to make an edit to your workbook that causes a ...

Discover More

Embedding Your Phone Number in a Workbook

Want to provide a bit of contact information in a workbook? A great place to do it (out of sight, but not inaccessible) ...

Discover More

Disabling a Function Key

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
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 9?

2019-12-28 14:48:16

John Mann

One detail I noticed in the original question is that it started 8 months ago. That raises the question of whether a change was made to thecomputer which might be a cause of problems. For example. new software added, software or drivers updated, new hardware (and related drivers) added, or even updating Windows itself. If the source of data being copied is external to the workbook, could that have some subtle change which is not visibly obvious?

This comment is in addition to the toher suggestions.


2019-12-23 12:42:11

Alyssa Moehle

Also a thing worth considering is the age of the spreadsheet. We have many spreadsheets from every version of Excel in my office (Word too). And we've had to start over from scratch on a few of the files. They'd have various issues: crash upon opening, never retaining formatting, colors completely unusable, no pasting without crashing, errors, etc. It reminds me of upgrading Windows vs a clean install. Sometimes the little bits of stuff tend to gum up the system, and/or old settings referring to files/DLLs no longer there.


2019-12-23 04:42:03

Janet

Have you tried copying and pasting smaller chunks of data at a time? Have you tried restarting your computer before opening this program and not opening other applications? Happy Christmas.


2019-12-21 15:13:24

Michael

For myself the real bugaboo hanging Excel when moving data around, copy pasting it in this or that column has been Conditioning Formatting in cells that I forget to consider. So that it is then applied many many times unnecessarily overlapping.


2019-12-21 11:58:01

Ron MVP

Actually, testing addins should happen earlier in the process. And before getting to the process of turning off addins one at a time you should turn them all off. You do this by starting Excel (any Office app) in "safe mode". Hold the <CTL> key while starting the application to start it in Safe Mode.

Then test to see if the problem has gone away. If it has, then addins are the problem, and you then can start turning off addins.


2019-12-21 10:51:15

J. Woolley

You might have better results if you right-click the destination cell in Excel and pick one of the Paste Special options. Or consider Pure Text, which I have used for years:
http://stevemiller.net/PureText/


2019-12-21 10:43:13

J. Woolley

@Eddy Janssens
You probably already know this, but Excel can open CSV directly (File > Open or drag-and-drop). You don't have to copy-paste.


2019-12-21 08:25:37

Eddy Janssens

Hi Allen,
I noticed the same behavior when pasting ± 200 000 lines out of a CSV file into a workbook. Before It took ±3.0 seconds. After the update 1903 from windows 10, it takes ± 35 seconds... Switching off the automatic calculation did not change anything... Any idea?


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.