14

I'm trying to copy a block of cells, but instead of shifting down the rest of the rows, it overwrites them

How do we copy and paste a set of cells and shift the rest so nothing is lost?

20

Instead of clicking "Paste", right-click or Ctrl click and select "Insert Copied Cells" or "Insert...", depending on your version of Excel.

insert copied cells

This will give you an option to shift cells right or down.

shift cells

If you cut cells, the context menu will have the option to "Insert Cut Cells" instead. Microsoft has a support article that describes these steps and other copy/paste options.

In the Mac Version it's:

enter image description here

enter image description here

  • No Idea how I missed that the first time I looked. Extra Note: in mac version it's Insert... – Iancovici Sep 27 '13 at 13:25
  • @echad Good point; I only have access to Windows at the moment, but I remembered it being the same or similar on a Mac. I updated the answer with a brief note to that effect. – John Bensin Sep 27 '13 at 13:35
  • @echad Thanks for the new screenshot. It would have been a while before I could get access to a Mac version of Excel. – John Bensin Sep 27 '13 at 13:45
  • 1
    FYI, the cells have to be in the same spreadsheet for this to work, else it will just replace them and you won't get the insert option. – user321224 May 7 '14 at 20:43
  • 2
    To add Eduardo's explanation: to paste from external source workaround, just paste it in another cell or sheet first, then copy-and-paste it again. – null Jun 3 '14 at 4:15
2

To fulfill this step with a keyboard shortcut, check out this article.

There are two options that I am aware of and both (unfortunately) require two steps.

Option 1

  1. With a single cell selected, hit Shift + Space to select the row.
  2. Hit Control + Shift + + (Plus Sign) to insert a row above the current row.

Option 2

  1. With a single cell selected, hit Control + Shift + + (Plus Sign) to insert a row.
  2. Hit Enter to accept the default of Shift Cells Down.

If inserting many rows at once, I think the first option is the best since you can repeat the second step without having to re-select the row.

This inserts a new empty row. To copy the row, you just need to hit Ctrl+C between the steps one and two. This way the content gets automatically pasted after step two.

1

I was also facing same problem; I came up with this solution: while doing copy and paste follow these steps:

  1. Instead of selecting only particular bunch of cells, select whole rows before copying. This can be done by dragging the mouse in the left side number list.
  2. You can go and paste it anywhere. And this time, you will be amazed that there's no overlap.
  • The tip to copy the whole rows helped me - Excel was complaing that I couldn't paste into an existing table - it was a structured references table - but it worked if I copied the whole rows. – entonio Feb 2 '18 at 18:42
1

So, this option works great, but only if you're working within the same workbook. I ended up here because the "Insert copied cells" option does not exist if you're trying to paste rows/cells from another workbook.

The simplest workaround I found was to copy the rows or cells you want from the one workbook, then paste them in a new blank sheet of the workbook you want to ultimately insert them into, the re-copy them from that new sheet, right-click the location you want to insert them on the worksheet, and the "Insert copied cells" option should now show up. Choose that, and you're done!

  • I find it strange you used the exact same text as this answer just 5 minutes later. Since it is a good answer I won't downvote but you should credit the original author imho. – Madmenyo Nov 6 '17 at 8:15
0

Sort them according to color or text and when they are one row, cut and paste it in desired location. Blanks can then be removed. This logic applies to major cases

0

Excel for Mac 2011:

(This is a bit of an aside -- for moving without overwriting, rather than copying). Use shift-drag, i.e.

  • select the cells you want to move
  • keeping the shift key pressed, drag the cells
  • drop the cells where you want (and then release the shift key)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.