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.

Excel warning message: This action won't work on 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"

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

original data with formulas

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.

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

Pasted Numbers Change to Dates

If numbers such as 1-4 or 3/5 are pasted into Excel, they will usually change to dates. Other types of numbers, such as zip codes with leading zeros, lose their formatting too. For example, copy the numbers below, and paste them onto a worksheet, to see how Excel adjusts them.

  • 3/4
  • 02345
  • 1-3

In the screen shot below, the original data is at the left, and the data pasted onto a worksheet, at the right. Two of the numbers were changed to dates, and the leading zero was dropped from the other number.

numbers change to dates

Prevent Numbers From Changing to Dates

To prevent Excel from changing the number format, follow these steps:

  • In Excel, select the columns where you will paste the data -- be sure to include enough columns for all of the data that will be pasted
  • On the Ribbon's Home tab, in the Number group, click the drop down arrow, and click on Text

number format text

  • Select the data and copy it
  • In the column(s) that you formatted, right-click the starting cell where you want to paste the data
  • In the popup menu, under Paste Options, click Match Destination Formatting

number format Match Destination Formatting

  • The data will be pasted in its original formatting, instead of changing to dates.

number format Match Destination Formatting

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.

turn off Paste Options buttons

Get the Sample Files

  1. 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.
  2. Numbers Paste As Dates: Get the sample file for pasting formatted numbers, to see how Excel sutomatically changes the numbers to dates. 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

Excel Humour

Has Excel ever ruined your day with annoying cut and paste errors? If you're lucky, you notice the problem right away, so you can undo it!

Excel cut and paste errors can ruin your day!



Last updated: April 1, 2024 3:36 PM