This is the last in a series of tips related to the Excel’s Subtotal feature which automatically inserts subtotals and grand totals into a list and groups the rows into a collapsible outline. Auto sum only works in certain situations. When it doesn’t work you can still accomplish the same thing with just a little more effort. Last month I explained how to write subtotal formulas. Now I’ll show you how to create the collapsible outline.
If you have a long report in Excel that already has subtotals in it, and you want to make it more manageable, that’s a perfect time to manually set up grouping.
Simply select all of the rows that you want to be able to hide (collapse) but not the row totaling them. Then click the Group button, which is located on the Data tab of the ribbon. In the left margin you’ll see a line appear next to the rows you just grouped. At the bottom will be a small box with a minus sign in it. Click that box to collapse or roll up those rows. The box now change to a plus sign. Click it to expand that section.
Now simply repeat that process for each section you want to be able to collapse. You can even select a larger group of rows that already have groups set up within them. Then you can choose which level of detail you want to be able to see. As you add levels, numbered boxes will appear at the top of the left margin allowing you to expand or collapse everything to the corresponding detail level.
To undo a grouping, select those rows again and click the Ungroup button.To get rid of all of your groupings, simply click the down arrow below the Ungroup button and choose Clear Outline.
One additional option is called Auto Outline. To have Excel automatically create groups, click the down arrow below the Group button and choose Auto Outline. If you don’t like the result, clear the outline and manually create the groups any way you want.
