How to insert empty rows in Excel 2013 when I have copied rows in my clipboard?

Question

This seems like something which should be painfully simple to do but I can't figure it out. So with an empty clipboard I can right click on a row header and say insert row, no problem, it inserts a blank row. But now let's say I go to another spreadsheet and select 4 rows, copy those, then go back to that other spreadsheet and do the same as described above, the only option I have is to insert copied rows. What I was to do is to insert a couple blank rows first to make a little extra space than what will be created by inserting them and then insert my copied rows.

Answer 1

Unfortunately it is not really possible: you can achieve insert blank rows, even if you have copied some rows by clicking on Home->Insert->Insert Sheet Rows:

This will insert the blank lines as you wish. However, it also voids your selection, i.e. the source rows are not in the clipboard anymore.

Overall however, inserting the copied rows/columns instead of blank rows/columns makes a lot of sense, as this is what one usually does right after copying. And adding blank rows afterwards is always easily doable.

I personally found it most efficient to do this with keyboard shortcuts and a few advanced tweaks:

Step 1: Select source

• Ctrl-Space - Select column
• Shift-Space - Select row
• Shift-Space - ArrowKey - Extend selection to get multiple rows/columns

Step 2:

• Copy (Ctrl-C) or
• Cut(Ctrl-X) or
• Delete (Ctrl--)

Step 3: Insert

• Select target row/column similar to step 1
• If only one column/row is selected, Excel will insert the source as selected. However, multiples of the selected rows/columns can be selected and will then be filled, e.g. if you copy 3 rows and select 12 rows, the it'll be filled 4 times
• Either insert (Ctrl-+) new columns/rows, or overwrite existing one (Ctrl-V)

Esp. Ctrl-Space, Ctrl-X and then Ctrl-+ is extremly usful to quickly move columns/rows!

Answer 2

This is not possible. Excel will always ask you to paste inserted rows. Also doesn't seems logical.

Answer 3

I noticed that the version I'm using (2007) was also doing this while the clipboard had cells in it. However, I noticed that if I deselect the cells by typing anything anywhere first, the regular right click insert option which adds empty cells returns! (how bizarre)

Hope this helps someone

Answer 4

Because of the context sensitive nature of Excel, the only way to consistently insert rows is to create a custom group on the ribbon and put the Insert Sheet Rows button in there.

I use Office Standard 2010 and in this version if there is anything in the clipboard (cell contents, etc.) then the context menu can be affected.

Here’s how I put it all together. Google search EX1969MH for all the pictures.

1: Right click the ribbon and choose Customize the Ribbon

Image

2: Now create a New Group – click the new group button

Image

I like to click the down arrow to move my group so its in the Insert tab.

3: Click Rename and Call it what ever you want, example missing functions / obvious functions / “Power User”

Image

4: Add the Insert sheet Rows Command From Popular commands ( aww – i thought it was going to be in power user commands) choose Insert Sheet Rows then click the Add >> button.

Image

Click OK then

5: Enjoy your new button on the insert tab.

Image

Answer 5

In Excel 2016 and possibly in the older versions as well, in order to switch the context menu from displaying "Insert Copied Cells to "Insert", do the following:

1. Select the row or column where you want to insert blank rows/columns.
2. Press the Escape button
3. Right-click on the selected row/column and the context menu should now have the "Insert" option.