Problem: My manager wants me to add a blank line between sections of a subtotal report.
Strategy: This is a fairly standard request. Quite simply, data looks better when it is formatted this way. But there is no built-in way to do this with Excel. I've tried many methods. There are two methods that will work here. One method is simpler but is really cheating; you only make it look like you added a blank row. The second method is convoluted but about 50% easier than the method I described in the previous edition of this book.
The first method is to try to fool the manager by making the total rows double height, with the totals vertically aligned to the top. This method may work if you are printing the report to give to the manager. It will give the appearance that a blank row has been inserted. Here's how you do it:
- To do this easily, add subtotals, collapse to level 2, and select all subtotal rows from the first subtotal to the last subtotal.
- Select Home, Find & Select, Go To Special, and from the Go To Special dialog, select Visible Cells Only and click OK. (You can use Alt+; as the shortcut for Visible Cells Only.)
- Select Home, Format dropdown, Row Height. Depending on your font, the row height will probably be between 12 and 14. Say that the height is 12.75. Mentally multiply by 2 and type 25.5 as the new height.
- In the Home tab of the ribbon, click the Align Top icon.
- Choose the 3 Group & Outline button to display the detail rows again.
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.
- There is not a blank row between rows 8 and 9.
This method will not work if you have to send the data set to the manager via e-mail. The manager may be smart enough to want to stop at each subtotal by pressing the End key, and this will not work with the double-height rows.
Alternate Strategy: This method is far more complex than the one just described but creates the desired result. Follow these steps:
- Add subtotals as described previously. Click the 2 Group & Outline button.
- Insert a new temporary blank column A to the left of the current column A. To do this, select any cell in column A and then choose Home, Insert, Insert Sheet Columns.
- Select the cells in column A from the first subtotal down to the last subtotal.
- Use Alt+; to select only the visible rows.
- Type 1 and press Ctrl+Enter to put a 1 next to every subtotal.
- Click the 3 Group & Outline button to see all the detail rows. If you did step 4 correctly, you will see a 1 on only the subtotal lines.
- Select any blank cell before the first number 1 in column A. Select Home, Insert, Insert Cells, Shift Cells Down, OK. This will move the 1's from the subtotal lines to the first row of each customer.
- Select all of Column A. Select Home, Find & Select, Go To Special and select Constants in the Go To Special dialog.
- You've added a 1 next to each subtotal.
- Select Home, Insert, Insert Sheet Rows. Excel will insert 1 row above each row in your selection. Through the combination of steps 7 and 8, you were able to make a selection that consisted of each cell underneath the subtotals. Inserting a new row above these cells creates the result.
- Insert a row above each cell in the selection.
Results: You will have added the blank rows requested by the manager. You can now delete column A.
Gotcha: When the blank rows are in, you may have a difficult time getting rid of the subtotals. If you select cell A2 and choose Data, Subtotal, Remove All, Excel will delete only the first subtotal. In order to delete all the subtotals, you have to select the entire range before calling the Subtotal 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. Now when you choose Data, Subtotal, you will find that Excel has selected all the subtotals. Click Remove All to remove the subtotals.