David B. Weigle Information Commons
Library FindIt:
Excel and Data Analysis

WICshop handout September 2007
Presenter: Anu Vedantham

Description: We will show you how to use Excel to extract meaning from data. We will bring in data from webpages, convert and format data in automated ways, look at ways to make data cleaner and more reliable, graph trends and find meaning in masses of numbers.

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

Questions? Email wic1@pobox.upenn.edu or stop by the Weigle Information Commons

  1. Introductions
  2. Index card  write down one thing you wish you knew how to do in Excel
  3. Open first sample spreadsheet - This is a modified version of FL0007, one of many samples from Contextures.com
  4. Basics of moving around the data
    1. Ctrl->  go to right end of data
    2. Ctrl<-  go to left end of data
    3. Ctrl-End go to last filled cell on the sheet
    4. Ctrl-Home go to Cell A1
    5. Selecting a row or column  click on its name
    6. Shift-click selects adjacent cells
    7. Ctrl-click selects random cells
    8. Copy and paste works with most types of selections  sometimes gives errors with existing cell configurations
    9. Paste Special options
  5. Sorting, ranges and data generation
    1. Sorting  data format
    2. Ranges and ID numbers
    • Create a range: Insert / Name / Define
    • Go to a range: Edit / Go To
    • Use named range in any function easily
    1. Series Generation: Edit / Fill / Series / AutoFill
    • Add an ID column to the left of the data.
    • Use AutoFill to number the rows. This is very helpful when importing data from databases or websites.
    • Look at Autofill options for generating simple equations
    • This also allows trend generation and linear extrapolation
  1. Filters
    1. Data / Filter / AutoFilter - Try filtering by one field
    2. Filtering by multiple fields
    3. Top 10 options to look at high and low values
    4. Simple query options
  2. Pivot Tables
    1. Open second sample spreadsheet from Microsoft. Data / PivotTable - Lets analyze subsets of the data
    2. Taking snapshots of the data  mixing filters and pivot tables
    3. Looking at the code behind pivot tables  some cautions about formula building
  3. Conditional Formatting
    1. Open third sample spreadsheet -This is adapted from the Amherst College tutorial linked below.
    2. Go to A1 and choose Format / Conditional Formatting
    3. Try setting positive numbers to green.
    4. Name the full range as Data
    5. Use Copy / Go to Data / Paste Special / Formats to replicate the conditional format. Notice how changes stand out.
    6. Now try a double format  red for more than 20 degrees and blue for less than -20 degrees
    7. Now try a relative formula  increases of more than 20 degrees from the previous year to be color-coded  (this would start at the second row).
    8. Now try a formula that is dependent on a cell from another sheet.
  1. Reference resources
    1. Basic functions overview from Georgetown University:
    2. Compatibility pack for Office 2007:
    3. Regression Analysis Tutorial from Kardi Teknomo:
    4. Amherst College Tutorial on data fit and analysis  we will use their sample files on global climate history:
  2. Online Tutorials and Videos
    1. Index of videos at About.com:
    2. VLOOKUP Tutorial on YouTube:
    3. In-cell Graph Tutorial on YouTube:
    4. Better Solutions.com  wide range of Excel Tutorials
    5. Allan Wyatts Excel tips on Data Validation
    6. Auto Filter discussion from University of Wisconsin:
  3. Q&A from index cards
  4. Graphing  discussion and handout
*