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.

  • 1
    I don't think it is possible (though I'm happy to learn the opposite): if you copy some rows (same applies to columns or even cells), Excel assumes that upon inserting you want to paste the copied range and proceed to do this. Either you clear the selection with Esc - or you insert the blank lines before/after the copy&paste...– Peter AlbertFeb 13 '13 at 20:45
  • If you have Excel data in the clipboard, insert will automatically make room for those rows and then paste them for you. If you want to insert a different number of rows, you will have to clear the Excel data in the clipboard by pressing Esc first. If this is not what you wanted, perhaps you could clarify your question.– techturtleFeb 13 '13 at 21:53

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:

enter image description here

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!

  • Thanks Peter, I'm making the transition from Libre Office because of how tired I got of it crashing 10 times a day and overall have been fairly surprised at how many features that add convenience to my day to day spreadsheeting are missing from Excel. This would be one of them, simply adding a second option in the context menu when yuo right click to insert rows and then insert copied rows under this would solve this and yet one of the largest companies in the world can't or won't do it. Boggles my mind. Either way I've put the insert row sheet up into my quick bar at the top so I'm good!– unc0nnectedFeb 15 '13 at 23:09
  • @RyanWiancko if Libre Office is crashing on you 10 times a day, you have a much bigger problem than just switching to Excel. Libre Office is 100% stable now.– SnakeDocDec 12 '13 at 19:32
  • I really can't agree with you there, Libre office is far far from stable when you start working on very large and complex spreadsheets– unc0nnectedFeb 5 '14 at 1:04

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

  • 1
    doesn't seem logical to simply office the user the choice? How so? What if I copied something and then realized I didn't actually want to paste it afterall? there's countless situations where I would want to insert blank rows with something in my clipboard– unc0nnectedFeb 15 '13 at 23:10
  • they should at least offer a way to easily purge the clipboard if they are going to enforce this illogical behavior.– SnakeDocDec 12 '13 at 19:33

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

  • Still working in Excel 2016 :)– raksliceApr 6 '17 at 20:47
  • You can also empty the copy buffer by pressing Esc, or by double-clicking in a cell.– raksliceApr 6 '17 at 20:52

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

    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.
    • 1
      Note that this clears the clipboard as well.– DarkMoonAug 12 '16 at 5:21

    Your Answer

     
    discard

    By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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