Excel 2007 or 2010 copy/paste without merging cells & with shift all down

Question

Let say I have an excel sheet with structure like

I select a few cells (at one column) and click Ctrl+C. After that I want to paste it. I select one cell(B116):

And click Ctrl+V. It gives me an error:

Excel think that I need to copy just one column or merge it. But I want excel just to create a few rows and moving all content down. How can I do it?

Answer 1

What you are trying to do, confuses Excel. When you paste an array by selecting one cell (and the pasted area goes into a merged cell), Excel does not know how you want the information pasted into the cells. If you highlight the same number of cells as the copied array, Excel will not have a problem and will paste the information.

Answer 2

Copy the rows you want added, then use CONTEXT MENU, E to Insert Copied Cells. In this case, since you have the rows copied and not just the cells, Excel will insert the copied rows into your table while retaining the formatting and structure of your table as-is. Then, select the cells you want to be blank and simply hit DEL.

The reason you want to do the above rather than only insert the cells you want copied is because by inserting only those cells, you need to displace the original gride either down or to the left; either of these will break your current table's structure and/or subsequent row alignments. By inserting the whole row, you retain the structure and formatting and can easily change the cell's contents/data as desired. You can also enforce a habit of leaving a few blank rows near the top or bottom for the purpose of having a "template" row to copy from. Alternatively, after you insert your rows, you can copy from another cell and paste that directly instead of inserting, since your table structure is already modified (i.e., don't insert again the individual cells, as this would again displace them).

PS: If you have cells on the right-hand end of the rows that you did not want copied over (including formatting, such as cell borders, etc.), then after you insert the rows, you can just highlight them and enter CONTEXT MENU, D, then choose to displace laterally.