How to insert a row between each row in Excel

How to insert a row between each row in Excel when you don’t want to manually go to each row and click insert.

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.

Insert a row between each row in Excel- Step 1

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.

insert a row between each row in Excel

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).

insert a row between each row in Excel

Insert a row between each row in Excel- Step 2

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.

insert a row between each row in Excel

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.

insert a row between each row in Excel

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.