How to Insert Every Other Row in Excel: 3 Best Ways

One of the most common tasks in Excel is inserting rows, but what if you only need to insert every other row? I show you how in quick and simple steps in this article.

Microsoft Excel is the gold standard for data organization and interpretation. However, the data organization part could be tiresome if you need to insert every other row or every nth row in any spreadsheet, including Excel. However, you can do this easily and automate the whole part if you keep reading this article until the end.

Reasons to Insert Every Other Row in Excel

You might ask why on earth would you need to insert blank rows in Excel every other or every nth row. Find below some reasons to learn this essential Excel skill:

• By learning this trick, you can create space for additional data under each row without disturbing the existing data structure.
• When you add a blank row after every row in Excel, you increase the readability of the data you’re presenting.
• You can also group related datasets in rows by inserting a blank row between two different groups.
• A blank row after every other row in Excel can work as a delimiter of the dataset.
• This trick helps you to insert formulas or functions in alternate rows that can reference data from the rows above or below.
• You can easily insert headers or titles in between rows when there are blank rows after every other row or the nth row.
• Blank rows enable you to apply styles or formatting to specific rows or groups of rows without affecting the rest of the data set.

Insert Every Other Row Using Mouse

If you’re dealing with a small Excel database and need to enter empty rows between every other row, you can just use some mouse movements as outlined below:

1. On your Excel sheet where you’ve got a dataset and need to insert blank rows between every other row, select the second row below the column header.

2. For example, if the header row is A1:E1, click on row 3.

3. Now, press the Ctrl key and then select the rest of the rows, like row 4, row 5, row 6, and so on.

4. After selecting the last row, right-click and select Insert on the context menu.

5. Excel will add a blank row between every other row.

6. To group two or more rows by separating the groups using a blank row, select rows in this order: row 4, row 6, row 8, and so on.

7. Now, right-click and select Insert to add one blank row after every three rows containing data.

It’s a pretty basic method and good for you if you’re an entry-level Excel user. Remember, you need to tactfully select the rows using a Ctrl+ Left-click. When you select a row, right-click, and hit the Insert button, Excel inserts a blank row above the selected row. Accordingly, you must plan the Ctrl + Left-click selections factoring in the count of rows in a group.

Insert Every Other Row Using a Helper Column

You can use the Sort feature of Excel to insert a blank row between every other row in Excel. However, you’ll need to take the help of a helper column. Find step-by-step instructions below:

1. After the last column header in your Excel worksheet, add the Helper column as shown in the above image.
2. Now, create a serial number for all the rows under the Helper column by typing 1,2,3, and then filling the entire column by dragging the fill handle.

3. Then, copy the serial number as is and paste it into the cell just below the last serial number, which is the number 10 in the above example.

4. Select the entire cell range below the column header. For example A2:F24 in the current tutorial.
5. Click the Sort & Filter button on the File tab inside the Editing commands block.
6. Hit the Custom Sort button.
7. Inside the Sort dialog box, choose Helper as the Sort by Column and Order should be the Smallest to Largest.
8. Hit the OK button to apply the sort command.

Performing all the above steps in an orderly fashion will enable you to put a blank row between every other row in your Excel sheet. Once you’ve successfully inserted the rows, delete the Helper column to get rid of the undesired column and row data.

Insert Every Other Row Using a VBA Script

Suppose, there are hundreds of rows between which you need to add blank rows. Following the above methods may not be an economical decision. You must rely on a VBA script to automate the whole task.

A VBA code also lets you club the action with other tasks on the same Excel worksheet or workbook and automate many manual steps. So, find below a simple VBA script that you can use to insert every other row in Excel:

1. Go to the Excel worksheet where you’ve got a database for analysis.
2. Click the File tab and choose Save As from the left-side navigation pane.
3. Now, choose Browse and select any local directory to save the Excel workbook.

4. When you see the Save As dialog box, click the Save as type drop-down list and choose the Excel Macro-Enabled Workbook option.
5. Click the Save button to save the file in XLSM format. This file type allows you to store and run VBA macros in an Excel workbook.

6. Now, hit the Alt + F11 keys together to open the VBA Editor.
7. There, click the Insert tab and choose Module from the context menu.
8. In the new Module that pops up inside the grey VBA editing window, copy and paste the following VBA script:

Sub InsertEveryOtherRow()
Dim rng As Range
Dim CountRow As Integer
Dim i As Integer
Set rng = Selection
CountRow = rng.EntireRow.Count

For i = 1 To CountRow
    ActiveCell.Offset(1, 0).EntireRow.Insert
    ActiveCell.Offset(2, 0).Select
Next i

End Sub

9. Click the Save button on the VBA Editor toolbar and hit the Close button.
10. Now, click the first row that contains the data just below the first column header.
11. Hit Ctrl + Shift + Down Arrow to select all the rows in your dataset.
12. Press Alt + F8 to open the Macro dialog box.

13. There, select the InsertEveryOtherRow macro and hit the Run button.

14. The VBA script will transform the rows by inserting one blank row in between two rows containing data.

That’s it! You get a blank row in between every other row in Excel. If you need to insert an empty row in between every other nth row, where n=2, 3, 4, etc., use the following VBA script:

Sub InsertEveryOtherRownthRow()
Dim rng As Range
Dim CountRow As Integer
Dim i As Integer
Set rng = Selection
CountRow = rng.EntireRow.Count

For i = 1 To CountRow / 2
    ActiveCell.Offset(2, 0).EntireRow.Insert
    ActiveCell.Offset(3, 0).Select
Next i

End Sub

In the above script, modify the row count in the code element CountRow / 2 to 3, 4, 5, etc. You also need to increase the values for the following code elements by 1 if creating a group of 3, by 2, if creating a group of 4, and so on:

• ActiveCell.Offset(2, 0)
• ActiveCell.Offset(3, 0)

For example, replace the code element with CountRow / 3, ActiveCell.Offset(3, 0), and ActiveCell.Offset(4, 0) to group 3 rows and put a blank row in between two groups of rows.

Conclusions

Give the above methods a try and you should be able to organize rows more effectively by adding a blank row in every other row on Excel.

The manual methods, like mouse, clicks with Ctrl and Sort-based methods, are suitable for medium-sized Excel sheets. Contrarily, you can rely on the VBA script for a larger database. The VBA macro also automates the process of adding a blank row after every nth row in Excel.

Don’t forget to mention your experience after trying out the above methods in the comment box below.