Excel: Double Space Your Data Set

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: My manager wants me to add a blank row after every row of the data.

  1. Double space the report!

    Strategy: Excel MVP Bob Umlas showed me this trick, and it has become one of my favorites. Search YouTube for Learn Excel 467 to see Bob demo the trick.

    Bob adds a new column with numbers 1, 2, 3, and so on. He then copies this range of numbers below the itself. When you sort by the new column, your report is instantly double-spaced! Follow these steps:

    1. In the blank column to the right of your data, enter the heading Sort.
    2. Fill the column with a sequence of 1, 2, 3, etc. One method is to type a 1 in the first cell, select the cell, and Ctrl+drag the fill handle to the end of the data set.
    3. Press Ctrl+C to copy the selected numbers in the new column to the Clipboard.
    4. Select the first blank cell beneath your new column. Press Ctrl+V to paste a duplicate set of numbers.

  2. Copy the numbers below.
    1. Select one cell in the new column. Click the AZ button on the Data tab. Excel sorts by the new column. Because every number occurs twice-once in the original report and once below the report-blank rows are sorted up into your data.

  3. Sort by C, and the data is double-spaced.
    1. Delete the Sort column.

    Additional Details: To triple space your data, you can paste two copies of the numbers below your data.

    Additional Details: Check out Bob Umlas's book More Excel Outside the Box (available at Amazon). It is filled with tricks like this one.