5.71 FAQ-983 What's the difference between missing values and blank cells?

Last Update: 12/2/2022

Blank cells are empty cells with no contents at all. Missing values cells are a kind of data which represent unknown, uncollected, or obviously wrong values. For example, the nonresponse answers collected from a questionnaire where people refused to give their personal information. Or, the meaningless values resulting from some calculation, such as "0/0".

Missing values have different displays in Origin, depending on the cell format.

  • When cell format is Text, missing is always displayed as blank cell.
  • When cell format is Numeric, missing is always displayed as "--".
  • When cell format is Text&Numeric, missing can be displayed as blank cell or "--".
    If you delete a cell by either clicking Delete key or using Edit: Clear menu, the data will be cleared and a blank cell remains. You can use Ctrl+Delete key to convert a cell data into "--".


Useful LabTalk functions to check if a cell is missing value, blank cell, NotANumber (NANUM), etc:

Missing blank.png

They can also be used in Custom Filters to filter rows

  • x!=0/0 will filter out both blank, missing value and text
  • !isNA(x) will filter out rows not numeric (blank, missing, and text)
  • !isempty(x) will filter out blank cells only
  • x!="--" will filter out cells with -- showing only
Missing filter.png


How they are treated in different aspects of Origin:

Blank cell Missing value Notes
Graphing Excluded Excluded
  • In 2D graphs, if you want to connect line across missing data/blank cell, select Format: Page and go to Display tab
  • In Contour graphs, there is an option on the Colormap/Contour tab of the Plot Details dialog to specify the color of missing values
Analysis and Calculation Excluded from most analyses and calculation. The calculation performed on blank cell usually returns missing values Excluded from most analyses and calculation. The calculation performed on missing values usually returns missing values
Cell Formula/
Set Column Values
In most formulas, the input including blank cells returns missing values In most formulas, the input including missing values returns missing values
  • When you use the LabTalk function Average, both blank cells and missing values will be excluded
  • If you try Istext, you will get F for missing value and T for blank cell
Statistics Excluded from most statistics tools Excluded from most statistics tools
Set as Grouping column Excluded Excluded
Sort Treated as missing values. You can choose to treat missing values as the smallest or largest when sorting a dataset (in the Nested Sort dialog)
  • In other cases expect sorting, missing values are considered the smallest value in a dataset
Find and Replace Leave Find what edit box empty to find blank cells Enter "--" to find missing values Enter "-" to find both missing and blank cells
Set as Categorical Excluded Excluded
Import Imported as blank cell Imported as "--"
Statistical tips in Status bar Excluded

Missing and blank diff 1.png

Count missing value as a factor

Missing and blank diff 2.png

Copy/Paste column, especially column beginning with some blank cells (e.g. time series) Blank cells remain Missing values remain
  • In Origin 2018b and earlier, when you copy a column beginning with some blank cells and then paste it, the beginning blank cells will be trimmed. Missing values "--" will remain on the other hand
Special Function: Sum
  • Blank cells returns missing values when you use "Sum" in Column Mode

SumInCol BlankCell.png

  • Excluded when you use "Sum" in Row Mode

SumInRow BlankCell.png

  • Missing values remain when you use "Sum" in Column Mode

SumInCol MissingV.png

  • Excluded when you use "Sum" in Row Mode

SumInRow MissingV.png


Keywords:empty cell, missing, null, nanum