CODEWITHSUNDEEP
excel
Christian Geiselmann
Christian Geiselmann

Reputation: 622

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

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.

Upvotes: 11

Views: 78210

Answers (7)

Francine
Francine

Reputation: 1

My solution is to copy and paste the cells you need to not overwrite to another blank workbook. Then copy and paste the cells you are adding. Then copy and paste the cells you put on a blank workbook back to the original workbook.

Upvotes: 0

Gordon
Gordon

Reputation: 31

Select a blank column to the right of the existing data (or insert a column to the left) and in the top cell immediately below the headers enter '51'...in the cell below '52', then select both and drag-fill so all rows have an incremental number reference greater than 50. Next insert your new data BELOW the existing data and in the number label column you inserted earlier, number the new rows 1->50, then sort your table smallest to largest to get your new data to the top, then delete your inserted column.

...

Alternatively simply select 50 rows from a blank worksheet and insert them at the top of your 1000 row table then paste your 50 rows of new data directly.

Upvotes: 0

Talsan
Talsan

Reputation: 41

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!

Upvotes: 4

manjunath
manjunath

Reputation: 1

I was also facing same problem but after that I come up with the solution for this. 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. Then you can go and paste it any where. This time you will be amazed that no overlapping.

Upvotes: 0

Philip
Philip

Reputation: 11

Do "PASTE SPECIAL" when pasting, then click on "skip blanks," it will copy of your data from spreadsheet B to A without copying over blank data.

Upvotes: 1

user2060451
user2060451

Reputation: 2688

Excel 2016. Without wasting too much time since the old function is not available anymore(!), I copied the old table/cells at the end of the working table. Then cut and insert cells. Two step process, but it takes 30 seconds.

Upvotes: 2

DHP
DHP

Reputation: 251

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.

Upvotes: 12

Related Questions