To insert blank rows in an Excel spreadsheet is a relatively straightforward process, as is inserting a block of consecutive rows, but what if you need to insert them alternatively? If it’s a small spreadsheet, than that’s fine, you can work your way down it, but what if the spreadsheet has already got lots of rows of data (as the one I had to work with earlier this week had – well in excess of 100 rows)? That can be more challenging! You could write a macro or VBA to do it, but I used a little trick that I’ll share.

### Inserting a blank row

To insert a single blank row, then the easiest way is to right-click on the row below where you need the new one to be & select Insert from the menu that appears…

Select Insert from the dropdown

New row is inserted with all rows below renumbered accordingly (& formulas adjusted)

### Inserting multiple contiguous rows

If we’re needing to insert a contiguous (connected) block of rows, we can take the same approach as above, however, first of all, we need to select how many rows we want to insert

### Inserting alternate rows

As I mentioned above, earlier week, I needed to insert some alternate rows, so I did a quick cheat. I could have selected each row of data individually & then followed the above process, however, with well over 100 rows, that would have taken a long time.

So, the first step was to insert a new column (I could have just used the next blank column to the right of my list, but as I wanted to then take advantage of the numbers I’d be creating, it seemed more logical to insert the column.

Once I’d done that, I typed a “1” next to the first row, a “2” next to the second row & then auto-filled them to the end of the data…

I typed in the 1 & 2 so that Excel would know what the interval between the numbers needed to be (If I’d used 1 & 3, then the sequence would have been 1, 3, 5, 7, etc)

Having copied these down, I then moved back to the start of this numbered list & selected the whole set of data (CTRL+SHIFT+END

Then, from the HOME Tab, I selected Sort & Filter & then Custom Sort & told Excel to sort by Column A (as I’d not given that a name)

The result of this is that the first column is now sorted resulting in blank alternate rows.

In my example, I needed 4 blank rows, so I simply repeated the copying of the numbers down so that there was one set matched to the data & 3 sets with no data…

Of course, you wouldn’t leave the data with blank rows like that, as Excel doesn’t work very well with blank rows. The reason why I needed the additional rows was to allow several rows of different data to be collated & because of the number of columns of data involved (and the fact that there was no additions involved) a Pivot Table couldn’t easily help.

So, a quick way of populating the blank rows with the same information from the “real” rows above is (again, whilst the range is still selected following the sort), to press the **F5 button**, select **Special>Blanks**

When the blank rows only have been selected, in the active cell type in a formula to select the cell immediately above the active cell (in this example, the active cell (where the cursor is) is B3, so the formula needs to be “=B2″…

… then use CTRL+ENTER to apply this formula to all of the cells that have been selected