How to Insert Blank Rows in Excel Between Data

Trying to add blank rows between your data in Excel can feel like a simple task, but doing it efficiently - especially with hundreds or thousands of lines - is another story. Whether you're trying to improve readability, add subtotals, or format a report for printing, manually inserting rows one by one is a recipe for frustration. This tutorial walks you through several methods to insert blank rows into your spreadsheet, from a quick keyboard shortcut to a powerful automated script.

Method 1: The Quick and Manual Way

For small datasets, there's no need to overcomplicate things. Manually inserting a few rows is the fastest approach. Excel offers a couple of simple ways to do this.

Inserting a Single Blank Row

To add one blank row, just follow these steps:

1. Click on the row number where you want to insert a blank row above it. For example, to insert a row between row 4 and row 5, select row 5. This will highlight the entire row.
2. Right-click the highlighted row number and select Insert from the context menu.

A new, empty row will instantly appear above your selection.

Keyboard Shortcut: For an even faster method, first select the row (Shift + Space), then press Ctrl + + (the plus sign). On a Mac, the shortcut is Command + Shift + =. To delete a row, the shortcut is Ctrl + - (the minus sign).

Inserting Multiple Blank Rows

If you need more than one row, you don't have to repeat the process. Excel figures out how many rows to add based on how many you select.

1. Click and drag your mouse over the row numbers to select the number of rows you want to insert. For instance, if you need three blank rows, highlight three existing rows.
2. Right-click anywhere in the highlighted area and choose Insert.

Excel will insert the same number of blank rows above your selection. If you selected three rows, three new ones will appear.

This manual method is perfect for quick adjustments, but it quickly becomes impractical when working with large tables where you need space added between every single line of data.

Method 2: Use a Helper Column and Sort

When you need to insert a blank row between every row of existing data, the "helper column" technique is a classic Excel trick. It seems strange at first, but once you understand the logic, you'll see how clever and efficient it is. It might take a minute to set up, but it saves an incredible amount of time with large datasets.

Step 1: Create a Helper Column

First, you need a temporary column to help with the sorting process. If your data is in columns A and B, insert a new column in C. You can call it "Helper" or "Sort Order" for clarity.

Step 2: Add a Number Series

In this new helper column, you need to create a simple number sequence next to your data.

1. In the first cell of the helper column (e.g., C2, assuming C1 is a header), type the number 1.
2. In the cell below it (C3), type 2.
3. Select both cells (C2 and C3). You'll see a small green square at the bottom-right corner of your selection. This is the Fill Handle.
4. Double-click the Fill Handle. Excel will automatically fill the number series down to the last row of your data. You should now have 1, 2, 3, 4, and so on, for every row.

Step 3: Duplicate the Number Series

This is where the magic happens.

1. Select all the numbers you just created in the helper column.
2. Copy them (Ctrl + C or Cmd + C).
3. Click into the first empty cell directly below your last number.
4. Paste the copied numbers (Ctrl + V or Cmd + V).

Your helper column should now look something like this: 1, 2, 3, 4... down to your last data point, followed immediately by another 1, 2, 3, 4...

Step 4: Sort by the Helper Column

Now, you'll use this special column to rearrange your data.

1. Click anywhere inside your dataset.
2. Go to the Data tab on the Ribbon and click the Sort button.
3. In the Sort dialog box, under "Column," choose your helper column (e.g., "Sort Order").
4. Under "Order," make sure "Smallest to Largest" is selected.
5. Click OK.

Excel will sort everything based on your helper column. Since there are now two rows numbered "1" (your original first row of data and an empty row), two rows numbered "2," and so on, the sheet will reorganize to have an empty row after every single row of data.

Step 5: Clean Up Your Worksheet

You now have your blank rows, but the helper column is still there. Simply right-click the helper column's header and select Delete to remove it. Your data is now perfectly spaced.

Method 3: Insert N Blank Rows Between Data

What if you need more than one blank row between each line item? You can easily adapt the helper column method to insert any number of blank rows (two, five, ten, etc.). For this, we'll use a slightly more advanced sorting approach with two helper columns.

1. Set Up Helper Columns: Create two empty helper columns next to your data. Let's call them "MainSort" and "SubSort."
2. Number The "MainSort" Column: Just like in Method 2, fill the "MainSort" column with a series of numbers (1, 2, 3...) corresponding to each row of your data.
3. Fill the "SubSort" Column for Data: For all rows that contain data, put the number 1 in the "SubSort" column. This marks them as the primary rows.
4. Create Additional Number Sets: Below your main data block, create series for your blank rows. For example, to insert two blank rows between data:
5. Perform a Multi-Level Sort: Go to Data > Sort.
6. Delete helper columns after sorting: remove "MainSort" and "SubSort."

This will insert multiple blank rows between your data rows.

Method 4: Use a VBA Macro for Automation

If you have to add blank rows regularly, setting up helper columns every time can become tedious. This is a perfect job for a VBA (Visual Basic for Applications) macro. With a macro, you can insert blank rows with a single click. Even if you've never written code, you can easily copy and paste this script.

Important: Always save a backup copy of your file before running a macro for the first time, as the changes it makes cannot be undone with Ctrl+Z later on.

Step 1: Open the VBA Editor

Press Alt + F11 on Windows or Option + Fn + F11 on Mac to open the VBA Editor. It will open in a new window.

Step 2: Insert a New Module

In the VBA Editor, go to the menu at the top and click Insert > Module. A blank white code pane will appear on the right side.

Step 3: Copy and Paste the Code

Copy the code below and paste it into the module window. This particular macro will insert one blank row between every existing row of data.

Sub InsertBlankRowsBetweenData()
    'Turn off screen updating to make the macro run faster
    Application.ScreenUpdating = False
    
    Dim lastRow As Long
    Dim i As Long
    
    'Find the last row with data in the active sheet
    If IsEmpty(ActiveSheet.Cells(Rows.Count, 1).End(xlUp)) Then
        lastRow = 1
    Else
        lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    End If

    'Loop backwards from the last row of data to the second row
    For i = lastRow To 2 Step -1
        'Insert a new row at the current position i
        Rows(i).Insert Shift:=xlDown
    Next i
    
    'Turn screen updating back on
    Application.ScreenUpdating = True
End Sub

The code works by finding the last row of your data and then looping backwards up the sheet, inserting a row at each step. Looping backwards is critical, if you were to loop forward, you'd end up in an infinite loop and inserting rows forever!

Step 4: Run the Macro

1. Close the VBA Editor to return to your Excel sheet.
2. Press Alt + F8 (or Option + F8 on Mac) to open the Macro dialog box.
3. You should see InsertBlankRowsBetweenData in the list. Select it and click Run.

The macro will execute instantly, and your worksheet will be formatted with alternating blank rows.

Final Thoughts

Excel gives you plenty of ways to manipulate your data, and adding blank rows is no exception. For small jobs, manual insertion is fastest. For larger, one-time tasks, the helper column and sort method is incredibly effective. For repeatable formatting tasks, a simple VBA macro can automate the entire process down to a single click, saving you time and effort.

Mastering these Excel tricks is great for data prep, but it's often a symptom of manual reporting - a process that drains hours every week. Instead of constantly exporting data to Excel and fighting with formatting, we built Graphed to connect directly to your data sources. We transform those manual steps into a simple conversation where you just ask for the charts and dashboards you need, giving you back the time you used to spend wrangling rows and columns.