The Excel Subtotal command gives a quick method of producing subtotals because that individual areas in a table that data.
You are watching: Before you use the subtotal command, your data must be:
Note that this command is not the same as the Excel Subtotal Function, i beg your pardon performs mathematics operations because that a selection of clearly shows cells.
We will show the Excel Subtotal command utilizing the above spreadsheet on the right, i m sorry lists the sales numbers for 3 teams throughout the an initial three months of the year.
The complying with steps deserve to be offered to display subtotals because that each of the months, Jan, Feb & Mar:Select any kind of cell within your data table (Excel will automatically detect the entire data variety for usage in the subtotal command);
Click ~ above the Subtotal option which is located in the "Outline" group, within the Data tab the the Excel ribbon.
This will cause the Subtotal dialog crate to open up up (see aboveright). Within this box:Within the At each change in: entry field, choose Month;Within the Use function: entry field, select Sum;Within the Add subtotal to: field, ensure the the Sales option is checked;Click OK.
These alternatives tell Excel to display screen a subtotal every time the value in the Month obelisk changes and that this subtotal should display the Sum the the data in the Sales column.
The result spreadsheet is shown below:
Clicking ~ above the outlines, to the left of the table permits you to hide or display screen the details the each section of your data table.
Other Subtotal Options
The Subtotal dialog box offers further options, in addition to those offered in the above example. The alternatives are each described below:
At each adjust in:
The at each adjust in: ar of the Subtotal dialog box permits you to choose which ar of your data table you want the Subtotals to apply to.
For example, in the spreadsheet above if we had selected the field Team, a subtotal would have appeared each time the team name changed (although in this case, it would certainly be much more useful come order the data table through team before applying the subtotals).
The Use function: field of the Subtotal dialog box allows you to choose a mathematical procedure that is to be performed top top the groups of data.
For example, in the spreadsheet above, rather of calculating the Sum of the monthly sales figures, we could have calculate the Average sales number for every month.
Add subtotal to:
The Add subtotal to: ar of the Subtotal dialog box allows you to select which columns of your table you want the selected mathematical operation to be used to. You can select multiple areas to use the subtotal operation to, return this alternative really only makes sense for fields containing numbers.
Replace existing subtotals
The Replace current subtotals checkbox gives you the alternative to add much more than one subtotal come a data table.
For example, if, in the above spreadsheet, we want to display the Sum and also the Average sales numbers for each month, us could first apply the Sum subtotal (as in the example) and then us could choose the subtotal choice again and also request the Average calculation. Adhering to this:If the Replace existing subtotals crate is checked, the 2nd subtotal request (for the Average) will replace the Sum subtotals;If the Replace present subtotals box is not checked, the second subtotal inquiry (for the Average) will certainly be shown in enhancement to the Sum subtotals.
The over spreadsheet top top the right mirrors the result after using both the Sum and also the Average subtotals.
Page break between groups
If you examine the Page break between groups checkbox, Excel will insert page breaks into your spreadsheet ~ each team of data. This may be advantageous if you want to publish out your spreadsheet.
Summary below data
The Summary listed below data checkbox simply specifies where the team summaries room positioned.If the Summary below data box is checked, the subtotals are positioned below each data group;If the Summary below data crate is no checked, the subtotals space positioned at the optimal of every data group.
How to eliminate Subtotals From her Spreadsheet
In stimulate to remove the subtotals from your table:Select any cell within the data table;
Click ~ above the Subtotal alternative (within the Data tab of the Excel ribbon):When the Subtotal dialog box opens up, click the Remove All button.
See more: Where Is The Content Placeholder In Powerpoint, How To Use A Placeholder In Powerpoint
Further instances of the Excel Subtotal command are listed on the Microsoft Office support website.