IBM Support

Enhancement Request to have an option on Excel Export to export SYSMIS as blank cells or #NULL

Technote (troubleshooting)


You work with IBM SPSS Statistics and have a data file with numeric variables. Some of the cells in the numeric variable do not have values and are SYSMIS.
When you export this file to Excel XLS or XLSX file format you see the SYSMIS cells being displayed as #NULL! in Excel.

The missing values are all exported as #NULL.  If your file has a lot of missing values, you end up with dozens of cells
with the #NULL notation. You find this is annoying.  You have to use Find/Replace to remove them after export in Excel.

Resolving the problem

This is functioning as designed. Missing values in numeric variables should always be exported as #NULL! values for Excel files.

See SAVE TRANSLATE syntax command in Command Syntax Reference Guide pdf on menu Utilities.
On chapter Operations -> Spreadsheet it is stated:

"* System-missing values are translated to #NULL! in Excel files."
However, an Enhancement Request was submitted to include a new option in a future SPSS Statistics release on the General (or Data)
tab of the Options dialog where a user can select how sysmis are exported, "NULL" or 'Blank", with Blank being the default.
(ER ECM00147254)

Historical Number


Document information

More support for: SPSS Statistics

Software version: 19.0, 20.0, 21.0, 22.0

Operating system(s): Linux, Windows, iOS

Reference #: 1486659

Modified date: 2015-08-06