Pivot Tables

• General Notes
  • This is a useful skill if you ever want to explore complex data sets.
  • I know the Business Department continues to ask us to cover this topic.
• Creating Pivot Tables
  • He points out that pivot tables are great for creating summary information.
  • He dos a nice job of pointing out what it would take to build a pivot table by hand
  • And illustrating that a hand created table is not very flexible.
  • He points out that there are many database functions available to pivot tables.
  • Data must be in rows, organized by columns.
  • No blank rows
• Manipulating Pivot Table Data
  • He demonstrates how to change field order.
  • And rows and columns
  • And the use of the filters box.
• Grouping by date and time.
  • This shows some really neat ways to group dates in a pivot table.
• Grouping by other factors
  • Further information on how to group data.
• Using slicers to clarify and manipulate fields
  • Filtering data in a pivot table.
• Using Pivot Charts.
  • A nice extension to pivot tables.

Lending Club

• I will work with this file
  • It is rather large (5M+)
  • 42,535 loans
  • Data from Lending Club (https://www.lendingclub.com)
  • Actually from here
  • I have messed with the data just a bit.
• We would like to make sense out of this data
• Start by building a pivot table.
  • Click somewhere in the data.
  • On the INSERT tab, clock Pivot Table
  • Select OK
  • move to the new sheet created.
• First look at loans by purpose and length of employment
  • Drag Length of Employment into the ROWS box.
  • Drag Purpose into the Columns Box
  • Drag Purpose into the Values Box.
  • Select the box for 10+ years and drag it down below 9 years.
  • We can add an easy filter, by dragging Home Ownership to the filter box.
  • Now select Own in the filter area
  • We can switch the columns and rows by dragging the boxes from one to another.
• Let's build another to look at average interest rate for by date issued and loan grade
  • Drag date issued to the rows box
  • Drag grade to the columns box.
  • Drag rate to the values box.
  • Drop the down arrow for Sum of Rate and select Value Field Settings
  • Change Sum to be Average
  • Click on Number Format and select percentage.
  • Right click anywhere in the dates
  • Select Group
  • Select Months
  • Look
  • Select Years
  • Look
  • Unmark Months and select Quarters.
  • Look
  • on the Design Tab, look at the options in the Layout Box.
• Build a Pivot Chart.
  • You are adverse to risk, so filter out all but grade A,B and C loans.
  • Delete the "Date Issued in teh Axis box"