See how to fix Excel copy and paste problems and avoid multiple selections error messages. Also, prevent Excel from changing your formulas to values, when pasting multiple selections.
This video shows two problems you might have in Excel, when copying and pasting multiple selections, and how to avoid these problems:
The written steps are below the video.
If you select more than one group of cells on a worksheet, and try to copy them, Excel might show an error message:
Or, in some versions of Excel, you'll see this error message instead:
Excel will show that copy error message if you selected multiple ranges, and those ranges don’t “match up”.
For example, Excel will show a warning message if you
To avoid that copy error message, be sure to select multiple regions that DO "match up".
All of the selected regions that you want to copy must be in either:
If even one of the selected cells does not match the other selections, Excel will show that error message.
The screen shot below shows an example of two selections that DO match up.
No error message will appear when copying this multiple selection
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: This problem can cause serious problems, because Excel doesn’t give you a warning message. If you don't immediately notice that the formulas were changed to values, your workbook could have serious errors later.
Here's an example, from my Excel workbooks, of how this copy and paste problem can damage your worksheets.
One worksheet had a named Excel table, where some columns had formulas
I didn’t need all the data in the new workbook, so followed these steps, to copy and paste part of the data:
A few minutes later, in the new worksheet, I noticed a problem:
For troubleshooting the problem, I did these steps:
Here is the copied data, with the Show Formulas setting turned on
To keep the worksheet formulas, when copying and pasting multiple selections, follow these steps:
With this technique, all the formulas will be pasted too.
This can can help you avoid potential problems, caused by Excel pasting values, instead of formulas
When you copy and paste in Excel, you might see option boxes, at the bottom right of the pasted data. You can click those buttons, to select an option for pasting the data, such as Paste Values.
If you don't like those boxes, and never use them, watch this video to see the steps to turn this feature off.
Note: For newer versions of Excel, click the File tab, instead of the Office button.
To turn off the Paste Options buttons, follow these steps
Copy and Paste Problems: Download the sample file for multiple selections copy and paste problems, to follow along with the video. The zipped file is in xlsx format, and does not contain any macros.
Last updated: November 9, 2022 11:59 AM