Software

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

Inserting a blank row between existing rows of data in an Excel spreadsheet can be a tedious task if you insert each row individually. Fortunately, if you know this trick, inserting rows between existing rows is a snap.

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.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

5 comments
navyking
navyking

Awesome, What thinking. It helped me a lot. thanks a ton to you.

ThreeLittleBirds
ThreeLittleBirds

I like this feature. In one of my departments, each month there is a need to insert rows between multiple ranges of data. I know staff is taking an inefficient way doing this (inserting one row at a time) so this feature will be a tremendous help. Now comes the hard part - getting staff to stop doing things "the old fashioned way" because that's the way they've always done it!

navyking
navyking

Awesome, What thinking. It helped me a lot. thanks a ton to you.

ssharkins
ssharkins

I'm glad you'll be able to apply this to something immediately -- here's hoping your staff cooperates!