Tips for Manipulating Microsoft's Excel Pivot Tables

Microsoft's Excel spreadsheet software for the Office97 suite and later has a way to manipulate data called Pivot Tables. This provides the user an easy-to-use, intuitive, and user-friendly method for displaying quantitative data for one or more variables. The following are some basic tips for manipulating pivot tables.

Field and Data Variables

Pivot Tables must have at least one data variable with values that it can manipulate arithmetically, e.g., sum, or can count as unique values. Pivot Tables must also have at least one field variable that displays one or more values that the data can be categorized by. For instance, a Pivot Table could include a data variable with the number of Medi-Cal beneficiaries, and a field variable of "county" with values of Alamada, Contra Costa, etc. Or it can contain a data variable of unique SSNs that it can count, accompanied by the field variable of "county."

Making a Pivot Table

A Pivot Table is created from a list or data base in Excel, Access, or some other importable data base. Each variable in this data base must have a name (in Excel, the first record). (To use a data base outside Excel, the Query software must be installed from your Microsoft Office suite.) Assuming there are at least two variables in this data base, one for a field and one for a data variable, a Pivot Table can be created by selecting Data from the Toolbar, then selecting Pivot Table Report, then following the steps presented. When a field is positioned above the Pivot Table, it's Orientation is "Page." When showing as a part of the Pivot Table as a column variable, it's Orientation is "Column." When displayed as a row, it's Orientation is "Row."

Note that a Pivot Table carries the entire data base from which it was built even when the originating Excel data base worksheet is deleted after the Pivot Table is created. (The advantage of deleting the worksheet with the data base (not the Pivot Table) is that the size of the file is reduced substantially.)

Creating Column and Row Headings from within a Pivot Table

After the Pivot Table is built, to display the data according to the values for one of the fields, drag (by selecting and holding down the left mouse button) the field either directly above the Grand Total or directly to the left of Grand Total, then drop the field by releasing the mouse button. In Office97, when the field is in the correct position for displaying as a row variable, you will see a checkered box in the form of a landscape rectangle (a wide, short box); in Office2000, the box will have a patch of blue on top. To display the values for a field down the left side of the page, in Office97, drag the field to the left of the Total for the data variable, and release when the box is in the form of a portrait rectangle (a narrow, tall box); in Office2000, the box will have a patch of blue on the left.

Pivot Tables permit the user to nest variables, that is, to put more than one variable in the row or column position such that one field's values are categorized within each of another field's values.

Subsetting to Selected Values

Fields in the Page can be subsetted such that data in the Table show for only a selected value for that field. To subset, click on the small triangle pointing down in the same cell as (All) next to the Page field name.

Grouping Selected Values

If data for more than one value need to be displayed, that is, values must be grouped in a manner different than what is presented in the Pivot Table, several methods can be used.

1) The "Grouping" method - With the variable containing the values to be selected in either the Column or Row orientation, select one or more of the values to be grouped together (by clicking on each value while holding down the Ctrl key), then, while the cursor is still on one of the highlighted values, right-click the mouse and select Group and Outline, then Group… to re-categorize these values into a new category (which can then be renamed within the formula bar).

2) The "Formula" method - With the variable containing the values to be selected in either the Column or Row orientation, right-click on the variable of interest, then select Formulas, then Calculated Item…, then build a new variable by Inserting items from the Items: box into the Formula box, and inserting a "+" symbol between items (for adding the counts together).

3) The "Hide items" method -- Double-click on the Page field, then in the Hide items: box, and then shade all those values you do not want to see the data for. Caution: You must remember when you hide values within a Pivot Table because there is nothing showing in the Pivot Table which reminds you.

Expressing Data as Sum, Count, etc.

The data variable shows up in the middle of the Pivot Table, and can be expressed as either a sum, count, average, etc. To change how data are expressed for the data variable, single-click on the right mouse button, then, in Office97 select the third from the bottom option, Field, (in Office2000, Field Settings...), then select the Summarize by: operation.

Note the other options available under the Pivot Table Field dialogue box (e.g., change the name of the field, change the format of the number, delete the variable (although at least one data field must show in the Pivot Table).)

Expressing Data in each Cell as a Column or Row Percent, etc.

To convert each of the cells into percents adding to 100% by column, row, or for entire table, follow the instructions above for "Expressing Data as Sum, Count, etc.," but then extend the Pivot Table Field dialogue box by clicking on Options, then click on the Normal selection under Show data as, and make your selection.

Other Options for Field Variables

Right-clicking on the Field variable, then selecting Options, then Advanced, permits the user to display the values for the field in the table by certain sorts, e.g., descending, or to limit the data values for that field to only the highest number of values, as selected to the user (e.g., the highest 10 values for a field).

Creating the List or Data Base from the Pivot Table

To create the list or data base that the Pivot Table was created from, just double-click on the Grand Total in the Pivot Table. To see the data base for records with only certain values for certain field variables, select the cell that meets the correct criteria, then double-click. Remember that if the resultant data base has more than 65,000 records, it cannot display all records in the Excel worksheet.