MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Subtotals Straight to the Point

February 2018 - by Bill Jelen

		Excel Subtotals Straight to the Point

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

category: Spreadsheets
covers: Excel 2010, Excel 2013, Excel 2016

Product Details
  • eBook: 64 Pages
  • Publisher: Holy Macro! Books
  • Print ISBN: N/A
  • 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:

  • How to get your data ready for subtotals
  • 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

  • Part 1 - Preparing Your Data and Adding 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.

Where to Buy

Related Products