add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba

Microsoft Excel Grouping and Outlining

In a report made up of numerous levels of subtotals, it is difficult to view a vertical or horizontal summary of the results. The Grouping and Outlining technique is designed to allow you to hide rows and/or columns, making it easier for you to view a report of summarized data.

The Subtotals feature automatically enters a SUBTOTAL function into the cells and enables Subtotals for columns alone. In Grouping and Outlining, you determine the subtotal levels according to the reports you created. As can be seen in the figures below, the grouping and outlining levels are different.

Grouping and Outlining Guidelines

Enter data and functions into the sheet using the guidelines below. After you have finished entering the data and the functions and adding the SUBTOTAL functions (manually, not automatically from the Data menu), from the Data menu, select Group and Outline, Auto Outline.

Auto Outlining is done according to the following guidelines:

© SUBTOTAL and GRAND TOTAL functions

Outlining is possible because of the insertion of SUBTOTAL and GRAND TOTAL functions. When you create an outline, Excel identifies the subtotals and uses them to determine the outline levels.

© No empty rows or columns

Data lists do not contain empty columns or rows.

Adding Icons to Toolbars

If you use Grouping and Outlining regularly, you will want to add several icons to the toolbar.

Add the Auto Outline, Clear Outline, and Show Outline Symbols to the toolbar.

Right-click a toolbar, and from the shortcut menu, select Customize. Select the Commands tab, and from the Data category, drag the icons to the Standard toolbar.

Tip – Hide outline symbols

The Show Outline Symbols icon also lets you hide the outline symbols. The outline symbols reduce the size of the data region in the window.

Alternatively, use the Ctrl+8 shortcut. To restore the outline symbols, press Ctrl+8 again.

Adding Manual Outlining

Manual outlining allows you to determine the outline levels for each group without requiring total data organization based on the guidelines presented at the beginning of this chapter.

Select cells A2:A4 (income items, see figure below). From the Data menu, select Group and Outline and then select Group. Select Rows and click OK. Rows 2-4 are grouped, and you can now view the total of that group in Row 5, Total Income. By clicking the + symbol, you can expand the group to show the details.

An additional technique for grouping and outlining is to select a number of rows or columns and press Alt+Shift+Right Arrow. To clear the outlining, select the same rows or columns, and press Alt+Shift+Left Arrow.

In the figure below, the income items (Row 5), the expense items for Depart1 (Row 11), and the Quarter 1 items (Column E) are grouped.

Clearing the outline

From the Data menu, select Group and Outline, Clear Outline.

Combining Automatic and Manual Outlining

You can combine automatic and manual Group and Outline. Add Auto Outline, and then add manual outline levels. The result will be totals and details for subgroups.

Copying or applying color to reports created by grouping and outlining

Select Visible cells only before copying reports created by Group and Outline.

1. Consolidate outline levels as desired.

2. Press Alt+; to select visible cells.

3. Copy and paste the data into a different sheet, or apply color to the visible cells by clicking the Fill Color icon on the Formatting toolbar and selecting a color.