Windows 7

# Microsoft Excel 2010 : SUBTOTAL Function, Subtotal Tool

12/3/2013 3:08:17 AM
1. SUBTOTAL Function

The SUBTOTAL function calculates a column of numbers based on the code used in the function. With the correct code, SUBTOTAL can calculate averages, counts, sums, and eight other functions listed in Table 1. It can also ignore hidden rows when the 100 version of the code is used.

##### Table 1. SUBTOTAL Function Numbers
Function_num (includes hidden values)Function_num (ignores hidden values)Function
1101AVERAGE
2102COUNT
3103COUNTA
4104MAX
5105MIN
6106PRODUCT
7107STDEV
8108STDEVP
9109SUM
10110VAR
11111VARP

See Figure 1 for examples of the SUBTOTAL function in action. The function will ignore any cells in the range that include SUBTOTAL functions themselves, as shown in column E of the worksheet in the figure. Column G uses the SUM function instead of SUBTOTAL and does not ignore the hidden rows or previous SUM formulas in the Grand Total.

### 2. Subtotal Tool

The SUBTOTAL function is very useful, but if you have a large dataset it can be time consuming to insert all the Total rows. When your dataset is large, use the Subtotal tool from the Data tab in the Outline group. This tool will group the sorted data, applying the selected function.

From the Subtotal dialog, you can select the column to group the data by, the function to subtotal by, and which columns to apply the subtotal to.

#### Summarizing Data Using the Subtotal Command

To quickly group and apply subtotals to a dataset, follow these steps:

1.
Sort the data by the column the summary should be based on. For example, if summarizing by region, sort the Region column.

2.
Select a cell in the dataset.

3.
Go to Data, Outline, Subtotal. The Subtotal dialog, shown in Figure 2, will open.

##### Figure 2. Use the Subtotal dialog to set your subtotaling options.

4.
From the At Each Change In field, select the column by which to summarize the data.

5.
From the Use Function field, select the function to calculate the totals by.

6.
From the Add Subtotal To field, select the columns the totals should be added to. Notice that, by default, the last column is already selected.

7.
Click OK. The data will be grouped and subtotaled, with a grand total at the very bottom, as shown in Figure 3.

#### Placing Subtotals Above Data

By default, subtotals appear below the data being summarized. If the subtotals need to appear above the data instead, deselect Summary Below Data in the Subtotal dialog.

#### Remove Subtotals or Groups

To remove all the subtotals and groups, click the Remove All button in the Subtotal dialog. To remove only the group and outline buttons, leaving the subtotal intact, select Data, Outline, Ungroup, Clear Outline.

 Other -----------------

 Video tutorials
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8

Popular keywords
HOW TO Swimlane in Visio Visio sort key Pen and Touch Creating groups in Windows Server Raid in Windows Server Exchange 2010 maintenance Exchange server mail enabled groups Debugging Tools Collaborating