10 October 2009

Insert multiple blank rows between existing rows in Excel

Microsoft’s Excel is an indispensable tool for the work place, thanks to it's multitude of features, and will no doubt remain so at least for the near future. I also use it on a regular basis and generally consider myself an advanced user. Even so, given it’s complexity, it never hurts to find out new tricks to speed up your workflow or to change it altogether.

One of my sources of information is the Microsoft Office blog at TechRepublic. One of their recent articles detailed a workaround for adding several blank rows between rows already filled with data at once, without repeating the right-click-Insert routine for each new row. While the idea is ingenious, it’s by far not the easiest way to do this in Excel.

As usual, the simplest solution is not obvious from the beginning. Here, the trick is to select all the rows in the spreadsheet where you need new blank rows separately before inserting. To do that hold down the Ctrl-key before clicking on the row placeholder on the left side of the spreadsheet and with the key still pressed select the other rows one by one. Only after you have finished selecting the rows you should right-click and use the ‘Insert’ command. Or, better yet, use Ctrl++ from the keyboard for the same effect. Microsoft Excel insert new rows between existing

Done! No need to reserve an empty column, type in numbers or use sorting. This method is also more flexible, because the discontinuous selection with Ctrl allows you to choose any rows in the sheet, not only the adjacent ones. But the method presented by TechRepublic could be successfully used in online spreadsheet apps, like Google Docs, where you can’t make discontinuous selections.

As with single rows inserts, Excel adds them above the selection, so keep that in mind when you choose the initial selection. The same trick works with columns too, and it adds them to the left of the existing ones.

While I don’t have a copy of Office 2007 to test this, it should work there as well, because the new version didn’t change the keyboard shortcuts, only the menus.

9 comments:

  1. Your solution sounds much simpler than TechRepublic's, but I can't make it work. I get an error message "Cannot use that command on overlapping selections" or "That command cannot be used with selections that contain entire rows or columns, and also other cells. Try selecting only entire rows..." in Excel 2007. Any ideas?

    ReplyDelete
    Replies
    1. It's still working for me as described under Office 2007 as well. You probably selected a regular cell along with the row/column, when I do that I get the same error message. Try to first select a row, then hold down CTRL and with CTRL down select the other rows one by one. That should work.

      Delete
  2. Thanks for your reply. I got it to work. I was highlighting the first row, then holding down Ctrl, then holding down the left button to highlight all the rows, including the first row. You need to click on the first row, hold down Ctrl, then, omitting the first row, click on each subsequent row. And each subsequent row needs to be clicked on individually, which can be kind of awkward when I have thirty rows to click on. So, am I correct that each row needs to be clicked one at a time, and that only one blank row is added between the data-filled rows? I usually have thirty rows and need three or four blank rows placed between them. In my case, it seems that I still need the TechRepublic method. Am I correct?

    ReplyDelete
    Replies
    1. Yes, that's pretty much the procedure. It does not work, indeed, if you want to insert multiple rows after the selection, mainly because Excel only inserts the number of rows you have selected - in this case you basically select one row (the extension of the selection with Ctrl doesn't count). You could select 3 rows at a time and use insert, this way Excel will also insert three rows, but I'm not sure if this helps you. I think for your specific case you would need to write a macro.

      Delete
  3. Brilliant. Just..brilliant.

    ReplyDelete
  4. and if i wana do it for big no of rows?

    ReplyDelete
    Replies
    1. Then you can either use the method linked here from TechRepublic (basically you add another column with odd numbers next to your table, then add the even numbers below them and finally sort the entire table by that column) - or you can write a macro for your specific use case.

      Delete
  5. Thanks! If you want to insert multiple copies of a line, first make one copy and insert it where you want. Then click on its row number, and while holding the mouse button down hit and then drag the mouse down as far as you want. Release the left mouse button, click the right mouse button and select 'Insert'. This will create as many blank lines as you wanted. Then, select all the cells in the row you wanted to copy, and grab the right bottom of that group and drag down to fill the empty lines you created. (this last part is the standard Excel duplicate function)

    ReplyDelete
  6. There is a utility "add-on" available that handles this in 4 mouse-clicks. Google "ASAP excel". It's an "add-on" and it adds a tab to the tool ribbon in excel. There's a free trial, and all 4 of us in my department were authorised for the full version because it was easy to demonstrate how much time we would save. The more we explore it the more cool stuff we find (like insterting multiple rows).

    ReplyDelete