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

Problems Opening Documents from Explorer

Windows Explorer is a great way to browse through the files available on your system. If you see a document file you want ...

Discover More

Displaying Letter Grades

Grading in schools is often done using numeric values. However, you may want to change those numeric values into letter ...

Discover More

Best Quality for High Resolution Graphics

You want your documents to look as good as they can. If those documents include graphics, then you also need to make sure ...

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)

Changing Your Name

One of the many pieces of information that Excel keeps track of is your name. If you want to change your name for Excel's ...

Discover More

Removing Add-ins

Add-ins are used to extend Excel's capabilities in lots of different ways. If you want to get rid of an add-in ...

Discover More

Spotty Recalculation

Does your worksheet or workbook not always recalculate like you expect? If so, then some of the ideas in this tip may ...

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 8 + 1?

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.