How to Use Subtotals in Microsoft Excel

How to Use Subtotals in Microsoft Excel


When you have a data table with a lot of information, subtotals in Excel can help us understand and interpret the information. Excel lets you add subtotals in a very simple way.

Suppose the following data table where I want to subtotal sales for each month:

Subtotals in Excel Initial Data

The first thing to do is sort the data by the column on which the subtotals are going to be obtained. For this example I will order the data for the “Month” column. To do this you have to click any cell and then choose the Sort Command in the Data Tab. Finally click in Descendent command. (In Windows click in Sort from older to newer).

Sort a Table Button

The table is sorted

Data Sorted for Use Subtotals

The Subtotals Button in Excel

For insertion of the subtotals in Excel we must press the Subtotal command from the Data tab.
Excel shows the Subtotals dialog box

Subtotals Dialog Box in Excel

Now we are going to explain each box. The first one is At each Change in: we are going to select Month, that means that every change in the column month excel is going to put the subtotal. The second is the Function, in this example we are going to use Sum function. Then select the Sales item in the Add subtotal to menu, this means that excel is going to use the function with the date of the selected column. Finally click OK and excel calculate the subtotals:

Table with Subtotals Added in Microsoft Excel

Notice how Excel has inserted a new row containing the subtotal for each month. In addition to the left of the spreadsheet Excel places additional controls are useful to hide or display groups of data according to the subtotals. Clicking on any of them the corresponding group expands or contracts:

Subtotals Groups in Microsoft Excel