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

Last Update: 12/19/2021

Missing values are a kind of data which represent unknown, uncollected, or obviously wrong values. For example, the nonresponse answer collecting from a questionnaire, such as those refusal to give their personal information. Or, the meaningless values resulating from some calculation, such as "0/0".

Missing value has different display 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 "--".
Blank cell Missing value Notes
Graphing Excluded Excluded
  • In 2D graph, if you want to connect line across missing data/blank cell, select Format: Page and go to Display tab.
  • In contour graph, 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
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
English | Deutsch | 日本語