Learn How To Summarize Your Data Instantly

If you want to summarize your data fast use Subtotal command to calculate automatically each similar groups and their grand total.

Info: Ensure yourself that your data is labeled and sorted properly before using Subtotal command.

You can find the subtotal command in the Data menu. Excel opens a dialogue box once you click it and displays all the labels in the drop menu ‘At each change in’ in the top of the dialogue box.

Now you need to select one label from the list. The label you selected becomes the Index and other labels are considered as items.

Indexing is done to create a tracking order for calculating subtotal of other items. If there is a change in data in the index Microsoft Excel stops summarizing the values till that point, makes a total and continues the summarizing next similar data.

You can use any one of the FUNCTIONS (COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR & VARP) in the dialogue box to find value of items. You can find this option in the middle of the dialogue box.

In the bottom of dialog box all the labels are displayed in the scroll menu ‘Add subtotal to’ and Excel ask you to select one or more than one item that you want to summarize. Selected items are summarized by the FUNCTION based on Indexed label.

The other features in this dialogue box are:

Replace Current Subtotals: In case you summarized a particular field using Subtotals and want to change the index label or summarizing function or items, open subtotal dialogue box make necessary changes in inputs and enable ‘Replace Current Subtotals‘ option. Excel will remove existing subtotal and replaces with the new one based on your input.

Page break between Groups: If data taken to summarize is huge and it is required to take print out for each item, you need to enable the option ‘Page break between Groups’. Excel prepares page break between each group.

Summary below data: This option helps you to get summary details below each summarized item.

Tips: If you want to remove subtotals and get back original data, open subtotal dialogue box and select ‘Remove All’.

The result will have outlines that helps you to overview the data. Outlines help you to expand or collapse the nested items without affecting the data. You can turn off outlines if not required. Open options dialogue box and disable ‘outline symbols’ from windows options in the view tab.

Tips: Press Ctrl key and numerical key 8 at the same time to turn ON or OFF outlines.