Data Appropriate for a Pivot Table


A pivot table requires that your data be in the form of a rectangular database. You can store the database in either a worksheet range (which can be a normal range, or a table created by using Insert image from book Tables image from book Table) or an external database file. Although Excel can generate a pivot table from any database, not all databases benefit.

image from book
Pivot Table Terminology

Understanding the terminology associated with pivot tables is the first step in mastering this feature. Refer to the accompanying figure to get your bearings.

  • Column labels: A field that has a column orientation in the pivot table. Each item in the field occupies a column. In the figure, Customer represents a column field that contains two items (Existing and New). You can have nested column fields.

  • Grand totals: A row or column that displays totals for all cells in a row or column in a pivot table. You can specify that grand totals be calculated for rows, columns, or both (or neither). The pivot table in the figure shows grand totals for both rows and columns.

  • Group: A collection of items treated as a single item. You can group items manually or automatically (group dates into months, for example). The pivot table in the figure does not have any defined groups.

  • Item: An element in a field that appears as a row or column header in a pivot table. In the figure, Existing and New are items for the Customer field. The Branch field has three items: Central, North County, and Westside. AcctType has four items: CD, Checking, IRA, and Savings.

  • Refresh: Recalculates the pivot table after making changes to the source data.

  • Row labels: A field that has a row orientation in the pivot table. Each item in the field occupies a row. You can have nested row fields. In the figure, Branch and AcctType both represent row fields.

  • Source data: The data used to create a pivot table. It can reside in a worksheet or an external database.

  • Subtotals: A row or column that displays subtotals for detail cells in a row or column in a pivot table. The pivot table in the figure displays subtotals for each branch.

  • Table Filter: A field that has a page orientation in the pivot table-similar to a slice of a three-dimensional cube. You can any number of items (or all items) in a page field at one time. In the figure, OpenedBy represents a page field that displays the New Accts item. In previous version of Excel, a table filter was known as a Page field.

  • Values area: The cells in a pivot table that contain the summary data. Excel offers several ways to summarize the data (sum, average, count, and so on).

image from book

Generally speaking, fields in the database table consist of two types:

  • Data: Contains a value or data to be summarized. For the bank account example, the Amount field is a data field.

  • Category: Describes the data. For the bank account data, the Date, AcctType, OpenedBy, Branch, and Customer fields are category fields because they describe the data in the Amount field.

A single database table can have any number of data fields and category fields. When you create a pivot table, you usually want to summarize one or more of the data fields. Conversely, the values in the category fields appear in the pivot table as rows, columns, or filters.

Exceptions exist, however, and you may find Excel's pivot table feature useful even for a database that doesn't contain numerical data fields. In such a case, the pivot table provides counts rather than sums.

Figure 18-4 shows an example of an Excel range that is not appropriate for a pivot table. This range contains descriptive information about each value, but it's not set up as a table. In fact, this range resembles a pivot table summary.

image from book
Figure 18-4: This range is not appropriate for a pivot table.




Excel 2007 Formulas
Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)
ISBN: 0470044020
EAN: 2147483647
Year: 2007
Pages: 212

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net