MrExcel.com - Your One Stop for Excel Tips & Solutions

Double Space Your Data Set

October 17, 2022 - by

Double Space Your Data Set

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

A simple data set with two columns and a bunch of rows. Your evil manager wants the data double-spaced - and has asked you to add a blank row between each row.
Figure 750. 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.

In column C, number the records 1, 2, 3, and so on. Copy those numbers and paste to the first blank cell in column C. This gives you two sets of numbers 1, 2, 3, 4, ... 15, 1, 2, 3, ... 15.
Figure 751. Copy the numbers below.

  • 5. 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.

Sort by Column C and the second "1" sorts to after the first 1. This continues throughout the data and you now have blank rows in columns A & B after every original row. Delete column C so your manager does not know how you did it.
Figure 752. Sort by C, and the data is double-spaced.

  • 6. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Austris Augusts on Unsplash