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 (see image below)

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.



Subtotals Feature



If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Autofill Formulas in an Excel Table One of the advantages of using an Excel Table is the ability to autofill a formula all the way down your data without having to copy and paste.When you write a formula anywhere in your Excel Table, it will automatically fill down and up within that column.As you add extra...
Lock & Protect Formula Cells If you have a workbook with lots of formulas and you want to protect those formulas from being amended by other people who share your workbook, then you can!You need to follow these steps:1. Press the Go To Special shortcut CTRL+G2. Select the Constants box and press ...
Custom Date Formats in Excel Custom date formats in Excel allow you to display only certain parts of the date.Say you had a date of 18/02/1979, which coincides to be my birthday 🙂You can use the Format Cells dialogue box to show only the number 18, the day that corresponds to that date (Sunday), the...
Quick Access Toolbar The Quick Access Toolbar (QAT) is located at the top left hand corner of the ribbon and has the most commonly used commands, like the Save, Undo and Redo.  The QAT is unique to each user´s workbook settings.You can move the QAT below or above the ribbon by right clicking on t...