Copy-paste rows into an Excel spreadsheet without overwriting existing content below

Question

This one seems to be trivial, but I cannot find a solution, and it is a major workflow obstacle for me when working with Excel 2010:

Imagine you have two Excel worksheets, Sheet A with 1000 rows, Sheet B with 50 rows or so. You want to copy all the 50 rows from Sheet B to Sheet A so that the new rows are on top (!!!) of Sheet A, but without overwriting existing data in Sheet A.

For doing this, you have first to add 50 rows on top of Sheet A (including a double-check to make sure you have really 50 and not 49), and then copy-paste your 50 rows from Sheet B into Sheet A (pointing at Row 1). This works, but it is time-consuming and nerves-eating.

Without adding 50 empty rows first, the 50 new rows will overwrite existing data in Sheet A.

One would suppose there must be a way to tell Excel to add the 50 copied rows on top of the existing content in Sheet A and just, as part of the process, add the same number of rows, so that no existing data will be overwritten.

I do not find a way to accomplish this. Of course, I could write a VBA macro for this, but is this really necessary? Or is there a simpler, by-default way to do this manually?

Note: For simplicity, I described the problem with entire rows to be copied. However, the problem refers also to copying a block of cells (such as B3:F53). When pasted into Sheet A, they should push down the existing rows (yes, entire rows, not cells from selected columns) rather than overwrite existing data in the rows below the cell marked as insertion point.

Answer 1

The following steps should work:

• Copy the cells from Sheet B to the clipboard
• Highlight the row where you want your data to be inserted (ex. if you want it inserted at the top, highlight row 1)
• Right click and select "Insert Copied Cells"

It should then give you the option to shift the existing data where you want it to go.

Answer 2

So, 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!