Using Excel Files in STATISTICA

In addition to using the traditional STATISTICA spreadsheet, you can open Excel files in a STATISTICA window and then perform analyses using the Excel file as your data source. STATISTICA supports analysis from an Excel workbook that is embedded in a STATISTICA workbook or an Excel spreadsheet that is in a standalone window. Your Excel workbook can have data on more than one sheet. By default, the active (visible) worksheet is used; however, you can easily switch to another worksheet by selecting the desired sheet the same way you would if the file were open in Excel, i.e., click on the sheet tab at the bottom of the workbook window. Note that, when an Excel file is open in STATISTICA, Reporting Tables are available from the Data menu.

See also, Opening Document Objects in STATISTICA.

Selecting the data for analyses. When an Excel worksheet is opened in STATISTICA, the Excel and STATISTICA menus merge, enabling you to access key functionality for both applications. To perform analyses using the data in the Excel worksheet, select the appropriate analysis from the Statistics or Graphs tabs (e.g., Statistics - Basic Statistics).

The first time you perform an analysis with worksheet data, the Select Excel Range for the Analysis dialog is displayed so that you can specify whether the variable names and/or case names are included in the worksheet. After you click OK in the Select Excel Range for the Analysis dialog box, the Startup Panel for the selected analysis or graph is displayed, and you can proceed with the analysis as usual. (Note that if the Review/Modify column types before importing check box is selected, the Review/Edit Column Types dialog box will be displayed before the Startup Panel is displayed.)

When variable names are not included with the Excel worksheet, STATISTICA assigns variable names, i.e., Var1, Var2, Var3, etc. As with STATISTICA spreadsheets, all values in a column are used for the selected analysis, unless case selection conditions are used.

Data type. STATISTICA enables users to define the data type for the specific columns. Data types include numeric, text, mixed numeric and text, and missing data. Empty cells in an Excel worksheet are always treated as missing data, and when a numeric column contains text values, those values are also treated as missing data.

STATISTICA provides a default data type for all columns based on the first few rows of data; however, you can change this default type once your worksheet is displayed by selecting Properties from the File menu to display the Select Excel Range for the Analysis dialog box, which contains your worksheet's row and column default settings. Click OK to display the Review/Edit Column Types dialog box, which displays a table with your worksheet's column headings and default data types.

Select the column heading whose data type you want to change (you can also select multiple column names using the standard SHIFT+click or CTRL+click commands in order to change more than one column type at the same time), and click the Edit button to display the Change Import Column Type dialog box. In this dialog box, select the Numeric, Numeric with Text Labels, or Text option button to define the selected column's data type. If you select the Text option, the Length text box is enabled allowing you to enter the length for your text column. Click the OK button to save your changes. Repeat for each additional column data type you want to change.

Display format. As much as possible, the Excel display format for a column is propagated to analyses in the same manner as STATISTICA display formats. This includes, specifically, Date formats,Time format, Date/Time format, and the number of displayed decimals.

Operations with analyses. As with STATISTICA spreadsheets, Excel documents can be specified in the Analysis automation method, i.e., Analysis Macros. Furthermore, graph auto-updating features and analysis write-back options (e.g., writing QC cause/actions) can also be used when an Excel worksheet is used for the data source.