Quickly add a blank row between multiple rows of data in an Excel spreadsheet

Inserting a new row into a sheet is an easy task:

• Right-click a row number and select Insert from the resulting context menu.
• Or choose Rows from the Insert menu.

Either way, Excel inserts a row above the current row or cell. To insert multiple rows, select multiple rows before inserting. For every selected row, Excel will insert a new row.

Inserting a row between several existing rows would certainly be a tedious job if you inserted each row this way — individually. Fortunately, there’s an easier, but roundabout, way to insert blank rows between existing rows:

1. First, you need a blank column adjacent to your data. For instance, if the spreadsheet comprises A1:E11, you could use column F or insert a column to the left of column A.
2. In the first cell adjacent to the data (not the headings), enter the value 1.
3. In the cell just below 1, enter the value 2.

   
   

4. Select the values 1 and 2 (cells F2:F3 above) and double-click the fill handle. Excel will auto-fill the cells in column F.
   

5. Next, press [Ctrl]+ C to copy the values in column F (F2:F11) to the Clipboard.
6. Select the first blank cell in the auto-fill column (F12) and press [Ctrl]+V to copy the auto-fill numbers from F2:F11.

   

7. Click Sort Ascending to sort the spreadsheet by the auto-fill values in column F. (You shouldn’t have to select anything since cells in column F are already selected.)
8. When Excel prompts you to extend the sort selection, click Expand The Selection and click Sort.
   
   

The result of the sort is a blank row between each of the existing rows. Excel simply sorts by the second set of auto-fill numbers (the result of the copy task), so it isn’t a true insert task, but the result is the same. To insert two blank rows between each existing row, copy the copy values a second time (step 5), and so on.

If the rows below the spreadsheet contain data, insert the necessary number of rows below the spreadsheet before beginning. For example, if you have 10 rows of data and you want to insert one blank row between each, you will need to insert 10 rows below the spreadsheet to accommodate the copied values.

I recommend that you save your workbook before attempting this change, just in case something goes wrong and you need to start over.