USE GROUP & OUTLINE BUTTONS TO COLLAPSE SUBTOTALED DATA

Problem: I just used the Subtotal command in “Add Subtotals to a Data Set,” and now I want to print the total rows in order to create a summary report for my manager.

Strategy: If you look above and to the left of cell A1, you’ll see a series of three small numbers. These are the Group & Outline buttons. You can use them to collapse subtotaled data. If you click the small 2 button, you will see just the customer totals.

Figure 640 One row per customer.

Click the small 1 button to see only the grand totals. Click the 3 button to go back to all detail rows.

Additional Details: In the 2 button view, you can collapse or expand a single customer’s detail records by clicking the - or + symbols next to the customer total.

Figure 641 Grouping Jan, Feb, Mar into an existing Q1 column.

Repeat this to group April, May, and June into Q2; July, August, September into Q3; and October, Novem- ber, and December into Q4. The result is that you can quickly toggle from monthly to quarterly views by using the 1 or 2 buttons.

Figure 642 Use the 1 button to collapse.

Problem: The grouping feature feels backwards. What if I have report headings above each section and I need to group the data below the heading?

Strategy: I picked up this great trick from Mack Wilk, one of the two-time ModelOff World Financial Mod- eling finalists. There is an obscure setting that makes grouping work the way you want it to work. Mack uses this trick in his models, with multiple levels of grouping. It creates an uncluttered view of the model.

1. On the Data tab, click the Dialog Launcher in the corner of the Outline group.

Figure 643 Open the dialog launcher.

2. In the Settings dialog, uncheck Summary Rows Below Detail.

Figure 644 Uncheck Summary Rows Below Detail.

3. Select the rows underneath the heading for section 1.

Figure 645 Select the rows for Section 1, excluding the heading.

4. Press Shift+Alt+RightArrow to group the selection. Repeat for the rows for each section.

Gotcha: When you mistakenly press Ctrl+Alt+Right arrow, your display may turn sideways (as if you were going to mount your monitor in a portrait fashion). Press Ctrl+Alt+Up arrow to return the monitor to the correct orientation.

You will now have group and outline buttons to collapse all sections. Use one of the + icons to display any section.

Figure 646 You can easily expand or collapse any section.

Problem: I’ve added subtotals and collapsed to the #2 view. My manager wants me to send him just the total rows in a file. When I copy and paste, I get all of the detail rows as well.

Strategy: You can use an obscure command in the Go To Special dialog box to assist with this task. Fol- low these steps:

1.Choose the 2 Group & Outline button to put the data in subtotal view.

2.Select the entire data set. Use Ctrl+* or Ctrl+A or Ctrl+Shift+8. Gotcha: If you forget to hold shift and press Ctrl+8, Excel will remove the Group & Outline symbols. Bring them back again with Ctrl+8.

3.Bring up the Go To Special dialog by choosing Home, Find & Select, Go To Special. Alterna- tively, you can press the F5 key and click the

Special button in the lower-left corner of the

Go To dialog.

4.In the Go To Special dialog, select Visible

Cells Only

3

Figure 647 Select Visible Cells Only.

5.Click OK. There will be thin white lines above and below each subtotal. If you are in Excel 2007, the color of selected cells is too light to make out the white lines. You’ve now selected only the visible cells.

Figure 648 The white lines indicate the hidden rows are unselected.

6. Press Ctrl+C to copy. The marching ants will surround each row.

Figure 649 Copy the subtotal lines.

7. Switch to a new workbook. Press Ctrl+V to paste. Excel will paste just the subtotal rows.

Figure 650 Paste the subtotals only to a new workbook.

You might think that you would have to select Paste, Values instead of just doing a paste. However, the Paste command works okay. Excel converts the SUBTOTAL functions to values.

Additional Details: Instead of selecting Go To Special, Visible Cells Only, you can press Alt+; (that is, hold down the Alt key and type a semicolon).

Alternate Strategy: There is a Quick Access Toolbar icon for Select Visible Cells. Follow the steps in "Make Your Most-Used Icons Always Visible" on page 12.

Figure 651 Add Select Visible Cells to the QAT.

SORT LARGEST CUSTOMERS TO THE TOP

Problem: I added subtotals to a data set and collapsed to the #2 view. Now, my manager wants the largest customers at the top of the data set.

Strategy: You would never expect this to work, but you can sort groups of records when in the #2 view.

Start with a the original data set shown here. Choose one cell in the revenue column. Click the ZA button to sort descending.

Figure 652 Collapse the data, sort by revenue.

Wal-Mart comes to the top of the data set, but notice that the Wal-Mart total is in row 67.

Figure 653 The largest customers come to the top.

Click the 3 Group and Outline button. You will see that all of the Wal-Mart records were sorted along with the Wal-Mart total.

Figure 654 When sorting Wal-Mart to the top, rows 2-67 were treated as a single unit in the sort. This is

fairly amazing.