2. Separating Subtotaled Rows for Distributed Files
It’s a bit involved, but a blank row can be inserted
between groups in a file that you’re going to distribute. The method
involves using a temporary column to hold the space below where a blank
row is needed.
|
This method will disable Excel’s capability to
manipulate the subtotals in the dataset. The total rows will remain,
but the outline icons no longer work properly, and future subtotal
changes will require the groupings and subtotals to be manually removed
first.
|
Inserting Blank Rows After Subtotals
To insert blank rows after subtotals placed below the data (see Figure 4), follow these steps:
1. | Collapse the dataset so only the subtotals are in view.
|
2. | In a blank column to the right of the dataset, select a range as long as the dataset.
|
3. | Press Alt+; (semicolon) to select the visible cells only.
|
4. | Type a 1 and press Ctrl+Enter to enter the value in all visible cells.
|
5. | Expand the dataset.
|
6. | Select the cell above the first cell with a 1 in it.
|
7. | Go to Home, Cells, Insert, Insert Cells.
|
8. | From the Insert dialog, select Shift Cells Down, and click OK, as shown in Figure 4.
|
9. | Highlight the column with the 1s in it.
|
10. | Go to Home, Editing, Find & Select, Go to Special.
|
11. | From the Go to Special dialog, select Constants and click OK, as shown in Figure 5
|
12. | Go to Home, Cells, Insert, Insert Sheet Rows. A blank row is inserted above the row containing a 1, as shown in Figure 6.
|
13. | Delete the temporary column.
|
