How do I use pivot tables?

The Pivot Table is powerful, but mysterious tool found in MS Excel that is similar to the “crosstab” analysis found in many powerful statistics programs. With pivot tables, you can create reports that sort and finds descriptive statistics (count, maximum, minimum, average, etc) of your data broken down by categories and also allow you to quickly restrict those statistics to particular subsets of your data.

To create a pivot table, open the workbook with your data. Use variable or column names in your dataset, as they help you organize your pivot table. Go to the Data menu and click on Pivot Table and PivotChart Report. The PivotTable and PivotChart Wizard should pop up, you will be asked to enter in a range you want to compare in your pivot table. I found it easiest to select the columns using your mouse, instead of entering in the range (it makes it easier deciding what data you want to compare). After you’ve selected your data, click next.

Although some prefer to hit “finish” to go straight to the “blank” pivot table on an Excel worksheet, it is probably easier at this point to first click on the “layout” button, which takes you to a page that allows you to set which variables you’ll use for your tables’ row, column, “page”, and data cells. The row and column variables are probably pretty straightforward, but the page and data cells are a bit more complex. Your page variables are dragged to the blank space to the upper left of your blank table layout and basically allow you to “filter” the data shown in your pivot table. For example, if you were doing a pivot table comparing educational attainment and presidential party vote, putting a party ID variable up in the page would then allow you to quickly restrict your table to just Republicans (or just Democrats). In terms of the data displayed, most variables revert to a “count” statistic when dragged onto the data field, but simply double-clicking them in the layout menu allows you to select from a variety of descriptive statistics.

Once your pivot table is generated, you can easily change these settings again by clicking on the pivot table and using the floating pivot table toolbar’s wizard button, which takes you back to the wizard. You can also drag variables directly off of this floating toolbar and into the pivot table. You can also choose options such as hiding specific values or grouping data (particularly helpful for generating month/year summaries of things like daily observations) by right-clicking on the relevant parts of your pivot table to access those settings.

It’s also sometimes helpful to copy and paste an entire pivot table (just select all the columns containing pivot table data and copy and paste them) to allow you to build up a series of different pivot tables showing results of interest.

Here’s a useful site with pictures to guide you through: http://www.cpearson.com/excel/pivots.htm