1. Download Excel file for this workshop.
Pivot Table 101
1. Go to the worksheet labeled BABYDATA. Observe 15 cases, 3 variables.
- First, change the data into an Excel TABLE.
- Select the entire range of cells A1:D16. Under Insert click on Table. Practice sorting the data by the different variables until you get how that works. Important to note that the rows of values for each case "travel together." Don't proceed until you get what that means.
- THIS IS A PIVOT TABLE
2. Create simple pivot table.
- With the entire data table selected go to DATA > PivotTable…
- Accept all the defaults. This will create a new worksheet and the pivot table will be inserted there.
- Take a guided tour:
- 1 is where the pivot table will appear
- 5 is the list of variables - column headers - we have to work with
- 2 and 3 are where we will specify what variable will be the row variable and what variable will be the column variable in our pivot table.
- 4 is where we tell Excel how to count the data into the cells of the table
3. Tell Excel how to make the pivot table. What's in the rows? What's in the columns? How do we count cases into the cells?
- Drag "Species" from area 5 to area 2. Drag "Color" from area 5 to area 3. NOTICE what happened in area 1. Excel listed all of the VALUES of each variable to set up a bivariate frequency table.
- Drag "Species" from area 5 to area 4. As you do, it will say "Count of Species" (you may have to expand the dialog box a bit to see this). Counts should now appear in your pivot table. It will be formatted in an unfortunate way. We'll fix that.
4. Click anywhere to "submit" the pivot table. Now we'll tweak the format.
- Select the columns containing the table and adjust the width so it's the same on all the columns.
- Center the data in all the columns (click the centering button while the columns are highlighted).
- Do the same for the rows, making them a little wider so we can read them more easily.
- Center the data vertically while you have the rows highlighted.
5. Now let's format the thing so we recognize it.
- Select the cells containing the pivot table (B3 to E8 - just clicking on B3 should get you the range). Then click COPY or control-C or command-C on macOS to copy the pivot table. Then under "Paste" select "Values"
- Add a new column to the left of column A. Select the column and right click and choose INSERT.
- Delete the text in these cells "Count of Species," "Column Labels," and "Grand Total"
- Select the three cells above cat dog mouse and MERGE. Type "Species" in this cell.
- Select the three cells to the left of black brown orange and MERGE. Type "Color" in this cell.
- Select the 9 cels in the body of the table (the ones bounded by cat dog mouse and black brown orange). Click on the border tool and select the full monty borders all around.
That's the basic way you get from a dataset to a bivariate table.
The Environmental Data
6. Worksheet "EnvSurveyData" contains 30 rows of data we saw in class last week. Familiarize yourself with this dataset. Turn it into an Excel table which means it is a range of cells where the columns are treated as variables and the rows as cases or "records." We can sort the table by any of the variables and the variable name row stays at the top and the data in the rows moves together.
7. A PIVOT TABLE is a cross-tabulation of two or more columns in an Excel table. Here are two very brief videos that provide a quick intro to pivot tables.
Quick and Dirty Frequency Tables
8. Highlight the columns containing our data - columns A through G and click on PivotTable under DATA. Accept the defaults except for where it says "Choose where to place the new pivot table." Click on Existing Worksheet and then navigate to cell A1 of the worksheet called FrequencyTables. Excel will fill in FrequencyTables!$A$1 in the dialog box. Click OK.
8. Excel jumps to the worksheet, sketches an empty pivot table on the left and opens a Pivot Table dialog box. This dialog box will let us use the variable columns from our input data to create cross tabulations.
The first frequency table we will create will be for the variable GENDER.
- Drag GENDER into the ROW LABELS area. Drag "another copy" of it into the VALUES area of the dialog box.
- Before we go, we need to make sure Excel is tallying the data correctly. We want Excel to just count the number of cases that go into each cell but it might want to so something else (like summing all the data values). Click the little i in a circle to see what Excel's plans are. If they are not to do "Count" then set Excel straight. You are in charge here, not Excel!
- Does your pivot table look like a frequency table? If so, you are set. Now repeat the above formatting steps to make it readable, presentable.
- Repeat for a second variable
9. Next we will reproduce the REDUCE by NOTCYNICAL table we saw in class.
- Go back to data. DATA > PIVOTTABLE…. Instead of defaults, let's put it below the frequency tables we just made.
- This time, select REDUCE for the rows and NOTCYNICAL for the columns.
- Drag either of these into the VALUE area. Check to make sure you are getting counts.
- Copy, paste values, and tweak the formatting.
10. Next we will create a percentage table below our bivariate frequency table.
- Proceed as above but with GENDER and REDUCE.
- We'll use the nice formatting as a template. Select the range of cells containing the table. Copy. Paste it a few rows below.
- We will now replace the contents of the cells in the copy.
- Percentage down. Click on the first column of the first row of the lower table. We'll enter a formula to compute the percentage. Type and equals sign and then click on the corresponding count cell above and then a slash for divided by and then click on the column marginal in the table above.
- We are lazy so we are going to use autofill. But to make this work we need to create an ABSOLUTE reference for the marginal total. We are planning to drag our formula - we want the count reference to change but we should always divide by the same cell. Return to the formula we just typed and put a dollar sign in front of the row number in the DIVISOR of the forumla
- For example, if you had typed =B12/B16 you would change it to =B12/B$16
- Drag the formula down the column over the other cell counts including the marginal (which should come up as 100%).
- Repeat for the other column(s).
11. Let Excel do table elaboration for you.
- Go back to your environmental data. Create pivot table on new sheet. REDUCE by NOTCYNICAL. But this time, put GENDER in the COLUMN LABELS box too. It should be ABOVE the other variable.
- Format up, copy and paste.