Improved Handling of Empty Cells in Origin 2019

Starting with Origin 2019, how empty worksheet cells are handled has been improved. The improvements were made in order to provide a cleaner-looking worksheet when the data has gaps in various places. The improvements also allow for easier visual determination of where data begins and ends in columns.

Before discussing the changes, let’s introduce the two types of “empty” cells:

  • Missing value cells: These are cells whose value is set to NANUM(Not A Number). They used to typically display the “missing value symbol” (- -). Starting with Origin 2019, they are visually empty but, behind the scenes, they have that value.
  • Unused cells:  These are cells that do not have any value, even NANUM. Typically, they are cells at the end of a column of data.

The first change is that, like cells with actual values, missing value cells will have a white background by default. Unused cells, however, will have a shaded background, allowing you to  differentiate which blank cells are missing values and which are unused. (Note: To modify this shading behavior, see Modifying Cell Shading Behavior below.)

The second change is about how empty cells are treated when either entering or pasting data into an empty column starting on a row after the first row. In older Origin, the preceding cells in the column were left as unused. This meant that, if included in data selection, they were not counted in most plots and analyses. Starting with Origin 2019, those empty preceding cells are treated as missing value cells for most plotting and analyses. This change means that if you do not want them to be included, you will need to deliberately exclude them from data selection or follow Hint #4 below.

Some Handy Hints
  1. Adding a value to a cell beyond the end of column data will fill the cell gap with missing values. You can’t have unused cells in the middle of a column of data.
  2. Clearing cells at the beginning or in the middle of a column of data converts the cells to missing values.
  3. Clearing cells at the end of a column of data converts the cells to unused.
  4. You can use Set as End from the context menu of either a selected row or cell to truncate the data at that row and make the rest of the  rows be unused. See Setting and Getting the End of a Column via LabTalk below.
  5. To force the display of the missing value symbol (- -) in a certain cell, select the cell (or cells) and hit Ctrl+Delete on keyboard.
  6. The Delete key has different behavior than the Delete context menu item. The Delete key simply clears the contents of the cell, while the context menu item actually deletes the cell itself, moving later cells up. If there is only one cell with a value in the column, the Delete key will set the entire column to be unused.
Setting and Getting the End of a Column via Labtalk

The Set as End command command is also available via LabTalk.

set name -e n sets the end of the column based on row index where  name  is a column range such as col(B)n is a 1-based row index such as 10. For example: set col(B) -e 10.

get name -e var Gets the end of the column based on row index into a variable var where  name  is a column range such as col(B) . For example: get col(B) -e nEndIdx.

Modifying Cell Shading Behavior

The display of cell shading for unused cells is controlled at the workbook level. It can be toggled on or off in the Window Properties dialog for the workbook using the Indicate unused cells with a shaded background check box. It can also be control via LabTalk via the page.OUTSIDERANGESPECPAINT  property where 1 = on and 0 = off. The degree of shading can be controlled via the @COR system variable. The default value is 45.  It is a “darkening factor” and the value is divided by 1000 to arrive at the percent darkening. For example 45/1000 = 0.045 or 4.5% darkened. 30 would be: 30/1000 = 0.030 or 3% darkened.

About Chris Drozdowski

Chris Drozdowski is a Product Support Engineer at OriginLab. He loves to talk to customers and educate them. He particularly relishes diagnosing and solving difficult, edge-case issues. As well, he contributes code to help solve problems or enhance user experience. In his down time at work, he likes to research and write about esoteric product features. Outside of work, he enjoys spending time with his family, having fun with C++, working on his aquarium, and exploring craft beers.

View all posts by Chris Drozdowski →

4 Comments on “Improved Handling of Empty Cells in Origin 2019”

  1. A colleague asked me for a simple Labtalk-Skript to search for empty cells and cells that contain “–” and “nan”.
    These cells should be masked from his data, so that they are not used in any further calculations.
    I wrote the following LabTalk Skript:
    loop (ii, 1, wks.ncols) {
    loop (j, 1, wks.nrows){
    if(isna(col(%(ii))[j])==1) { worksheet -s ii j ii j; mark -w1;};

    The Skripts runs properly but consumes a lot of time. We have data with 30 cols and 300000 rows.

    Is there any faster way to mask unwanted empty data fields in “numeric” and/or “text an numeric” cols.

    1. Hello,

      Please try this script. It runs in no time since it will check columnwise instead of cellwise.
      colmask irng:=1:0 cond:=eq val:=nan;

      We have menu for this tool.
      U can choose Column: Mask Cells by Condition… to see it.
      Set it and the choose the > next to Dialog Theme and choose Generate Script.
      It will dump the script u can use to Script window.

      The irng is input. I set it to 1:0 (1st column to last column). We use 0 for last col or row index.

      Let me know if it works.

      Thanks, Snow


メールアドレスが公開されることはありません。 が付いている欄は必須項目です