Strategy: This is really hard. There is no built-in way to do this. I’ve tried many methods. I picked the three best methods to explain here.
1) To do this easily, follow the steps in the preceding "bold pink Tahoma font" chapter. Add subtotals, collapse to level 2, select column A between the headings and grand total, Edit – Go To – Special– Visible Cells Only. You will then have selected just the subtotal rows, as shown in Fig. 721.
2) From the menu, select Format – Row – Height. Depending on your font, the row height will probably be between 12 and 14. The height shown here in Fig. 722 is 12.75.
3) Mentally multiply by 2 and type 25.5 as the new height, as shown in Fig. 723.
This creates a fairly ugly intermediate result, as shown in Fig. 724.
4) Select everything from the first subtotal to the last subtotal again, this time selecting all of the columns in the dataset. As shown in Fig. 725, use Edit – Go To – Special – Visible Cells Only.
5) Press Ctrl+1 on the keyboard to display the Format Cells dialog. Select the Alignment tab. By default, you will see that the Text Alignment – Vertical is set to Bottom, as shown in Fig. 726.
6) Choose the Vertical dropdown arrow. As shown in Fig. 727, you will appear to have choices for Center, Bottom, Justify, or Distributed.
7) Use the up scrollbar to display the other choice: Top, as shown in Fig. 728.
8) Select Top and choose OK, as shown in Fig. 729.
As shown in Fig. 730, the intermediate result looks only slightly better.
9) Choose the Group & Outline button #3 to display the detail rows again, as shown in Fig. 731.
Although you can see that there is no blank row after the subtotals in rows 8 and 13, when you print the report for your manager, it will appear to have a blank row, as shown in Fig. 732.
10) From the menu, select Insert – Row. You will have inserted 50 rows at the appropriate spots, as shown in Fig. 747.
11) You can now delete the temporary column A. Select any cell in column A and choose Edit – Delete – Entire Column, as shown in Fig. 748.
Gotcha: In order to delete all of the subtotals, you will have to select the entire range before calling the Data Subtotals command. One fast way to do this is to click on the blank gray box above and to the left of cell A1. This box will select all cells in the worksheet, as shown in Fig. 751.
Now when you choose Data – Subtotals, you will find that Excel has selected all of the subtotals. Click Remove All to remove the subtotals, as shown in Fig. 752.