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.
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.
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.
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.
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.”
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)