Home > Data > Data Entry > Copy Paste Excel Fix Copy & Paste Problems 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.

Video: Copy & Paste Problems

This video tutorial shows two problems you might have in Excel, when copying and pasting multiple selections, and how to avoid these problems:

• Excel might show an error, "This action won't work on multiple selections"
• Values might be pasted, instead of formulas.

The written instructions are below the video.

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

Copy Error for Multiple Selections

If you select more than one group of cells on a worksheet, and try to copy them, Excel might show an error message:

• "This action won't work on multiple selections"

Or, in some versions of Excel, you'll see this error message instead:

• "That command cannot be used on multiple selections"

Why You See Copy Error

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

1. select cells in columns A:C
2. press Ctrl, and make another selection in different columns, A:B
3. press Ctrl+C to copy the selected cells (or use another method to copy)

Avoid Excel Copy Error Message

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:

• the exact same columns
• OR, the exact same rows

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.

• both selections are in the exact same columns -- A:C
• the selections have a different rows, which is fine

No error message will appear when copying this multiple selection

Paste Problem for 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: 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.

Paste Problem Example

Here's an example, from one of my Microsoft 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

• In column G, the product cost was calculated with a VLOOKUP formula
• In column H, the total cost was calculated by multiplying quantity x cost

I didn’t need all the data in the new workbook, so I followed the steps below, to copy and paste part of the data:

• First, I used the mouse to select the heading and first two rows of data (cells A1:H3)
• Next, I used the keyboard shortcut, Ctrl + C, to copy the selected cells
• Then, on a new sheet, I selected cell A1, and pasted that data, using the keyboard shortcut, Ctrl + V
• Next, I went back to the original table, and selected A5:H6
• Then I pressed the Ctrl key, and selected cells A8:H8.

• Returning to the new sheet, I selected cell A4, and pasted that data

A few minutes later, in the new worksheet, I noticed a problem:

• In cell F4, I changed the quantity from 10, to 15
• The total, in cell H4, did not change -- it still shows the total cost for 10 items

For troubleshooting the problem, I did these steps:

• clicked the Formula tab at the top of Excel
• clicked the Show Formulas command

Here is the copied data, with the Show Formulas setting turned on

• There were formulas in rows 2 and 3
  • a single range was copied and pasted
• There were values, instead of formulas in rows 4, 5 and 6
  • multiple range selection was copied and pasted

How to Paste the Formulas

To keep the worksheet formulas, when copying and pasting multiple selections, follow these steps:

• Copy the multiple selections
• Right-click the cell where you want to start the paste
• In the pop-up menu, click Paste Special
• In the Paste Special window, click OK

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

Video: Turn Off Paste Options in Excel

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.

Turn off Paste Options buttons

To turn off the Paste Options buttons, follow these steps

• At the top left of the Excel window, click the File tab
• In the list at the left, scroll to the end, then click Options
• The Excel Options dialog box opens, with a list of categories at the left
• In the list of Option categories, click Advanced
• Scroll down to the "Cut, Copy and Paste" section
  • Remove the check mark from 'Show Paste Options button when content is pasted'
  • (optional) Remove the check mark from 'Show Insert Options buttons'.
• Click the OK button, to close the Excel Options window.

Get the Sample File

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.

More Tutorials

Copy Number Cells Only

Paste, Skip Blanks

Filtered List Paste Problems page

Data Entry Tips

Excel Table