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