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. 
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.
23 comments:
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?
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.
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?
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.
Brilliant. Just..brilliant.
and if i wana do it for big no of rows?
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.
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)
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).
Thanks i just tried that and it worked. However i have a challenge, im working on a worksheet with more that 2000 rows which i need to insert additional rows i between... clicking each row one by one is quite tedious, is there a quicker solution?
In that case you can either use the original technique described on TechRepublic or write a quick macro in VBA for the task - that's probably the most efficient method.
need help please? entering over 1 one hundred thousand Karaoke songs on a spreadsheet (to use for song lists) and I ran out of rows (there are only 16,384 rows allowed). Now what can I do to add more rows so I can add more songs and be able to sort alpha? Anyone now exactly how I can fix this as I have already entered 16,384 song titles and have a lot more to add? Please help
You should switch your file format to the new Excel format (.xlsx), it supports over 1 million rows.
Thanks so much - I just knew there was a simple explanation out there!
The error happens when you click an already highlighted row when inserting a new row. When selecting the row, try clicking the number to highlight the entire row when the black arrow appears. Click while pressing ctrl one row at a time. This is time consuming though. Now I'm trying to install kutools for excel app
Hi George. I need to do this. For instance: I have a list of 100 names oriented vertically all consecutively; I need to add 15 lines between each of the names. Is there a way to add the same 15 lines below each name with a command all at the same time? Or at least an easiest way other than adding them for each name at a time?
Thank you.
Hi George. I have a list of 50 names sorted vertically/consecutively and I need to add 15 rows below each name. How can I do that in a simpler way other than having to repeat the 15-lines-insert for each name one by one? Thank you.
Hi Andres,
I'm not sure how familiar are you with VBA in Excel, but it seems the easiest way to do this here - basically writing a small piece of code to do the manual job for you.
Very nice trick and really its working just you have to exactly right click on left row of the spread sheet contains rown numbers
Nice work done beautiful article
I'm not getting insert option once I select multiple rows. Plz help
its superb man its working for me
Superb trick yaar. Thanks for the guidance.
Post a Comment