1. Home
  2. Sheets


These options let you customize the default behaviour of your spreadsheets.

  1. To access this dialog select Tools | Spreadsheet Options then click the Sheets tab.
  2. Set options as required then click OK to close the dialog.

Auto update Genstat

When selected, data will be updated in the Genstat server (memory) whenever you move the cursor focus to another window or menu. If the spreadsheet is loaded from a file, the entire data set is sent to the Genstat server on the first update. Subsequent updates only send modified values.

If a new spreadsheet is opened with Genstat data, only the modifications are transmitted. If you disable this option, you have to use one of the Update items from the Spread menu to ensure that changed data values are updated before proceeding to use other menus to analyse data.

For very large spreadsheets, updating the data to Genstat can be time consuming. The Maximum file size option (below) lets you specify a limit for the size of spreadsheets that allow automatic updating data to Genstat.

Therefore, any spreadsheets larger than the value given will have automatic updating disabled and the spreadsheet must then be updated by selecting Spread | Update and choosing an option.

Maximum file size Specifies the maximum file size for using auto update (above). There is no maximum file size in Genstat, although there may be a practical limit with regards to loading, analysing and updating.
Auto refresh sheet from Genstat When selected, data in the spreadsheet is updated from the Genstat server whenever changes have been made due to calculations in the server. Newly calculated columns will be automatically added to the end of the spreadsheet.
Remove empty rows and cols on save Removes empty rows and columns when saving spreadsheets to files.
Prompt for update on close When you close a spreadsheet Genstat will prompt you to update the server with any changed values.
Copy missing as asterisk (*)

Missing values (or empty cells in text columns) can be represented as null fields or asterisks (*) when copying cells to the clipboard. When null fields are written to the clipboard the <TAB> separators are still included.

The appropriate choice will depend on the intended destination of the pasted data; for copying to a spreadsheet via the clipboard a null field should be used, whereas if the data is being inserted in a text file asterisks may be a better representation.

An asterisk (*) is displayed in the status bar when this option is selected. The current default is applied to each spreadsheet on opening; subsequent changes to the Spreadsheet Options menu do not affect open spreadsheets. However, the shortcut Ctrl+Shift+F8 can be used to toggle the setting for the current spreadsheet

Copy with full precision By default data copied to the clipboard is just what is displayed in the spreadsheet. If the number is 1.996745 displayed with 1 decimal place in a cell, then 2.0 will be copied to the clipboard. With this option turned on, the number in the cell (in this example 1.996745) will be copied to the clipboard.
Clear selection after operation Automatically removes spreadsheet selections on completion of editing operations that use the clipboard (from the Edit menu, Copy, Cut, Paste and Paste Special).
Show new from clipboard options

Controls whether a dialog specifying the options for creating a new spreadsheet from clipboard data is displayed when you copy data onto the clipboard then select Spread | New | From Clipboard.

Copy dates as displayed

This option is enabled when the Paste with full precision option (see above) has been selected. When selected, dates are copied to the clipboard as displayed within the column (e.g. as text). If this option is not selected dates will be copied to the clipboard using its numerical date/time value. Genstat stores a date/time value as an 8 byte double precision real value.

The integer part of the value is interpreted as the number of days from a base date, and the fractional component as the time during the day, as a fraction of 24 hours (e.g. 0.25 = 6 hours). For more information on how Genstat interprets and handles dates please refer to Formatting Columns as Dates or Times.

Use Excel binary clipboard

Controls how data is copied from Excel to Genstat using the clipboard. When copying data to the clipboard within Excel the data are copied as text exactly as they are currently displayed within Excel. This means that numbers displayed with a fixed number of decimals will only be copied with that number of decimals and this can result in the loss of some precision.

When this option is selected, Genstat will read the underlying binary values for the data on the clipboard, and the associated formats (e.g. date format) rather than the text displayed on the clipboard. Therefore, numerical data will be copied with full precision and date/time data will be copied as dates.

Note: Excel has an option to base dates from 1904 rather than 1900. Excel does not pass the base date as part of its BIFF format, so if you create date columns using the clipboard when the base date in Excel is 1904, this will produce incorrect dates. The only way to get correct dates is to use the File | Open menu or the Excel import wizard. To create a 1904 base date so that it displays correctly, a value of 1462 must be added to these columns.

Maximum columns Sets the limit on the number of spreadsheet columns that one spreadsheet can contain. You may enter any value between 250 and 50,000 columns. The new value will not take effect until you exit and restart Genstat. Setting this field to a value much higher than you use will create an unnecessary memory overhead.
Maximum undo operations Sets the limit on the number of spreadsheet operations that are recorded in the Undo history. Setting this value to 0 will disable Undo for spreadsheets: this reduces spreadsheet memory usage and increases the speed of many operations.
Use import wizard when opening an Excel file Specifies whether the import wizard is used when opening Excel files. The wizard provides you with a step-by-step guide for importing data from your Excel files. If you choose not to use the import wizard you will instead be able to set import options using a legacy dialog that will appear when you open an Excel file.
View/edit all SQL statements Specifies whether the SQL statement from any ODBC operation is displayed before being run. The SQL statement may be edited while it is being displayed.
Automatically resize window width When selected Genstat will automatically change the width of a spreadsheet window to show as many columns as possible. If columns are inserted or made wider the window width will increase and if they are deleted or narrowed the window width will decrease.
Cornell ecology file extensions

Cornell Ecology Format files (also known as Cornell Reduced Condensed Format) are text files in a special format. There is no standard extension for these files, so you must specify the file extensions to use for this format.

The default extensions interpreted as being this format are CEP, CRC, SPE and ENV, but you can change this here so that text files with the specified extensions will then be read into a spreadsheet rather than opened into an edit window. Enter the file extensions to be interpreted in CE format, separating them with semicolons (e.g. CEP;CRC;SPE;ENV).

Defaults Resets the spreadsheet options on this tab to their default values.
Updated on October 11, 2019

Was this article helpful?