Page 134 - Excel Data Analysis

08 537547 Ch07.qxd 3/4/03 12:07 PM Page 120

EXCEL DATA ANALYSIS

PIVOTTABLE BASICS

sing a PivotTable, you can create a dynamic and options. PivotTables are dynamic because you can change a
immediate summarization of your Excel list or report's appearance instantaneously as well as create links
U external database. PivotTable Reports allow you to to the original data to refresh the table when your data
cross-tabulate, or summarize data records in two or more changes.
ways by combining values from different fields. For You can use any Excel-accessible database to import data
example, you can analyze the order amounts for values and create a PivotTable report. See Chapter 5 for
salespeople from each state, find the total sales per more information on importing data values from external
salesperson, and then rank the salespeople by total sales sources.
amounts. You can accomplish all of this by simply changing
the location of fields on the report and applying filtering

Filtering and Grouping Data PivotTable Restrictions
Large lists work well in PivotTables, because you can quickly To have your PivotTable work properly, consider the
filter them to show specific values, and group them to hide following guidelines and restrictions:
or reveal sections of data. To construct your PivotTable, you
drag fields — basically the labels of your data — to a layout. • You must label each column because Excel will make
The field essentially acts as a filter for your data in that you them into the field names on the PivotTable report.
can select which data values you want to display for each
field. Excel includes the All option with each field, which • Because a PivotTable report creates the necessary
you can use to display all possible values for that field. For totals based upon the fields you have in the report,
example, if you select All for the Months row field, the you must remove any automatic totals from the list.
PivotTable displays all values from each month. If you only • Excel uses the entire list, even hidden cells. If you do
want to select specific values, you can select them not want hidden data in the PivotTable, you must
individually from the Column and Row fields. The Page field filter the list to another worksheet using the
only allows the selection of one value and that value Advanced Filter. See Chapter 3 for more information.
matches all data records that display on the page. See the
section "Filter a Field" for more information on filtering. • Excel allows you to create a PivotTable that contains a
maximum of 8,000 total items. You can only have 256
Once you construct your PivotTable, you can use Excel's fields in the Page area and 256 fields in the Data area.
Group option to combine items together into one value The other areas are only limited by the overall size
or to show a specific relationship. See the section limitation.
"Group PivotTable Data Items" for more information
on the grouping option.

PivotTable Data Types
No matter what the data source, you must make your data source fields one of the following types:
TYPE DESCRIPTION
Category Contains a text value that describes the data. You use category type values as labels for the Row,
Column, and Page fields.
Data Contains a value to summarize. Typically the data values are numbers, but you can also summarize
text values.

120