How to insert a row between each row in Excel when you don’t want to manually go to each row and click insert.
Table of contents
YouTube Insert a row between current rows in Excel
Watch, like, subscribe
As shown below we have some data and we want to include rows between each row of data. This one is very small so we could do it manually (highlight a single row, right click and insert) but pretend there are hundreds of rows.
Step 1- Create helper column
First thing to do is to create some helper cells (column D) and note that we have filled the series with odd numbers from 1 to 11.
Step 2- Add enough ‘blank’ rows beneath your list
Below this we can now create the even numbers as shown below. To make it more presentable, in those ‘blank rows’ I have coloured them (to create alternate coloured rows and therefore make it easier to read) and included some dashes (perhaps we need to upload it to a system and this is what it needs to look like).
Step 3- Sort the list based on the helper column
Now instead of working out how to insert a row between each row in Excel, we are going to move the rows around by sorting on the series we created. So this is less an insert of rows and more a re ordering.
Below, we can sort the list based on column D from smallest to largest.
The end result looks like we have had to manually insert a row between each row in Excel, but it is done quickly and accurately.
What if you want to insert a blank row every third row in excel
If you need to insert the blank row every third (or fourth or fifth etc), you just need to play with the series in column D. If you were doing every third row, then the initial series (with the full rows) would be 1,2,4,5,7,8. The blank rows would be numbered 3,6,9. Then you can perform the same sort and it will look like you inserted a blank row every third row.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.