Normally copy/paste operations in Excel are pretty instantaneous. You copy data from one place and then paste into a cell in Excel. Pretty straightforward. But what do you do if you notice that copy/paste seems to take an excesseively long time when doing a paste in Excel? There are several things you can look for to resolve a situation like this.
1. If you are copying from a web page and pasting straight HTML into Excel there can sometimes be a delay during the paste operation. It depends on how much data you are pasting. the cause of the slowness is the formatting of the data itself. One thing you can do is paste the data into a notepad document , then copy and paste the data into Excel. This will strip out formatting and paste raw ASCII into Excel. Excel will then make a determination on the format of the data based on what gets pasted. You can also choose to Paste Special > Values into Excel which just pastes in the value of the data and none of the formatting.
2. Excessive amounts of custom styles can also cause the copy/paste to be slow. If you click on the Home tab in the Ribbon and look under Styles you will see a drop-down (see below pic) that when clicked will display all of your styles. Custom styles show up at the top and can be removed by right clicking the style and left clicking Delete.
3. Memory over-allocation is another cause of slow copy/paste. To check this, in your workbook hit the END button on your keyboard and then the HOME button. If your cursor drops down past the millionth row or to the last column this is an indication of memory over-allocation. To work around this problem you would need to remove all blank and unused rows and columns. After removal, click File > Save to save the changes to the workbook. This is necessary for the new memory allocation to take effect.
4. Finally, a large number of shapes, conditional formatting and pivot tables can also result in slow copy/paste behavior. If you have a large number of autoshapes or shapes in the workbook you can try removing a portion of these to see if the copy/paste time improves. A large number of conditional formatting can also cause slow copy/paste behavior. To check, click the ‘Conditional Formatting’ drop down under the Styles section of the Home tab and then click Manage Rules. This will open the Conditional Formatting rules manager. You can try deleting unused rules or removing a number of rules to test making the workbook more responsive. Lastly, a large number of pivot tables can cause copy/paste slowness. To work around that problem you can try moving a portion of your pivot tables to separate sheets or even other workbooks.