What Makes Pivot Tables So Valuable?
A pivot table is a valuable feature available in Microsoft Excel, among other spreadsheet software. It is a summarization table that allows fields to be dragged within axes, across axes (thus the name "pivot"), and above the tables to be used as drop-down menus. The function can be accessed in Excel from the "Data" drop-down menu of the Ribbon, at the top of the screen.
Other People Are Reading
-
Cross Tabs
-
Fields are represented in pivot tables as "cross tabs," which can be dragged and dropped to change the structure of the table. The vertical and horizontal axes can be layered with fields, thus allowing changes in summarization by layer.
Dragging fields above the table turns them into drop-down menus, called "page fields". Clicking through a page-field menu enables drill-down analysis, seeing the table for each value of the page field. If the arrow of a cross tab is clicked, values for the field to be hidden from the pivot table display can be selected.
Calculated Fields
-
The pivot table toolbar allows creation of calculated fields. To access this in Excel, click the "Pivot Table" button in the pivot table toolbar, click "Formulas", then "Calculated Field". Name the new field, then provide a formula for it, as a function of pre-existing fields.
-
Display of Summarizations
-
Double-clicking a cross tab opens a panel for summarization of the field in various ways, including sum, average, count, and variance.
External Data
-
Pivot tables are not confined to data sources within the same spreadsheet. When the pivot table is first created, the user is prompted to select whether the data source is external or internal. Selecting "external" triggers a request for the location of the data. For example, data can be pulled from an external "Access" table that is much too large to maintain in an Excel spreadsheet. This opens up the possibility of combining Excel functionality with Access capacity.
Pivot Chart
-
Later versions of Excel feature a pivot chart, which is based on a pre-existing pivot table. It is a chart featuring cross tabs and page fields, the same as a pivot table. Pivot charts are created by accessing the pivot table toolbar and selecting Chart Wizard. Alternatively, you can select the "PivotChart" item on the pivot table menu in the pivot table toolbar.
Programmability in Excel
-
Pivot tables are programmable in Excel. This is useful because certain features are lacking in pivot tables. For example, refreshing of data is not done automatically. Inserting a print button that refreshes before printing may be an option you, as a programmer, would want to offer users. Programming can also be used to restrict how others display the pivot table.
-
