Reading Excel files

This topic applies to Excel 95 and later files. To read Excel 4 or earlier versions, see the topic Reading Older Excel Files and Other Spreadsheets.

To import an Excel file

  1. From the menus choose:

    File > Import Data > Excel..., or drag and drop an existing Excel file directly into an open IBM® SPSS® Statistics instance.

  2. Select the appropriate import settings.
    Excel files can contain multiple worksheets. By default, the Data Editor reads the first worksheet. To read a different worksheet, select the worksheet from the list.
    You can also read a range of cells. Use the same method for specifying cell ranges as you would in Excel. For example: A1:D10.
    Read variable names from first row of data
    You can read variable names from the first row of the file or the first row of the defined range. Values that don't conform to variable naming rules are converted to valid variable names, and the original names are used as variable labels.
    Percentage of values that determine data type
    The data type for each variable is determined by the percentage of values that conform to the same format.
    • The value must be greater than 50.
    • The denominator used to determine the percentage is the number of non-blank values for each variable.
    • If no consistent format is used by the specified percentage of values, the variable is assigned the string data type.
    • For variables that are assigned a numeric format (including date and time formats) based on the percentage value, values that do not conform to that format are assigned the system-missing value.
    Ignore hidden rows and columns
    Hidden rows and columns in the Excel file are not included. This option is available only for Excel 2007 and later files (XLSX, XLSM).
    Remove leading spaces from string values
    Any blank spaces at the beginning of string values are removed.
    Remove trailing spaces from string values
    Blank spaces at the end of the string values are removed. This setting affects the calculation of the defined width of string variables.
  3. Click OK.