• Excel Book Excerpt

Excel DOUBLE SPACE YOUR DATA SET

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

DOUBLE SPACE YOUR DATA SET

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

LE10000712.jpg

Figure 690 Double space the report!

Strategy: Excel MVP Bob Umlas showed me this trick, and it has become one of my favorites. Search 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.

LE10000713.jpg

Figure 691 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.

LE10000714.jpg

Figure 692 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 Excel Outside the Box (available at Amazon). It is filled with tricks like this one.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel:

privacy policy