This preview shows pages 1–3. Sign up to view the full content.
This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: 4482fc457cf952d367d4900716473f59df4254b4.xls 12/20/2011 Excel Subtotalling Overview Excel can automatically summarize data by calculating subtotal and grand total values in a list. To use automatic subtotals, your list must contain labeled columns and the list must be sorted on the columns for which you want subtotals. When your data is subtotalled, Excel outlines the list data, grouping detail rows with associated subtotal rows, and grouping subtotal rows with a grand total row. You can choose the summary subtotal calculation: SUM, MAX, MIN, AVG, etc. How to Subtotal Data With your data in list format, sort by the column or columns you want to subtotal on. Then from the menus choose Data, Subtotals and complete the dialog that displays. Sample Worksheets in this Workbook Sample Data 3-Level Outline 4-Level Outline 5-Level Outline 6-Level Outline Miscellaneous Removing Subtotals from a List Don't attempt to delete subtotals and outlining manually. Instead, click in the list and from the menus choose Data Subtotals to open the subtotals dialog. Then choose the "Remove All" button. The SUBTOTAL Function Excel uses its subtotal function to calculate list subtotals. The syntax of this function is interesting. For example: =SUBTOTAL(9,E6:E51) The range of data to subtotal is E6:E51. The number that's the first parameter indicates the sum calculation. Assignment 1 Level 1 Task 3 output. Sample Data The basic rule for grouping and subtotal ing list data is to sort first by the field on which you'l group. For example, if you want to sum Unit Volume for each Sales Division, sort by Sales Division first. Sales Division Market Brand Unit Volume Central Columbus Flower Pots 2,767 Central Columbus Flower Pots 2,857 Central Detroit Flower Pots 76 Central Detroit Flower Pots 199 North East Trenton Flower Pots 1,114 North East Trenton Flower Pots 510 North East Trenton Flower Pots 70 Central Wichita Flower Pots 8,223 Central Wichita Flower Pots 5,348 Central Wichita Flower Pots 7,795 Central Wichita Flower Pots 3,718 South Miami Adhesives 91,284 West Portland Adhesives 4,107 Central Indianapol is Bat eries 3,706 North East Albany Ceiling Lamps 15,538 North East Albany Ceiling Lamps 1,630 North East Albany Ceiling Lamps 2,364 West Seat le Ceiling Lamps 47,236 West Seat le Ceiling Lamps 3,806 West Seat le Ceiling Lamps 161 North East Albany Curtains 8,441 North East Baltimore Curtains 61,400 Central Columbus Curtains 4,336 Central Detroit Curtains 4,840 South Miami Curtains 55,976 South Mobile Curtains 6,191 West Portland Curtains 23,789 West Seat le Curtains 1,542 North East Trenton Curtains 5,516 South Atlanta Desk Lamps 17,391 North East Boston Desk Lamps 202 West San Diego Desk Lamps 865 West San Diego Desk Lamps 1,905 West Portland Duct Tape 1,228 West Salt Lake Duct Tape 5,782 North East Trenton Duct Tape 195 Central Wichita Duct Tape 4,963 North East Albany Floor Lamps 87,919 North East Albany Floor Lamps 25,953 North East Albany Floor Lamps 39,164 North East...
View Full Document
- Spring '01