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!

Here are some Excel copy and paste tips and trouble shooting suggestions.

Copy and Paste

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!

This video shows some of the problems, and the written details are below the video.

NOTE: Updated with new, improved video (Feb 5, 2020)

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.

Copy and Paste Multiple Sections

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.

Pasted Values Only

Then, I changed one of the amounts in the copied data, and noticed a problem. In column H, 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.

Keep Your Formulas

To keep your formulas, when you’ve copied multiple selections, follow these steps when pasting:

• 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

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

Select Ranges in Same Rows or Columns

If you’re planning to copy them, the selected ranges must include exactly the same rows or columns.

For example, if your first selection is B1:D6, you can also select H1:H6 (same rows), but not H1:H5 (different rows).

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)

Happy copying!
___________