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

Copy The Cell Above In Excel Sometimes we get data that is downloaded from an external source and it is not formatted properly. You may have cells with missing data and cases where you want to copy the cell directly above to fill in your empty cell in Excel. This can be achieved with the following step...
Conditionally Formatting A Drop Down List In a previous post I showed you how to Create a Drop Down List in a Table.    We are now going to take this concept one level further and apply some conditional formatting to the drop down data validation list. This is useful if you want to highlight when a job is completed, c...
Rank Largest to Smallest With Excel Pivot Tables Excel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the RANK LARGEST TO SMALLEST calculation. This option will immediately calculate the rankings (1 being the LARGEST value) for your values, allowing you to pi...
Fix Incorrect Formatting Using Flash Fill In Excel   Flash Fill in Excel is a new feature that was introduced in Excel 2013.  One of the cool uses of Flash Fill is to fix incorrect formatting in your text automatically. Ever had the scenario where your data is formatted differently? Example: First names starting wit...