Insert Alternate Blank Rows in Excel

I have an Excel report that fills about 1,000 rows. I want to insert a blank row between each of these rows. Obviously, I know how to insert a row one at a time, but is there a quicker way to do this?

Tom Steele

You bet. You can create the blank rows separately and then interleave them with the existing rows by sorting. To start, insert a new column to the left of the existing column A. Enter 1 in cell A1 and highlight column A all the way to the last row that contains data. From the Edit menu select Fill | Series and click on OK. Column A should now contain numbers from 1 to the total number of rows. Press Ctrl-C to copy these cells to the clipboard, click in the cell just below the last of them, and press Ctrl-V to paste. Now highlight the entire data area, including the new rows with just a number in column A. Select Sort from the Data menu and choose the No header row option in the resulting dialog box. Under Sort by select Column A, under Then by select column B, and click on OK. Finally, delete column A. You now have a blank row after every one of the original 1,000-odd rows.

Because blank lines can often cause problems with charts or calculations, you may want a quick way to remove them. You can use a similar technique.

Again, insert a new column to the left of column A and fill a series from 1 to the end of the data. Highlight the entire data area and sort by column B (the first column of real data). This will group all of the blank rows together. Next, highlight the data area again, and re-sort by column A. The blank lines are gone, and your data is restored to its original order. Finally, remove column A.