How to Produce a Pivot Table in Excel 2007
Creating a pivot table has never been as easy as it is in Excel 2007: simply open the worksheet that contains the data list you want summarized by the pivot table, position the cell cursor somewhere in the cells of this list, and then click the Pivot Table command button on the Ribbon's Insert tab or press Alt+NVT.
Excel then opens the Create Pivot Table dialog box, while at the same time selecting all the data in the list containing the cell cursor indicated by a marquee around the cell range. You can then adjust the cell range in the Table/Range text box under the Select a Table or Range option button, if the marquee does not include all the data to be summarized in the pivot table.
By default, Excel builds the new pivot table on a new worksheet it adds to the workbook. If, however, you want the pivot table to appear on the same worksheet, click the Existing Worksheet option button and then indicate the location of the first cell of the new table in the Location text box. (Just be sure that this new pivot table isn’t going to overlap any existing tables of data).
If the data source for your pivot table is an external database table created with a separate database management program such as Access, you need to click the Use an External Data Source option button, then click the Choose Connection button and then click the name of the connection in the Existing Connections dialog box.
After you indicate the source and location for the new pivot table in the Create Pivot Table dialog box and click its OK button, the program adds a blank grid for the new table and displays a Pivot Table Field List task pane on the right side of the Worksheet area. This Field List task pane
is divided into two areas: the Choose Fields to Add to Report list box with the names of all the fields in the data list you selected as the source of the table preceded by an empty check box at the top and an area divided into four drop zones (Report Filter, Column Labels, Row Labels, and Values) at the bottom.
To complete the new pivot table, all you have to do is assign the fields in the Pivot Table Field List task pane to the various parts of the table. You do this by dragging a field name from the Choose Fields to Add to Report list box and dropping it in one of the four areas below called drop zones:
Report Filter
This area contains the fields that enable you to page through the data summaries shown in the actual pivot table by filtering out sets of data - they act as the filters for the report. So, for example, if you designate the Year Field from a data list as a Report Filter, you can display data summaries in the pivot table for individual years or for all years represented in the data list.
Column Labels
This area contains the fields that determine the arrangement of data shown in the columns of the pivot table.
RowLabels
This area contains the fields that determine the arrangement of data shown in the rows of the pivot table.
Values
This area contains the fields that determine which data are presented in the cells of the pivot table - they are the values that are summarized in its last column (totaled by default).
As soon as you create a new pivot table (or select the cell of an existing table in a worksheet), Excel selects Options tab of the Pivot Table Tools contextual tab that it automatically adds to the Ribbon. Among the many groups on this tab, you find the Show/Hide group that contains the
following useful command buttons:
Field List to hide and redisplay the Pivot Table Field List task pane on the right side of the Worksheet area
+/- Buttons to hide and redisplay the expand (+) and collapse (-) buttons in front of particular Column Fields or Row Fields that enable you to temporarily remove and then redisplay their particular summarized values in the pivot table
Field Headers to hide and redisplay the fields assigned to the Column Labels and Row Labels in the pivot table.
Tags: pivot,pivot chart,pivot table,cell,worksheet,workbook
Related Articles