Next
When you select Microsoft Excel in the Export Tables dialog box, a number of additional options become available. To use this option, you need to have Microsoft Office 2007 or later.
Note: You may need to set an access security setting in Excel before you can run the export. If this applies to your machine, IBM® SPSS® Data Collection Survey Reporter will display a message telling you this. For step-by-step instructions on setting the security setting, see Enabling security access for Microsoft Excel, Word, and PowerPoint exports.
Display charts. You can optionally choose to export data in chart format. The export creates each chart on a separate worksheet immediately following the worksheet that contains the related table. Select a chart type from the drop-down list. To use a custom chart that you have created in Excel, type in the name of the custom chart. To specify a chart template (Excel 2007 only), click Browse... to select the desired file. For details of the way in which you can display data in charts, see Displaying Results in Charts.
Note: The chart type you select is used only if you have not specified a chart type for a table using the Table Properties dialog box.
The Excel worksheets that contain the tables are called T1, T2, T3, etc. The worksheets that contain charts are called T1_ch, T2_ch, T3_ch, etc. where T1, T2, and T3 are the names of the worksheets containing the tables to which they relate.
Hide Excel during export. Select this option to hide Excel during the export. This makes the export faster.
Launch Excel after export. Select if you want Survey Reporter to automatically launch Excel and open the file that contains the exported table(s) when the export is complete.
Save to file. Check this box and enter a name and location for the output file, or choose the Browse button to browse to the folder where you want to save it. If you do not specify a save location, and the Launch after export option is also not selected, you are prompted to either launch the associated application or select a save location.
Choose the Advanced button to view or edit the Advanced Excel Export Properties dialog box.
Display Properties:
Variable. Choose whether to use variable names or the more friendly descriptions for the variable texts in the tables.
Category. Choose whether to use category names or the more friendly descriptions for the row and column headings in the tables.
Borders. Select this option if you want the tables to have borders in Excel.
Headers and footers. Select this option if you want to export the headers and footers that have been defined for the table. When this option is selected, all of the headers and footers are displayed left aligned, regardless of the positions defined in the Header and Footer dialog box. This has the advantage that they are easily visible in a wide table.
Formatting for headers and footers. Select this option if you want headers and footers to be copied to the clipboard as formatted HTML. When this property is set to False, the annotation is copied as plain text, so that all tags (except <br/>) appear in the Excel output. Set this option to False in a server environment.
Base values. This option controls the display of base rows and columns. If you leave this box blank, all rows or columns containing bases are suppressed for all tables, so that they are not displayed in the output file. If you check the box, rows and columns are displayed or hidden according to what is specified in the definition for each table.
Chart options:
Chart series. Choose whether you want the table rows or columns to form the chart series. If you select the Chart category elements or Chart special elements box, ensure that the orientation of the categories or special items (in rows or columns) corresponds to the setting entered here. For example, to create a chart for a mean that appears on the top of a table, choose as columns.
Chart category elements. You can optionally create charts for individual categories, for individual variables, or for all variables on the table. Select:
• Per element to create a separate chart for each category in the variable(s).
• Per variable to create a chart for each variable(s).
• Per table creates a single chart for all variables on the table.
Chart special elements. You can optionally create charts for statistical items (such as the mean, minimum value, standard deviation, etc.). Select:
• Per element to create a separate chart for each statistical item in each variable.
• Per variable to create a chart for all statistical items in each variable.
• Per table creates a single chart for all statistical items in all variables on the table.
Base chart on. If you want to base charts on a cell item other than the default, select the cell item from the drop-down list. The cell item must be included in the table.
Display series base. Select this option to display the base for the chart series in the legend for the chart.
Display base for last series category. Check this box to display the base for the last data point in the chart legend. This option is applicable only when charting special items. If the count cell item is present, this is used. If not, the unweighted count cell item is used.
Display column statistics results. Displays the column IDs for a column proportions or column means test next to the category descriptions, and adds the column proportions test results to the chart above the relevant columns.
Chart percentages using scale of 0 to 100%. Check this box if you want percentages to be charted on a scale of 0 to 100%. If this option is not selected, the scale is based on the biggest value in the chart.
Worksheet options:
Use Excel styles. Select this option if you want to use styles to control the formatting. By default, the exported tables will look the same whether you use styles or not. However, styles make it easy to alter the look of your tables and apply standard formatting to multiple tables. If you want to use Excel to manipulate the data in the tables rather than printing them, you may prefer to export without using styles. See the topic Exporting Tables Using Microsoft Excel Styles for more information.
Printing: This option controls the print option that will be selected in the Excel file. Note this does not affect how the tables appear in Excel. It affects how large tables appear when they are printed. The options are:
• Repeat axes. This selects the Excel print option to repeat title rows and columns on every page so that when a table is split between more than one printed page, the table row and column headings are repeated on every page.
• Fit to page. This selects the "adjust to fit on one page" Excel printing option.
• None. This does not select any printing options in Excel.
Show column statistics results with first cell item. This option controls the display of significance letters for column proportion and column mean tests. If you leave this box blank, the significance letters are shown in cells after the cell items, as in the Results pane. If you check this box, significance letters are shown in the same Excel cell as the first cell item. Note that charts are not produced for tables with column proportion results shown with the first cell item.
Create a separate table for each cell item. Select this option if you want each type of cell contents to appear in a separate table (all of the tables are on the same worksheet). This is useful if you want to perform calculations on the output or set up your own charts in Excel.
Wrap description text. Select this option if you want to wrap long descriptions onto the next line.
Auto-fit column widths. Select this option if you want to automatically change the width of the table columns to accommodate the width of the text. By default, this option is not enabled.
Click to see a
picture of the Export Tables dialog box with Microsoft Excel selected
Click to close the picture