How do I use pivot tables?
The Pivot Table is powerful, but mysterious tool found in MS Excel.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 thedescriptive sum of your independent datastatistics (ifcount, youmaximum, wantminimum, toaverage, compare one partetc) of your data withbroken another,down forby example).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. KeepUse thevariable headersor oncolumn names in your data,dataset, thisas helpsthey 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 clicksome onprefer “New Worksheet” andto hit Finish.
A table that is seperated into a column, row, and data boxes. A tool panel called “PivotTable Field List” will pop up with the data you selected. Click on your a data set and drop it into the row. Click on the other data set and drop it into the column. Now your table is defined by the two data sets you’re tryingfinish” to crossgo tabulate. Depending on what you want to find the sum of, you take that data set and drop it into the data. The data will give you the sum of all the data you’re trying to compare. To see the break down, gostraight to the “PivotTableblank” Fieldpivot List”table andon doublean Excel worksheet, it is probably easier at this point to first click on the data“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 placesto “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 field.(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