MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Subtotals Straight to the Point


February 2018

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
  • 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:

  • 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

MrExcel Bookstore

eBook from MrExcel.

Add to Cart ยป

Related Products


Power Excel With MrExcel - 2017 Edition

January 2017

This is the print book edition of "Power Excel with MrExcel - 2017 Edition" - by Bill Jelen. Master Pivot Tables, Subtotals, Visualizations, VLOOKUP, Power BI and Data Analysis.