Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
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 Albert Feb 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. –  techturtle Feb 13 '13 at 21:53

4 Answers 4

up vote 2 down vote accepted

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-ArrowKeySpace - 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!

share|improve this answer
    
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! –  unc0nnected Feb 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. –  SnakeDoc Dec 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 –  unc0nnected Feb 5 at 1:04

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

share|improve this answer
    
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 –  unc0nnected Feb 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. –  SnakeDoc Dec 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

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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