Page 60 - Excel Data Analysis

04 537547 Ch03.qxd 3/4/03 11:50 AM Page 46

EXCEL DATA ANALYSIS

SUMMARIZE DATA WITH SUBTOTALS

f you want to summarize a data list based upon Subtotal dialog box. For more on sorting your data, see
common values, you can use the Subtotal option to Chapter 2. For more on labeling columns or rows, see
I automatically insert subtotals for common records and a Chapter 1.
final total for the entire data list. For example, if your data Located in the Subtotal dialog box, the At each change in
list contains monthly calls made by the sales department, field defines the column label you want Excel to monitor for
Excel can subtotal the number of calls for each month and changes, and when it notes a change, it inserts a subtotal.
then create a total for the number of calls that year at the You control the location of the subtotal in the Add subtotal
end of the data list. Excel does this by monitoring the value to box. The Use function field defines the function that
in the Month column and inserting a subtotal whenever the Excel uses to summarize the data. Although the default
value changes, for example, when January changes to function is Sum, you can select other functions to count
February. If each record in your data is unique, such as a records or determine average values.
separate amount for each month, a subtotal amount
displays under each record. The checkboxes at the bottom of the dialog box control the
placement of the subtotal row within the worksheet. By
To create subtotal amounts, first sort your list grouping all default, Excel replaces any subtotals in the data list and
common records together based upon the column you places subtotals above each group.
want to use to group the data. You must label each column
because Excel uses the labels to identify your cells in the

SUMMARIZE DATA WITH SUBTOTALS

Month

Amount

⁄ Select the range of cells to Note: See Chapter 1 for information ■ The Subtotal dialog box ‹ Select an appropriate
subtotal. on selecting a range. displays. column name from the At
each change in field.
¤ Click Data ➪ Subtotals.

46