Excel’s Subtotal Feature

Excel´s Subtotal feature is a great way to automatically insert a Sum/Count/Average/Max/Min subtotal to your data set with a press of a button.

This feature is located under the Excel Data tools menu: Data > Subtotal.  To insert this feature you need to follow these quick steps:

STEP 1: Highlight your data and go to Data > Subtotal

STEP 2: This will open up the Subtotal dialogue box

STEP 3: At each change: Select which column you want to use to separate the data.  Make sure that the data in the selected column is in ascending/descending order

STEP 4: Use function: Select which function you want to use for your calculation.  You can select from Sum, Count, Average, Maximum, Minimum, Product, Count Numbers, StdDev, StdDevp, Var, Varp

STEP 5: Add subtotal to: Select the column that you want to Subtotal

STEP 6: Press OK and this will add extra lines to your data with the chosen Subtotal

STEP 7: If you want to change the Subtotal (say from a Sum to an Average) all you have to do is click in your data and go to Data > Subtotal and it will bring up the Subtotal dialogue box once again.  Under Use function select Average and press OK.

Download workbookSubtotals-Feature.xlsx

John Michaloudis

Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.