Using Formulas and Functions in Microsoft Excel 2013
- 4/15/2013
- Creating simple cell formulas
- Assigning names to groups of cells
- Using names in formulas
- Creating a formula that references values in an Excel table
- Creating formulas that reference cells in other workbooks
- Changing links to different workbooks
- Analyzing data by using the Quick Analysis lens
- Summing a group of cells without using a formula
- Creating a summary formula
- Summing with subtotals and grand totals
- Exploring the Excel function library
- Using the IF function
- Checking formula references
- Debugging your formulas
Summing with subtotals and grand totals
You frequently need to organize the data in an Excel worksheet by one or more criteria. For example, you might have a worksheet in which you list yearly sales for each product that you offer, with the products broken down by category. If your data is organized this way, you can have Excel calculate a subtotal for each category of products. When you create a subtotal, you identify the cells with the values to be calculated and the cells that identify the change from one category to the next. Excel updates the subtotal and grand total for you if the value of any cell changes.
Create a subtotal
Click any cell in the range that you want to subtotal.
Click the Data tab.
In the Outline group, click Subtotal.
Click the At Each Change In down arrow.
Click the value on which you want to base the subtotals.
Click the Use Function down arrow.
Click the subtotal function that you want to use.
Select which columns should have subtotals calculated.
Click OK.
)
Remove a subtotal
Click any cell in the subtotaled range.
Click the Data tab.
Click Subtotal.
Click Remove All.
)