-- -- CSCAR  
-- --   center for statistical consultation and research  
School of Social Work
--
about us
about us
location
workshops
software help
external resources
spatial
jobs
contact
search
--
The Center For Statistical Consultation and Research
3550 Rackham Building
University of Michigan
Ann Arbor, MI 48109-1070
cscar@umich.edu
.
Software Help
 
Preparing Data: From Excel
How to Set up the Excel File:

  • Place the variable names in the first row. Be sure the names follow these rules:
      • variable names can be no more than 8 characters long
      • variable names must start with a letter
      • variable names may only have letters, numbers, or underscores in them
      • do not use following characters in variable names: %,$,#,@,!,+,*,~,",.,-,.
      • no blanks in variable names
      • be sure that each variable name is unique (no duplicate variable names)
      • be sure variable names are on the first row only!

  • Only include the raw, un-summarized data. Delete extraneous data in your Excel file, like row or column totals, graphs, comments, annotations, etc. To prevent "ghost" rows and columns, copy only the raw data onto a new worksheet, and save from there.
  • Include a unique identifying number for each case. Sometimes you may have more than one identifier, such as Household ID and Subject ID; place these in separate columns. If you have several spreadsheets containing data on the same individuals, include their identifier(s) on each sheet.
  • Only include one value per cell. Don’t enter data such as "120/80" for blood pressure. Enter systolic blood pressure as one variable, and diastolic blood pressure as another variable. Don't enter data as "A,C,D" or "BDF" if there are three possible answers to a question. Include a separate column for each answer.
  • Don't leave blank rows or columns in the data.
  • Don’t mix numeric and character values (e.g. names and ID numbers) in the same column.
  • While character variables are allowed in statistical packages, they are not as flexible as numeric variables, which are preferred. Use numeric values when feasible.
  • Date values are best entered in three columns: one for month, one for day, one for year. You can change them into date values in your statistics package later.
  • If you have missing values, you can indicate them with a numeric code, such as 99 or 999, or you can leave the cell blank. Be sure, if you use a missing value code, that it cannot be confused with a "real" data value.
  • Save the spreadsheet with values only – not formulas.
  • Do not underline text, or use boldface or italics.
  • An excerpt from an ideal Excel data set might look like this:
     

    A

    B

    C

    D

    E

    F

    1

    id

    time

    numstems

    experim

    height1

    height2

    2

    1

    1

    1

    1

    438

    452

    3

    2

    2

    1

    2

    584

    952

    4

    3

    2

    1

    3

    688

    415

    5

    4

    1

    1

    1

    683

    933

    6

    5

    2

    1

    2

    157

    331

    7

    6

    2

    1

    3

    255

    572

    8

    7

    1

    2

    1

    707

    452

    How to Save the Excel File:

    Version 4.0 Worksheets can be read by most statistical packages. To save your Excel file in version 4.0, go to the File menu and choose Save As... and then select Excel 4.0 Worksheet (not Workbook) as the file type. You will be able to save only one worksheet at a time in Excel 4.0 format. To preserve your original Excel data, use a different name when saving in this special format. To be sure that the file name will be easily recognizable on any system, use a name not longer than eight characters, and add the extension .xls. If you have several worksheets, they will need to be saved individually and merged in the statistical package you are using.

    The consultants at CSCAR can help you with this. Contact us.

     
    CSCAR Home | About Us | Location | Workshops & Seminars | Software Help | External Resources | Spatial Analysis GIS | Contact Us | Search
     
    --
     
    Copyright © 1998 - 2001 The Regents of the University of Michigan, Ann Arbor