Contextures

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 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 steps 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"

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"

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)

different columns selected

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

same columns selected

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 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

  • 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

original data with formulas

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

  • First, I copied the heading and first two rows of data (A1:H3)
  • On a new sheet, I selected cell A2, and pasted that data.
  • Next, I went back to the table, and selected A5:H6
  • Then I pressed the Ctrl key, and selected cells A8:H8.

copy and paste multiple selections

  • 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

values, instead of formulas

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

use Paste Special command

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

Data Entry Tips

Excel Table

 

Last updated: January 17, 2022 1:53 PM