Why can t I copy and paste formulas in Excel

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

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

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.

• 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

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

1. 07-07-2009, 12:44 PM #1

   I am trying to do a very simple copy and paste of a simple formula in Excel 2007 and nothing seems to be working. Excel will not let me copy and paste a formula and will only paste the value into to workbook. For instance, if I make a very simple spreadsheet such as: A1 type in 10, A2 type in =A1 (calculated A2 to be 10) B1 type in 5 And then click the copy on B1, and then click paste special on cell B2, the only options it gives me are text and unicode text and so no matter how I paste, cell b2 will always be populated with a value of 5. I cannot imagine a simpler copy and paste and no matter what I do I can't make this work. All cells are formatted as general. It seems that all copy and pasting of formulas in my excel has been disabled. If I open any spreadsheet on my computer, I can't copy and paste formulas, but do the exact same thing on the exact same spreadsheet on any other computer and it works no problem.

   Any help?

   Last edited by jakethesnake; 07-07-2009 at 12:48 PM.

2. 07-07-2009, 12:51 PM #2

   Sounds to me as though you have 2 separate instances of XL running as opposed to 2 files within the same instance of XL.

   For ex. close everything completely, open XL, press CTRL + N to create a 2nd file in the same instance of XL, repeat your process above and you should find you can copy / paste etc... If you open 2 instances of XL and run one file in each you will experience the issues you mention.

3. 07-07-2009, 12:54 PM #3

   I only have one spreadsheet open and one instance of excel open. I am literally trying to copy and paste from the cell above to the cell below. Not into different work books or different files.

4. 07-07-2009, 12:57 PM #4

   Could you attach the offending file to the board - we can then see if it's a client issue or specific to the workbook... there may be a setting I'm not aware of
   (I suspect there are plenty!)

5. 07-07-2009, 12:58 PM #5

   sorry to add my 2 cents but did you try dragging the formula down cells? or is that not what you are looking for?

   cheers

6. 07-07-2009, 12:59 PM #6

   I'm not at the offending computer at the moment, but it does it for all spreadsheets, even new ones, but if I save it to our common drive and open it up on any other computer, it works like a charm, so I doubt it would be a file issue (as it is happening to all files and only on this computer), but if you'd still like the file I can upload it in about half an hour..

7. 07-07-2009, 01:00 PM #7

   And yes, I've tried dragging down, also to no avail (again, but only on this computer...)

8. 07-07-2009, 01:01 PM #8

   Just checking ... not to sound like the IT dept but have you tried completely rebooting the client in question ?

9. 07-07-2009, 01:03 PM #9

   when you click on paste special check values and see if that works?

10. 07-07-2009, 01:05 PM #10

    I haven't tried the 'magic windows fix-all' of rebooting yet, but I will now!

    And mikey, when I click paste special, the only two options are text and unicode text. Values, formulas, etc don't even appear.

11. 07-07-2009, 01:17 PM #11

    ahhhh maybe it has something to do with excel itself. I wonder if all the components are installed on it. Does it do it no matter how many different excel sheets you open?

12. 07-07-2009, 01:23 PM #12

    Well, well, resetting did it. By the looks of it there was a program that was accessing the clipboard that had hijacked it and was causing it to fail. It's amazing how much can be fixed by a hard reset. Thanks for your help everyone!

13. 07-07-2009, 01:26 PM #13

14. 07-07-2009, 01:27 PM #14

    re: clipboard etc... some useful links to apps to aid determining underlying cause: http://www.howtofixcomputers.com/bb/ftopic140408.html

15. 04-01-2011, 12:28 PM #15

    In case it helps anyone - I had this exact problem in Excel 2003. Using this thread as a guide I traced the problem to a clipboard enhancement tool I use called Arsclip. Kill that and the copy / paste / paste special works as normal..

    It's a real shame because I find Arsclip really useful - and this is the only time I've found it to interfere with any other program.

16. 09-27-2011, 01:19 AM #16

    Hello all, I am looking to use VBA to replace <= with character code: 2264 from Unicode (hex) or 32 from ASCII (decimal). Typically I would do: Cells.Replace What:="<=", Replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

    But as you can see from the latter half the symbol is not there. Would definitely appreciate any help that you can give me. Thank you!

17. 03-27-2012, 11:08 AM #17

    I had this exact same problem with Excel 2007 on Windows 7, and was able to solve it easily once I figured out what the problem was. My problem was Skype. Specifically, the Skype "click-and-dial" software, which installs itself in numerous programs and browsers. I de-installed Skype (including click-and-dial) then reinstalled Skype without click and dial (for some reason, it did not even offer to install it during the reinstall).

    Voila! Problem solved!

    Last edited by lextran12; 03-27-2012 at 11:11 AM.

18. 04-25-2012, 10:20 PM #18

    I have been having the exact same problems - I just deleted the Skype "click-and-dial" software. Seems to have cured the problem but it was an on or off issue so I am going to give it a few days.

19. 04-25-2012, 10:31 PM #19

    It usually only causes a problem if you have the browser add-in installed and have your browser open while Excel is open. If the browser is closed, copy/paste should work normally. There is also an updated version of the browser add-in from Skype if you do need it installed.

20. 05-15-2012, 03:45 PM #20

    Try this: Click on: Formulas > Calculation Options. From the drop down mwnu, choose Automatic.
    Let me know if that works.

21. 06-08-2012, 03:47 AM #21

    it is right, i also experience the same error, can not "copy" or use "paste special option" though i have been trying a lot of steps to this problem you can: 1/ Close Skype if it is opening or 2/Delete/ remove Skype from Google Chrome browser, no need to unistall it

    Done. Every thing is ok now.

22. 06-08-2012, 02:12 PM #22

    I'm having the same problem. When you say you reset it, do you mean you just re-booted your computer?

23. 06-08-2012, 02:19 PM #23

    Uninstall the Skype browser add-in and you should be able to copy and paste in Excel. If you need the Skype add-in, download the latest version from their website which has fixed the compatibility issue.

24. 09-21-2012, 07:45 PM #24

    I have similar problems in both Excel and Word - but they are random. Sometimes they copy normally, sometimes it is text-only which is very annoying in both. However, I do have Skype running so I shall try what has been suggested here. Thanks.

25. 01-05-2015, 06:35 PM #25

    Just had this happen in Excel 2010 and couldn't figure out what was happening until some googling found this discussion.

    For the record it was PushBullet's Universal Copy/Paste feature that was the culprit in my case. Hope this is helpful for someone else down the road.