Notes on excel pivot tables
A pivot table is an interactive worksheet table containing fields created from columns of data in an external list or table. The table is used to summarize and analyze data. The table supports drag and drop. Subtotals and totals are included automatically.
The pivot table wizard is used to locate and arrange fields and data from a database into a pivot table. The wizard displays a series of dialog boxes that are used to locate, arrange and move data from a data source (usually columns of excel data) into a pivot table.
Creating Pivot Tables
Specify the type and location of your source data (i.e. the range in the source spread sheet of your data). These data must contain (short) column headings. Then create a layout and spicify a worksheet destination.
- High light the top left corner of your source data (click on the cell which contains the column heading of the left most column of data.
- select Data, PivotTable Report...
- Select source (default is Excel list or database). Select Next >
- Modify range (if required). Select Next >
- Drag and drop then select Next >
- A source column into the ROW area. This column will contain the row titles in the pivot table. (e.g. Quantity)
- A source column into the COLUMN area. This column will contain the column titles in the pivot table. (e.g. Vendor)
- One or more source columns into the DATA area. These columns will contain the data to be analized (i.e. summed).
- Select destination for the pivot table. (i.e. where you want to put it) Select Finish.
The pivot table now contains all data from the source columns (i.e. filters were ignored in creating the table. The table contains sub-totals for each unique row/column/data item combination in the table as well as grand totals for each row/data item combination, column/data item combination and for each data item.
Editing Pivot Tables
The layout of the pivot table can be modified at by returning to the pivot table wizard.
- If the pivot table dialogue box is not displayed on the pivot table sheet, right click on a tool bar and select pivot table.
- Select (click on) the pivot table wizard icon. (It's a box with a double arrow pointing north west and south east)
- Select layout, then drag and drop columns of data either to or from the pivot table.
Changing field names, functions and formating
Field control is exercised through the field control icon on the pivot table dialogue box. The field control icon is a stick figure in a black circle (top left) with a curved double arrow pointing north and east bottom right.
- Select a box in the data area of the table. (i.e. click on a number)
- Select the field control icon on the dialog box.
- Type in a new name in the Name input field. (the source field name remains unchanged
- Select the summerization method in the Summarized by drop down box.
- To format the data, click on the Number... button, then select the desired formating.
The GETPIVOTDATA functin
The GETPIVOTDATA function requires two arguments: pivot_table and "name" separated by a comma. The pivot_table argument is either the cell or cell range in the pivot table that contains the data you want to retrive. The "name" argument is the pivot table data name for the data that you want to retrive.
Page hits since 03/04/2002