Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. What is the shortcut to ONLY select the visible cells? Guess, then click 'See Answer' as it can be done.
2. How do you take all the formatting from chart (see 1 below) and PASTE it onto another chart (2)? Guess and then see how it can be done with 'See Answer'



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.

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.

insert a row between each row in Excel

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

insert a row between each row in Excel

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.

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.

Move and insert an Excel column (quickly)

Invert/ Reverse COLUMNS in Excel