Excel and Pivot Tableswiclogo

WICshop handout December 2009
Presenter: Anu Vedantham

Description: We will explore Pivot Tables in Excel - a powerful way to analyze large datasets. We will use Adobe Connect Pro for the online session. This is our first online workshop. We recommend a headset with microphone for participation but you can also participate using a computer that has speakers.

Available online at http://wic.library.upenn.edu/multimedia/tutorials/excelpivot.html

Acrobat Room: https://pennlps.na4.acrobat.com/pennwic (Login as Guest with your full name)

Presentation Slides

Questions? Email wic1@pobox.upenn.edu or stop by the Weigle Information Commons. The Library ITA can help with Excel questions.

  1. Plan of Action
    1. Icebreaker online
    2. Check sound and get started. We will use the Office 2007 PC Version though many aspects will work on other versions and platforms.
    3. Download Start Spreadsheet and open on your computer. (An Office 2003 Start Spreadsheet version is also available.)*
    4. The presenter will demonstrate each step and then you can repeat on your spreadsheet.
    5. You can check the Finish Spreadsheet to see ideal results.
  2. Basic Process
    1. Start with a simple datalist
    2. Convert to an Excel Table and name
    3. Create Profit Pivot and explore design options
    4. Set up printing area
    5. Explore conditional formatting
    6. Create Revenue Pivot and Chart
    7. Move Chart to a new sheet
    8. Format one data point for emphasis
  3. Program Evaluation Online Form

*Data is from a training spreadsheet from the Microsoft website's Office 2003 training materials.

Online Resources - WIC has Lynda.com licenses for patron use in the Weigle Information Commons, and Lynda has an extensive online course on pivot tables. We have online tutorials on Excel Basics, Excel and Data Analysis, Excel Charts and Excel and Stats.