Excel Subtotals Straight to the Point

February 2018

Everything you ever needed to know about Excel Subtotals

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.

Bill Jelen, MrExcel

covers: Excel 2010, Excel 2013, Excel 2016

Product Details
• 64 Pages
• Publisher: Holy Macro! Books
• PDF ISBN: 978-1-61547-243-7
• ePub ISBN: 978-1-61547-365-6
• Mobi (Kindle) ISBN: 978-1-61547-143-0

While I had 86 appearances on TechTV's Call for Help with Leo Laporte, the one episode that generated the most viewer mail was my 26th, when I took a few minutes and showed one of the most mundane examples ever: How to add subtotals to a data set.

This book completely and thoroughly covers every trick I know about Subtotals. In the course of writing the book, the content ended up in these sections:

• Adding Subtotals. Showing only the Subtotals. Sorting Subtotals. Formatting Subtotals
• Subtotal Tricks
• Subtotal Troubleshooting: How to get Medians. How to do Horizontal Subtotals
• Using the SUBTOTAL function without using the Subtotal command: Total Visible or SUMIF Filtered
• Using SUBTOTAL's cousin AGGREGATE including a fairly complex array formula example
• Creating Subtotals with VBA
• Skipping Subtotals and building your summary report with Power Query
• Why Have a Book on Subtotals

• How to Set up Your Data for Subtotals
• How to Fit a Multiline Heading into One Cell
• No Tiny Blank Columns Between Columns
• How to Sort Data
• Sort Days of the Week
• Sort a Report into a Custom Sequence
• Add Subtotals to a Data set
• Part 2 - Subtotal Tricks & Techniques

• Use Group & Outline Buttons to Collapse Subtotaled Data
• Manually Apply Groups
• Group Report Sections
• Subtotals Above Each Group
• Copy Just Totals from Subtotaled Data
• Sort Largest Customers to the Top
• Format the Subtotal Rows
• Add Other Text to the Subtotal Lines
• Subtotal One Column and Count Another Column
• Subtotal by Month With Daily Dates
• Subtotals by Product Within Region
• Add a Page Break For each Group
• My Manager Wants a Blank Line After Each Subtotal
• Add a Calculation Only to the Subtotal Rows
• Shift Subtotals One Column to the Right
• Finding G/L Accounts Not in Balance
• Part 3 - Subtotal Troubleshooting

• Why Is Final Subtotal Row Appearing After Many Blank Rows?
• Enter a Grand Total of Data Manually Subtotaled
• Why Does Subtotal Dialog Sometimes Default to Count?
• Adding Subtotals to Dozens of Columns
• Can You Get Medians?
• Horizontal Subtotals
• Subtotals Don't Work with Tables
• Part 4 - Using the SUBTOTAL function without Invoking the Command

• Adding a Total Visible Below a Filtered Data Set
• Using 109 For Rows Manually Hidden
• The 9 Basic Arguments
• The 9 New Arguments
• SUMIF or COUNTIF of Filtered Data
• Part 5 - Introducing AGGREGATE

• New Arguments for What to Ignore
• New Arguments for Aggregation Function
• Some Functions Require a Fourth Argument in AGGREGATE
• How is SMALL or LARGE Different than MIN or MAX?
• Doing MINIFS or MAXIFS Before They Existed
• Part 6 - Using VBA with Subtotals

• Creating Subtotals Using VBA
• VBA & TEXTJOIN to Subtotal Dozens of Columns
• Part 7 - Replacing Subtotals With Power Query

• Using Power Query to Get #2 View of Subtotals

About The 'Straight to the Point' Series

Books in this series are designed to thoroughly cover one targeted aspect of Excel.