Excel Copy and Paste Tips and Trouble

Copy and paste. It’s one of the first things you learn to do in Excel, and something you do every day. Without copy and paste, your Excel work would take much longer, and you’d be exhausted by the end of the day, from all that typing!

With copy and paste, you can select a huge range of cells, with dates, text, numbers and complicated formulas, and paste them on a different sheet, or another workbook. It’s so quick and easy to do, that you take it for granted.

Until something goes horribly wrong!

Copy and Paste Catastrophe

Last week, I was merrily copying and pasting data from one workbook to another, and everything was going well — or so I thought. The data was in a table, like the one shown below, with formulas in a few of the columns.

CopyPasteProblem01

I didn’t need all the data in the new workbook, so I copied the top section, and pasted it. Then, I copied a few sections in the middle, by pressing the Ctrl key as I selected different ranges.

CopyPasteProblem02

Then, I changed one of the amounts in the copied data, and noticed a problem — the total didn’t change. Shown below is the copied data, with Show Formulas turned on.

CopyPasteProblem03

For the rows that I pasted as multiple range selections, the values were pasted — not the formulas. Yikes! That could create serious problems, if you didn’t notice the missing formulas.

Quick Copy and Paste Values

As long as you’re aware that copying and pasting a multiple range selection results in pasted values, you can use that to your advantage. Instead of using the Paste Special – Values command, you can simply select the data in two separate chunks, then copy and paste it.

CopyPasteProblem04

Copy Multiple Selections Error

Sometimes you might select multiple ranges, and when you try to copy them you see an error message, “That command cannot be used on multiple selections.”

CopyPasteError

If you’re planning to copy them, the selected ranges must include the same rows or columns. For example, if your first selection is B1:D6, you can also select H1:H6, but not H1:H5.

You can select multiple ranges in different rows or columns, as long as you keep the ranges consistent. In the screenshot below, B1:D6 and H1:H6 were selected. Then, B9:D10 and H9:H10 were selected, and those four ranges were copied.

Each selected row has identical ranges selected (B:D, H) and each selected column has identical ranges selected (1:6, 9:10)

CopyPasteProblem05

Happy copying!

___________

You may also like...

6 Responses

  1. RALPH says:

    When using Excel (Office 2003),I do not get the option to paste a copy, only to insert copied cell. No’Paste’ or Paste Special”

  2. Steve Barnes says:

    Hi
    I have used Excel for years, and never run into this problem.
    I have been keeping a food log in a simple Excel worksheet…….rows and columns.
    When I have the same food on a different day or for a different meal time, I have always just
    cut and pasted……no problem…..highlight, click cut, move to new line, click paste.

    Suddenly, yesterday, it still cuts and pastes, but it doesn’t past what I cut….it pastes entirely
    different information from a different section of the page. Bizarre!

    How can I fix this? Is there a way to clear its cut-and-paste memory? (I’m using Windows 8)

    Thank you.

  3. Lucky_261 says:

    Hi Steve,

    Did you find any solution for your issue? I’m getting the same issue. I think this happens with excel tables only.

    Thanks in advance

    Lucky_261

  4. Bill G says:

    I had the same issue and realized I had inadvertently had another tab selected. So even when I was selecting one single cell the system was attempting to copy the single cell in both tabs resulting in the error to occur. Hope this helps.

  1. May 13, 2013

    […] Originally Posted by slipperx I am using Excel 2010. When I select and copy a single cell (or a group of cells) in one workbook and try to paste into another Workbook loaded in the same instance of Excel I get an Error 'That command cannot be used on multiple selections' – I cannot even paste into a bran new opened workbook. However if I open another instance of Excel then the data pastes quite happily. Any ideas as to why this behaviour is happening? See if the link below helps. Excel Copy and Paste Tips and Trouble | Contextures Blog […]

Leave a Reply to Bill G Cancel reply

Your email address will not be published. Required fields are marked *