Pivot Tables

• This is excel chapter 5, all but section 1.
• Tables are good for filtering and sorting data.
• Subtotals are good for summarizing data in one or two dimensions.
• Pivot Tables are good for summarizing and exploring data.
• Our data
  • This worksheet:
    • Is the same as the previous data except
      • I have stripped the unknown data entries (n)
      • I have removed other blank cells in the table.
      • I have removed the first column.
      • I have added a table of state FIPS code on the state fips codes tab.
• First build two tables.
  • As before
    • Select a cell in the data table
    • Select Insert Table
    • This time, name the table as well.
      • Table Tools Design tab.
      • Properties group.
      • Table Name box.
    • I called my RawData and FIPS
• This data has a relation
  • Think vlookup
  • The State FIPS Code field in the ssamatab2 table
  • And the FIPS Code field in the state fips codes table.
  • The first tells us where to find related data in the second.
  • Database people like this sort of decomposition
• Excel supports relations
  • Data tab
  • Data Tools group
  • Relationship
  • New relationship
  • Select the tables and fields that hold the same data.
  • You can build multiple relations.
• Build a pivot table
  • Select a cell in the table (data table)
  • Insert tab, Tables group, Pivot Table command.
  • Since we are using a relationship, select the Add this data to the Data Model
  • This will open a pivot table in a new tab.
  • Note the Pivot Table Fields, and select All
    • This will show all of the tables linked by relations.
  • Let's look at unemployment by state.
    • Drag the State Name from the FIPS table into the ROWS area.
    • Drag the Year from the Raw Data table into the COLUMNS area.
    • Drag Unemployment Rate from the Raw Data table into the Values area.
    • Summing unemployment rates, which is what is happening
      • Click on the drop down menu in values
      • Select Value Field Settings
      • Select Average.
      • While we are here, we can format by selecting Number Format
      • Or we can Field Settings in the Active Field group of the Analyze tab.
  • Right now EXCEL has decided to add total rows and columns.
    • On the Design tab in the Layout group, slect Grand Totals and turn these off.
  • Let's look at a second level of data in each dimension
    • Drag Selectd Area and Division into the rows, below the state name.
    • Drag the Months field into the Columns area.
  • Notice we can collapse and hide as in the Subtotals tools
  • We can also filter and sort as in the Table tool.
• This tool is powerful but it can be abused.
• You can compute statistics that don't make sense.