Excel Subtotals Straight to the Point

Bill Jelen

• Publisher: Holy Macro! Books
• Year: 2018
• eBook ISBN: 9781615473656
• Edition: 1
• Topic: Informatica
• Subtopic: Competenze per il business

This book completely and thoroughly covers every trick known about Subtotals in Excel. You will learn: How to get your data ready for subtotals, Adding Subtotals, Showing only the Subtotals, Sorting Subtotals, Formatting Subtotals, Subtotal Tricks, Subtotal Troubleshooting, Using the SUBTOTAL function without using the Subtotal command, using AGGREGATE, Creating Subtotals with VBA, Replacing Subtotals with Power Query.

Subtotal Tricks & Techniques

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 31 One row per customer.

Click the small 1 button to see only the grand total.

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.

Manually Apply Groups

Problem: Those group and outline symbols shown in the last topic are cool. Is there any other way to get those? Can I apply them without using subtotals?

Strategy: You just select columns or rows to be grouped and select Data, Group. It is fairly tedious to add many groupings, but this can be easier than continually hiding and unhiding rows or columns.

Below, select the entire column for Jan, Feb, and Mar and click the Group icon on the Data tab. Excel will group those 3 columns and assumes the next column is the summary of those columns.

Figure 32 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, November, 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 33 Use the 1 button to collapse.

Group Report Sections

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 Modeling 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 34 Open the dialog launcher.

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

Figure 35 Uncheck Summary Rows Below Detail.

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

Figure 36 Select the rows for S...

Table of contents

• About the Author
• Preparing Your Data and Adding Subtotals
• Subtotal Tricks & Techniques
• Using the SUBTOTAL function without Invoking the Command
• Introducing AGGREGATE
• Using VBA with Subtotals
• Replacing Subtotals With Power Query