Download Excel Workbook
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 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 Academy Online Course.
