Copying and pasting can save you lots of time in Excel. Occasionally though, you might run into problems. See how to fix Excel copy and paste problems when you’re working with multiple selections. One problem is sneaky, and you might not even know it’s happening!
Excel Copy and Paste Problems
In the video below, you’ll see two Excel copy and paste problems, and how to fix them:
- If you try to copy more than one group of cells on a worksheet, you might see this Excel error message: “This action won’t work on multiple selections.”
- If you copy more than one group of cells on a worksheet, and paste them in a different location, Excel might change your formulas to values.
- Note: Excel doesn’t show a warning message about this problem. You might not even notice that it’s happening, until it’s too late!
Video: Copy and Paste Problems
In this video, you’ll see two problems you might have when copying and pasting multiple selections, and how to fix them.
To follow along with the video, go to the Fix Excel Copy and Paste Problems page, on my Contextures site, and get the sample file.
Video Timeline:
- 0:00 — Introduction
- 0:15 — Copy Error Message
- 0:42 — Avoid the Error Message
- 1:54 — Paste Problem
- 2:40 — Copy 2 Ranges
- 3:23 — Paste With Formulas
- 4:13 — Get the Workbook
NOTE: The video transcript is at the bottom of this page.
Get the Sample File
To follow along with the video, go to the Fix Excel Copy and Paste Problems page, on my Contextures site, and download the sample file.
That page also has written instructions for the copy and paste problem fixes.
More Data Entry Tips on Contextures
Data Entry Tips
Fill Blank Cells
Video Transcript
This is the full transcript from the “Copy and Paste Problems” video, shown above.
‘——————
Video Introduction
In this video, I’m going to show you a couple of problems that you might run into, if you’re trying to copy and paste a couple of regions in Excel.
This is Debra Dalgleish from Contextures.com.
Copy Error Message
One problem that you can have when trying to copy and paste multiple regions, is getting an error message from Excel.
I’m going to select a few sales here and I’ll press Ctrl, and select a few more cells and then maybe a few over here.
And if I press Ctrl + C to copy, Excel says, “This action won’t work on multiple selections.”
Avoid the Error Message
You can select multiple regions and copy them, but you have to select either,
- selections from exactly the same columns
- or exactly the same rows
So I’ve got some in columns, A, B, and C, and then others in D and F. So that’s why I get that error message.
To fix that, I’m going to unselect. I’ll press Ctrl, and just drag over those and that unselects them.
So if I want to select something else, right now, I’ve got columns A, B, and C selected, and rows 4 through 8.
So I could either:
- select rows 4 through 8 in another column.
- or I can select something else in columns A, B, and C.
I’ll come down and select a few more cells here. When I press Ctrl + C, it copies without any error.
And I’m going to unselect again with Ctrl and drag.
If I want to select something else over here, I can, as long as I stay in rows 4 through 8. So I’ll press Ctrl, and when I press Ctrl + C, it copies it without any problem.
Paste Problem
You might also run into pasting problems when you’re working with multiple copied ranges.
I have a list of orders with a few columns of information, and here I have the number of units that were sold, the unit cost, and a very simple formula here that multiplies those two numbers to give us a total,
I’m going to select the headings and a few rows, and do a Ctrl + C to copy. And go to my Test1 sheet, I’ll click here, and on the Home tab, click Paste.
Everything looks okay.
So if I changed this from 46 to 50, the total changes, everything’s working well.
Copy 2 Ranges
Now I’m going back to the order sheet.
This time I’m going to select a couple of rows at the top, and to select something further down, I’ll press the Ctrl key, and select these rows.
I’ll click the Copy button or you could press Ctrl + C, and I’ll go to the Test2 sheet this time.
And if I click here and paste, it all looks just like it did before, so I’ll change this again to 50.
But the total doesn’t change, because I had multiple ranges selected, when it pasted, it pasted values here, instead of the formulas.
Paste With Formulas
I’m going to undo that. Now back on the order sheet,
- I’m going to press Esc to stop the copy.
- And I’ll select three rows, Ctrl, and select a few more at the bottom.
- Then I’ll Ctrl + C to copy.
And I’m going back to Test2, but this time
- instead of just clicking the paste button,
- I’ll right click, click paste special,and it’s going to paste all.
- I’ll click OK.
And now I’ve got formulas here, so if I change that to 50, it changes the total.
So if you’re going to copy and paste multiple regions, don’t do a simple Paste button, do Paste Special and Paste All.
Get the Workbook
Thanks for watching this video. You can go to my website, Contextures.com, to get this workbook.
And please subscribe to my Contextures YouTube channel, so you can see the latest videos as I post them.
_______________________
How to Fix Excel Copy and Paste Problems
_______________________
Thank you Debra for this helpful post. Another thing I’ve found is when you try to copy/paste from one Excel session/instance to another (even if they’re the same version of Excel) you don’t always get what you want. Formulas can become values, formats can be lost, and invisible cells which were not included in your Copy range can magically appear when you Paste.
Hi Jerry, maybe the activation of Excel Instances – if you use Microsoft Office 365 – helps to solve your problem. Because using a separate instance I found out that formats and data are copied as they are visible in one Excel file, hidden content etc. is not copied that way if you paste the content as value e.g.: https://docs.microsoft.com/en-us/office/troubleshoot/excel/force-excel-to-open-new-instance
This was not helpful for the issue myself and my partner are having. When we go to either copy or paste information within a cell : for an example the cell says, ” Remotely Sent: Exp 2/5 AI836393 ”
Now what we need to copy or paste is just that confirmation number. But when we go to select just that number it ends up either copying the whole cell or when pasting , it overrides the whole thing and erases whats already in the cell.
Yeah for me my same problem as Jerry Cooper CMA, I have been trying to copy and paste a selection of 37 cells but they end up being 50+ cells when I paste them into the document I want them in.
Erin, try this, to avoid copying the hidden cells:
–Select all the cells that you want to copy
–Then, to select the visible cells in the current selection, press Alt + ;
–Press Ctrl + C to copy
–Go to the other location, and press Ctrl + V to paste
no its not working either, when I filtered and tried to copy paste still the hidden cells came in after doing alt+; thing if theres any other way please let me know
Hi, my issue is I am copying from multiple documents, ie other excel, some pdf, some word, and in some cases the copied data ie a descriptive name; with numbers ie file numbers, when then pasted to an excel sheet, pastes over two cells, breaking up the copied data.
I had this happen in the past, and don’t recall how to correct. Can you assist?
I am sorry your answers did not solve my problem. I am using excell mobile ( tablet). When I copy and paste from one sheet to another on the same folder I don’t have a problem. But I lose all my formulas when I open a new excell folder.
Hello, I got an issue with Excel copy Paste. When I copy data from one cell & Paste it on other applications,the copy is still active in the excel. For Eg, I normally work with SAP & Excel, copy data from Excel (1), paste it in Sap & Copy the results from SAP (2) , and when I try to paste it in excel, the copy (1) still active there and I have to hit the escape Button & Open the clip board to find the copy (2) from SAP then paste it. It was not like this before, but now it’s really annoying. Hope you can help
Hi Sumesh,
I am facing same issue, did you get any solution on this ?
Just sharing. I encountered the same problem and here was the reason…
I was working on multiple sheets and i accidentally selected cells from different sheets or more than 2 sheets. Unfortunately i saved the document with that “multiple selection” and that gave me headaches until i noticed that i have at least 2 selected sheets.
Try to click the sheet title to deselect the others. -Ray
Transferring formulae was never a problem for me but, I have been facing the problem of losing the margin and orientation formatting while copy -pasting one sheet to the other. The orientation and the margins always change. Don’t know how to fix that.