9 Workbooks Worksheets Columns

Less grand image workbooks 650px.png

Workbook, Worksheet and Column Basics

The Origin workbook is a nameable, moveable, sizeable window that provides a framework for importing, organizing, analyzing, transforming, plotting and presenting your data. Each workbook is a collection of one or more worksheets. Each worksheet contains a collection of columns and each column contains rows of cells.

Workbooks 01C.png

Naming Workbooks, Worksheets and Columns


Workbooks
  • A Workbook has a Short Name and an optional Long Name and Comments. Origin uses the Short Name for internal operations.
  • Short Name must be unique within the project file, can contain only alpha-numeric characters (letters and numbers), must begin with a letter and are limited to 13 characters.
  • A Workbook Long Name is optional, need not be unique within the project file, can use any characters in any order and has a limit of 5,506 characters (including spaces).
  • To name a workbook, right-click on the window title bar and choose Properties. Here you can edit Long name, Short name and Comments. Use the Window Title drop-down to control which name(s) show on the window title bar.
Worksheets
  • A Worksheet has a Short Name and optional Long Name and Comments.
  • The Short Name must be unique in a workbook.
  • A Worksheet Short Name has a 64 character limit, including spaces. It can contain special characters but must begin with an alpha-numeric character. These special characters are not allowed: {}|"<>()![].
  • A Worksheet Long Name is optional, need not be unique within the project file, can use any characters in any order and has a limit of 5,506 characters (including spaces).
  • To name a worksheet, double-click on the sheet tab, or right-click on the tab and choose Name and Comments. More details are listed under Worksheets, below.
Columns
  • A Column has a Short Name and an optional Long Name.
  • The Short Name must be unique within the worksheet. When spreadsheet cell notation is enabled (default setting), you cannot edit the column Short Name (see Column Short Name Restriction). When cell notation is disabled, you can edit the column Short Name. When editing Short Names note that they must use only alphanumeric characters (no special characters), must begin with a letter or number, and cannot exceed 18 characters.
  • A Column Long Name is optional, need not be unique within the project file, can use any characters in any order and has a limit of 30,000 characters. The Long Name can be edited directly by clicking in the Long Name cell or by right-clicking on the column header and choosing Properties from the shortcut menu.
  • Dialog box and Status Bar references to data range will use Long Names, provided that (1) Long Name exists and (2) you have selected Use Long Names when available (Preferences: Options: Miscellaneous). Otherwise, Short Names are used.

Workbooks

By default, when you start an Origin session or open a new project, You are presented with the New Workbook dialog box.

Workbooks new workbook close.png

If you click the Close button, the dialog box closes and, simultaneously, a new workbook window based on the Default Template is added to the workspace. This workbook will be the same one that is added to the workspace when you click the New Workbook button Button New Workbook.png on the Standard toolbar. Each time you click this button, a copy of this template is added to the workspace. There is much more to say about templates. To read further, begin by looking over the section below on Workbook Templates.

By default, an added workbook window is assigned a Short Name of BookN, with N reflecting the order of window creation. You can rename the workbook with something more meaningful:

  1. Right-click on the workbook window title bar and choose Properties....
  2. Give your workbook a Long Name (optional) and/or a Short Name and choose to display either or Both in the window title bar. See the above table for rules on workbook naming.

The Properties dialog has a Comments box for entering text. These Comments display as a workbook window tooltip in Project Explorer and become searchable when using the Edit: Find in Project tool.

In Origin 2020, the Object Manager adds support for displaying and manipulating workbook content:

  • List all sheets in the active workbook.
  • Click a sheet in Object Manager to activate the corresponding sheet in the workbook.
  • Right-click in Object Manager for access to common worksheet operations, including Delete, Insert, Add, Move, Copy, and Rename.


UG UI OM workbook.png

Spreadsheet Cell Notation (SCN)

Origin workbooks support Spreadsheet Cell Notation (SCN). Spreadsheet Cell Notation allows the sort of cell-level calculations that are familiar to users of spreadsheets (more details below).

Starting with Origin 2019b, SCN remains ON by default for all new workbooks but the SCN icon has been removed.

  • By default, SCN is ON for all new workbooks.
  • In Origin 2017 - 2019, when SCN was enabled in the workbook, you saw this icon Spreadsheet cell notation mode.png in the upper-left corner of the worksheet, signaling that SCN was turned on.
  • Beginning with Origin 2019b, the SCN icon is hidden (by default) but SCN remains enabled (also by default) to make room on the workbook window for the Data Connector icon.
  • Most users will want to leave SCN enabled but in rare cases (e.g. you need to customize the column Short Name), you may want to turn SCN off. To disable SCN, right-click on the workbook title bar and choose Properties. Look for the Spreadsheet Cell Notation check box about half-way down the page.
  • When SCN is turned off, you see this icon Button SCN OFF.png in the upper-left corner of the workbook.
  • If you open a project or workbook window in Origin and SCN is turned OFF in a particular workbook, the SCN OFF button Button SCN OFF.png will display in the upper-left corner. This includes projects or workbooks that were created prior to Origin 2017. To enable SCN, right-click on the book title bar, choose Properties and check the Spreadsheet Cell Notation check box.

Worksheets

Prior to Origin 2018, an Origin workbook could contain a maximum of 255 worksheets. That number is now increased to 1024. When you have more than 255 sheets in a book, you will need to save your file to one of the Unicode-compliant formats (opju, oggu, otwu).

To add worksheets to the workbook, right-click on a worksheet's tab and choose one of the following:

  • Insert. Inserts a single worksheet ahead of the active sheet.
  • Add. Appends a single worksheet.
  • Duplicate Without Data. Duplicates the active worksheet without duplicating the data.
  • Duplicate. Duplicates the active sheet, including the data.

Each sheet in a workbook can have its own set of customizations. When you Insert or Add a worksheet, the new sheet is based on the ORIGIN.otwu file, specifically the version of ORIGIN.otwu that is saved to your User Files Folder (found in UFF if you have customized this file). To add a sheet that is based on another sheet in the workbook (including number of columns and special formatting), you would use the Duplicate or Duplicate Without Data shortcut command.

When a sheet is created it has only a Short Name. If you add a Long Name to the sheet, the Long Name will show on the sheet tab, by default (Note that the rules for sheet Short Name are not very restrictive - see Naming Workbooks, Worksheets and Columns, above).

To add a Long Name to the sheet tab:

  1. Double-click on the sheet tab and enter a Long Name. Alternately, you right-click on the sheet tab, choose Name and Comments and enter a Long Name in the dialog box.


Starting with Origin 2020, there is a system variable @SSL that is used to control sheet name to display. To learn more, look for @SSL in the LabTalk System Variable List.

When mousing over the worksheet tab, the worksheet Short Name, Long Name and Comments appear as a tooltip.

There are a couple of new worksheet data selection behaviors for Origin 2020:

  • Keyboard: CTRL + SHIFT + END extends selection to the last filled cell in the sheet; CTRL + SHIFT + HOME extends selection to the cell in row 1, column 1; SHIFT + arrow key extends the selection to additional columns or rows
  • Graph: Selecting a plot in the graph now selects corresponding data in the worksheet. This feature can be toggled on/off with View: Show Plot Selection on Worksheet. Default behavior is controlled by LabTalk system variable @PS.

Worksheet Properties

To open the Worksheet Properties dialog

  1. Right-click in the gray area to the right of the worksheet grid (but inside the workbook window) and choose Properties.


You can use the Worksheet Properties dialog box to customize properties of the sheet, including...

  • Display of row labels, header labels and grid lines (View tab).
  • The number or rows and columns and other sheet dimensions such as column or row header height (Size tab).
  • Enabling of rich text, text wrap, how to display truncated cell content, sheet font and color (Format tab).
  • Auto add rows, ignore hidden rows in plotting and analysis, cell resizing (Miscellaneous tab).
  • Printing/exporting of grid lines, headers and footers, background color (Print/Export tab).
  • Script to run after import or upon data change (Script tab).
  • Display and edit a user tree (e.g. the user adds some configuration info for use in the template) (User Tree tab).


Note that many of the sheet customizations can be applied at the cell level by right-clicking on a selected cell.

For more information, see The Worksheet Properties dialog box.

Worksheet Columns

  • To add a new column to the right end of an existing worksheet, right-click in the gray area to the right of the worksheet columns and choose Add New Column or Click the Add New Column button Button Add New Columns.png on the Standard toolbar.
  • To add multiple columns to the worksheet, make the worksheet active then choose Column: Add New Columns... from the main menu. Specify the number of columns to add in the Add New Columns dialog box and click OK; or use the Format: Worksheet... menu item or the F4 hot key to open the Worksheet Properties dialog, then set the desired value for Column Number in the Size tab.
  • To insert a column into the worksheet, highlight a column, then right-click and choose Insert. A column is inserted ahead of the selected column and column Short Names are adjusted accordingly.


Worksheet columns can be renamed by:

  • Double-clicking on the column heading opens the Column Properties dialog box. Enter/edit Short Name and/or Long Name.
  • Type a Long Name directly into the worksheet header cell by double-clicking in the cell.
  • Import a data file and specify that the workbooks, worksheets, and columns be named upon import.
  • Use the Enumerate Labels tab of the Worksheet Properties dialog to enumerate or duplicate column names and labels.
  • Type names into a few columns (e.g. Peak 1 and Peak 2), then highlight the cells and drag the bottom-right corner of the selection to auto fill and enumerate the names for other columns. This also works for other column label rows such as Comments.


See the above table for rules on worksheet column naming.

The Column Properties Dialog Box

The Column Properties dialog box is used to customize properties of the column including...


To open the '''Column Properties''' dialog box:

  1. Double-click on the column header.
  2. Right-click the selected column(s) and choose Properties....

Use the Properties tab to edit the column Short Name, if desired. Other properties -- Long Name, Units and Comments -- can be edited here or entered directly into the column label row cells.

Formatting Worksheet Data

Data in the Origin worksheet is treated as either text or numeric data. While the display of text data in the worksheet is fairly straightforward, the display of numeric data is more complicated.

Unless otherwise specified, all numbers in the worksheet are stored internally as floating point, double precision (Double(8)) numbers. This includes date and time, data which is formatted to display in degrees-minutes-seconds or numbers that are formatted to display a fixed number of decimal digits.

When dealing with numeric data, understand that what you see in the worksheet is a representation of a number that is stored internally. This is important for two reasons:

  • Calculations involving worksheet values are always done on the double-precision number that is stored internally, not the value that is displayed in the worksheet.
  • You can apply various Format and Display options to change the way that this stored number displays in the worksheet.

Origin supports custom formatting of worksheet data, to include date-time, percentages, degrees-minutes-seconds and hexadecimal numbers. For more information, see the table under Other Custom Display Formats, below.


Numeric Display Formats
  1. Double-click on a column heading to open the Column Properties dialog.
  2. Click the Properties tab, then set Format = Numeric.
  3. Set Display to Decimal: 1000, Scientific: 1E3, Engineering: 1K, Decimal: 1,000 or Custom (see below).


Date and Time Formats

By default, Origin uses a modified Julian Day value for date-time calculations. Frequently, however, you wish to display your data in a standard date-time format in the worksheet:

  1. Double-click on a column heading to open the Column Properties dialog.
  2. Click the Properties tab, then set Format = Time, Date, Month or Day of Week.
  3. Set the Display list to one of the listed options.
  4. If none of the listed options are appropriate you can choose Custom Display and construct your own custom date-time string using these date-time format specifiers.

When you import date-time data into the worksheet, by default, Origin will treat this data as text. If your date-time data are left-aligned in the worksheet cell, Origin "sees" it as text. You will need to open the Column Properties dialog box and chose the proper Format and Display options. When you see that your date-time data are right-aligned in the cell, you know that Origin "sees" the data as numeric, displaying in a date-time format.

Workbooks formatting data1.png


Other Custom Display Formats

Origin can display numeric values in the worksheet in a variety of custom formats. This illustration shows various formats applied to the same set of numeric values (column A(X)).

ColProperties Custom Formats.png

The following is a sample listing of some supported custom format options (this just happens to be the pre-populated list that ships with Origin 2019). Note that you can enter custom formats directly into the Custom Display list and they will be saved to this list.

There are many other format options. For more information, see Custom Numeric Formats.

Format Description Example
if cell value = 123.456
*n Display n significant digits. *3 displays 123
.n Display n decimal places. .4 displays 123.4560
S.n Display n decimal places, in scientific notation of the form 1E3. S.4 displays 1.23456E+02
E.n Display n decimal places, in engineering format. E.2 displays 123.46
* "pi" Display a number as a decimal, followed by the symbol π. * "pi" displays 39.29727π
#/4 "pi" Display a number as a fraction of π, with a denominator of "4". #/4 "pi" displays 157π/4
#/# "pi" Display a number as a fraction of π. #/# "pi" displays 275π/7
##+## Display a number as two digits, a "+" separator, then two digits (e.g. surveying stations). ##+## displays 01+23
#+##M Display a number as one digit, a "+" separator, then two digits, plus a suffix of "M". #+##M displays 1+23M
#n Display a number as an integer of n digits, pad with leading zeros as needed. #5 displays 00123
#% Display a number as a percentage. #% displays 12346%
# ##/## Display a number as proper fraction. # ##/## displays 123 26/57
# #/n Display a number as proper fraction, in nths. # #/8 displays 123 4/8
DMS Display a number in Degree° Minute' Second", where 1 degree = 60 minutes, and 1 minute = 60 seconds. DMS displays 123°27'22"
D MDn EW (longitude)
D MDn NS (latitude)
Display a number in Degrees and Decimal Minutes. Parameter n specifies decimal places. Positive values will have "E" or "N" appended, Negative values will have "W" or "S" appended. If you wish to preserve negative values do not append "EW" or "NS". D MD3 EW displays 123° 27.360 E
D MDn EWB (longitude)
D MDn NSB (latitude)
Display a number in Degrees and Decimal Minutes. Parameter n specifies decimal places. Letter "B" ("before") specifies that positive values should have "E" or "N" prefixed, negative values will have "W" or "S" prefixed. If you wish to preserve negative values do not append "EWB" or "NSB". D MD3 EWB displays E 123° 27.360
 %#x Display a number as a 32-bit hexadecimal (max 8 hexdigits). The "#" symbol specifies "Ox" prefix. %#x displays 0x7b
 %#0nx Display a number as a 32-bit hexadecimal (max 8 hexdigits) notation, as an n-character string, pad with leading 0 as needed. %#06x returns 0x007b
 %#0nI64X Display a number as a 64-bit hexadecimal notation (max 13 hexdigits, 15 total including #="0x"), as an n-character string, pad with leading 0 as needed. %#014I64X returns 0X00000000007B

Column Label Rows

Column label rows store metadata -- data that is used to describe other data. Typically, this metadata may be brought in as header information in imported files, or it may be manually entered. Display of column label rows is optional and the user can selectively show them or hide them, as needed.

Column label row information is often used in plotting operations (e.g. worksheet Long Names used as graph legend text or Axis titles). The F(x)= row is used in performing math operations on columns of data (see below). Data stored in User-defined Parameter rows might be used in labeling or grouping of datasets in plotting, data manipulation, statistical analysis or math operations (see Tutorial 2, below).

align="center"

Beginning with Origin 2020, you can copy a selected subrange of worksheet cells and include associated column label row information with the copy-paste operation. To copy label rows with data cells, right click on your subrange selection and choose Copy (including label rows).

Managing Display of Column Label Rows

Display (showing or hiding) of column label rows is controlled by shortcut menu commands:

Workbooks 03B.png
  1. Right-click here and choose View from the shortcut menu.
  2. Right-click here and choose Edit Column Label Rows or other label row command.
  3. Right-click here to control worksheet elements (display Row Label, Column Header, etc) or select a cell in this area, then right-click to Set Comments Style.

Column List View

Origin 2019 introduced a new view mode for the worksheet called Column List View that is a transposed view of the column label row metadata. This view is potentially useful if your worksheets have many rows of metadata and you want to focus on some particular aspect of that metadata. With the worksheet active, choose View: Column List View or press CTRL + W.

Workbook Column List View 1.png

Further, you can apply a data filter to metadata in Column List View. When you return to the standard worksheet view (clear the mark beside View: Column List View), only data associated with the filtered metadata will show in the worksheet.

For Origin 2020, Column List View displays column index number ahead of column short name (+ column designation). In addition, you can hover on the left edge of column long name and a tooltip reports dataset size. To disable the display of column index, set @DSI=1.

UG CLV index.png

Sparklines

Numeric data stored in a column will graphically display in the column header in a special label row called Sparklines. A sparkline is, by default, a small inset line plot of the data in a column, plotted as the dependent variable (Y) against the row number or the associated X column as independent variable (X). When importing data, Origin displays sparklines by default when the number of columns is less than 50.

Sparklines column label row.png


To Show or Hide Sparklines:

  • Show Sparklines by clicking Column: Add or Update Sparklines. This opens the sparklines dialog.
  • Show Sparklines for selected columns by clicking the Add Sparklines button Button Add Sparkline.png on the Column toolbar.
  • Right-click on the worksheet's Sparklines column label row and choose Add or Update Sparklines.
  • Delete sparklines by right-clicking the Sparklines column label row and pressing the Delete key.
  • In addition to the default line plot, Sparklines can display as Histogram or Box Charts. Highlight one or more columns by clicking on the column header, then choose Column: Add or Update Sparklines. In the dialog box that opens, set the Plot Type to Histogram or Box.
Sparklines histogram box.png
  • Sparkline plot properties can be customized. Double-clicking on a sparkline pops open a graph window. Double-clicking on the pop-up window opens the Plot Details dialog box where you can customize the plot. When you close the pop-up window, your customizations are applied to sparkline.

The Workbook Organizer

As mentioned, the workbook commonly stores metadata, some of which is visible in the column label rows. Other metadata may be hidden in the workbook. Such hidden metadata might include things like import file path and name, date and time of data import, file header information not written to the column label rows, variable names and values, etc. This hidden metadata can be viewed in the Workbook Organizer panel.

To show a workbook's Organizer panel right-click on the workbook title bar and select Show Organizer (note that this action toggles the panel on or off). The Organizer provides a tree-view listing of metadata stored with a particular workbook.

Workbook organizer.png

Some Workbook, Worksheet and Column Limits

Maximum Number of... 32Bit OS 64Bit OS

Worksheets in a workbook
Rows in a worksheet, 1 column
Rows in a worksheet, 5 columns
Rows in a worksheet, 32 columns
Columns in a worksheet, 1 row
Columns in a worksheet, 100 rows
Columns in a worksheet, 1000 rows

1024†
90,000,000
14,000,000
3,700,000
65,500
65,500
65,500

1024†
90,000,000
90,000,000
90,000,000
65,500
65,500
65,500

† > 255 sheets requires saving file to Unicode-compliant (e.g. *.opju) file format. Unicode formats not compatible with Origin versions prior to Origin 2018.

Workbook Templates

When you start an Origin session or open a new project, You are presented with the New Workbook dialog box. The dialog has three tabs -- System Templates, User Templates and Construct Columns:

  • The System Templates tab lists built-in templates supplied by OriginLab.
  • The User Templates tab lists your custom templates, with filters for Analysis Templates (*.OGWU + saved operations), workbook Templates (*.OTWU) and Workbook Files (*.OGWU).
  • The Construct Columns tab has controls for constructing a workbook from pre-designated columns, by specifying a sequence of letters and numbers (in-dialog examples are given).
Workbooks New Workbook.png
  • The default template is a simple, one sheet, two-column workbook created from the default ORIGIN.otwu workbook template, and located on the System Templates tab.
  • Whether you keep this as your default template, or designate another default template on either the System or User Templates tabs, the default will be the template that opens when you click the New Workbook button Button New Workbook.png on the Standard toolbar.
  • When the New Workbook dialog is open, you can double-click on any template in the System or User Templates tabs, to open a window that is built from that template.
  • To designate a template on either the System or User Templates tabs as the default template, (1) click once on the template to select it, (2) check the Set as Default Template box and (3) click OK to close the dialog box.
Open Default Workbook.png


For more information on use of the New Workbook dialog box, see the New Workbook dialog box.

You can modify any file in the New Workbook dialog -- System Templates or User Templates -- and save your changes. When you modify System Templates and save, the modified file is written to your User Files Folder (you cannot overwrite the original system file) and the modified file then displays on the User Templates tab.


Saving a Modified Template

There are several types of files listed on the System Templates tab of the New Workbook dialog box:

Icon Description of File
NewWork Template.png Template
  • These are of the basic Origin workbook template file type. They contain no data and no analysis operations.
  • To save a modified Template, choose File: Save Template As. Specify a name, file path and optionally, a description and/or a preview image.
NewWork Workbook.png Workbook
  • This type of file differs from a Template in that it can be saved with data and operations.
  • To save a modified Workbook, choose File: Save Window As. Specify a file path, name and optionally, a comment and and/or a preview image.
NewWork AnalysisTemplate.png Analysis Template
  • This type of file saves no data but it MUST store analysis operations. If you open an Analysis Template, you will see little green lock icons Green lock.png on output data columns or report sheets.
  • To save a modified Analysis Template choose File: Save Workbook as Analysis Template. Specify a file path, name and optionally, a comment and/or preview image.

A comment or description produces a tooltip when you hover on a file in the New Workbook dialog box.
A preview image allows you to see an image of your saved file in the New Workbook dialog box.

When creating preview images of your customized templates, it works best to save your image (BMP, JPG, PNG) with dimensions of about 160 pixels wide by 130 pixels high. Use your image capture program to resize your image before saving and adding to your file as a preview image.


For more information see "What is Saved with the Workbook Template?"

Data are not saved with template files. There are other options for saving files without data. One is to "clone" the project; the other -- the default for any workbook that contains Data Connectors -- is to save the Origin file without imported data. For workbooks that do not contain Data Connectors, you can exclude imported data when saving, by opening the (workbook) Window Properties dialog (Format: Workbook) and checking the Exclude imported data when saving project box. Imported data and graphs of that data will be discarded when saving; but data that you have entered via the keyboard, plus any output resulting from analysis of imported data, are saved.

Simple Utilities for Filling Columns with Data

Origin provides several utilities for filling a worksheet range or column, with data. The simplest of these use a menu command to fill a worksheet column with either row index numbers, uniform random numbers or normal random numbers. This is useful for generating quick datasets to test and try out other Origin features.

These simple procedures create a dataset in a pre-selected worksheet range or column(s):

Action Toolbar Button Menu Command
Fill a range or column with row numbers Button Set Col Values Index.png
  • Column:Fill Column With:Row Numbers

or

  • Right-click and select Fill Range/Column(s) With Row Numbers
Fill a column with uniformly distributed random numbers between 0 and 1 Button Set Col Values Uniform Random.png
  • Column:Fill Column With:Uniform Random Numbers

or

  • Right-click and select Fill Range/Column(s) With Uniform Random Numbers
Fill a column with normally distributed random numbers Button Set Col Values Normal Random.png
  • Column:Fill Column With:Normal Random Numbers

or

  • Right-click and select Fill Range/Column(s) With Normal Random Numbers
Fill a column with a patterned or random set of numbers --
  • Right-click and select Fill Range/Column(s) With A set of Numbers...
Fill a column with a patterned or random set of Date/Time Values --
  • Right-click and select Fill Range/Column(s) With A set of Date/Time Values...
Fill a column with arbitrary set of Text&Numeric values --
  • Right-click and select Fill Range/Column(s) With Arbitrary set of Text&Numeric values...


The auto fill feature can be used in filling column label rows and the worksheet data cells:

To use auto fill to extend a pattern in the data across a range of cells (numeric data only):

  1. Select a contiguous block of cells and move the mouse cursor to the bottom right corner of the selection.
  2. When the cursor becomes a "+", hold down the ALT key and drag the mouse to the bottom or the right.


To use auto fill to repeat a pattern in the data across a range of cells (text or numeric data):

  1. Select a contiguous block of cells and move the cursor to the bottom right corner of the selection.
  2. When the cursor become a "+", hold down the CTRL key and drag the mouse toward the bottom or to the right.


Datasets can also be generated quickly using LabTalk script. As an example:

  1. With a new worksheet active, open the Script Window from the Windows menu, and copy-paste the following lines of script code into that window:
  2. col(1)={0:0.01:4*pi};
    col(2)=sin(col(1));
  3. Highlight the two lines and press ENTER to execute them. The first two columns of the worksheet will be filled with data.

Setting Column Values

The Set Values dialog box is used to set up a mathematical expression that creates or transforms one or more columns of worksheet data. The dialog box includes a menu bar, a control used to define output range, a tool for searching and inserting LabTalk functions into your expression, a column formula box used to define a one-line mathematical expression and a Before Formula Scripts panel (usage optional) intended for data pre-processing and defining of variables used in your one-line expression.

SetValues.png

Since Origin 2017, the column formula box (the upper box) in Set Values has supported a simplified spreadsheet cell notation like is used in MS Excel and Google Sheets. A cell is addressed using column Short Name + row index number (e.g. the first cell in column A -- formerly represented as "col(A)[1]" -- is now simply "A1").

In new workbooks, spreadsheet cell notation is enabled by default. Spreadsheet cell notation can only be used in defining the column formula. It cannot be used in the Before Formula Scripts panel of Set Values, nor can it be used in your LabTalk scripts. Note that the "old" column and cell notation will work in spreadsheet mode, so if you are an experienced user and you prefer to use the old notation, you may enter it as you always have. For an introduction to the spreadsheet cell notation syntax as well as a contrast with the "old" methods, see Column Formula Examples.

To open the Set Values dialog box for a single column:

  1. Select a worksheet column or a range of cells in a worksheet column.
  2. From the menu, choose Column: Set Column Values... or right-click on the worksheet column and choose Set Column Values... from the shortcut menu.


To open the Set Values dialog box for multiple columns:

  1. Select multiple, contiguous worksheet columns (skip no columns) or the entire worksheet.
  2. From the menu, choose Column: Set Multiple Column Values... or right-click on the worksheet column and choose Set Multiple Column Values... from the shortcut menu.
SetValues Multiple.png

Set Values Menu Commands

Menu Commands
  • Formula: Load a saved formula into the column formula box. Formulas are saved using Formula: Save or Formula: Save As....
  • wcol(1): Use the menu to include worksheet columns in either your column formula or your Before Formula Scripts (column reference is inserted at the cursor). A Column Browser is available to help in selecting the correct columns. Columns are listed by column index.
  • Col(A): Similar to wcol(1) menu functionality but columns are listed by column name (including Long Name, if it exists).
  • Function: Add LabTalk functions to your expressions (function name is inserted at the cursor). Note that when you hover over a function in the menu list, the function description will be shown in the Status Bar. When a function is selected, its description will be displayed in a pop-up Smart Hint.
  • Variables: Add a variable or a constant to Column Formula or Before Formula Scripts; Add range variables (including by selection) or file metadata, to Before Formula Scripts.
  • Options: Allow direct editing of column formula in worksheet Formula row; add a comment about the column formula; or preserve text in Set Values columns (do not treat as text as missing values).
Column Formula
  • Add a single line expression for generating data. Functions, conditional operators and variables can be used.
Before Formula Scripts
  • LabTalk scripts to be executed before the expression in the column formula box is executed.

When you mouse over one of the functions listed in the Function menu in Set Values dialog, a one-line tooltip is displayed in the Status Bar. If you select the function, a Smart Hint appears with a more detailed explanation and a link to the full function description, syntax, examples, etc.

Additionally, you can click the Search and Insert Functions button Search Insert Functions.png to search for available functions by keyword and, once found, insert the function into your expression.

Use the Before Formula Scripts panel to define variables, LabTalk functions, or run LabTalk scripts before the expression in the upper panel is evaluated.


To learn more, see Set Column Values - Quick Start

The "F(x)=" Worksheet Column Label Row

For simple expressions, you can use the F(x)= row to set column values. Any expression you enter here is directly entered into the Set Values dialog and vice versa. Note that the simplified spreadsheet cell notation that works in the formula box in Set Values also works in F(x)=:

  1. Double-click in a cell in the F(x)= column label row.
  2. Enter an expression to enter output in the data column below.
Fx column label row.png

Set Column Values Tutorials

Tutorial 1: A Quick Units Conversion using F(x)=

  1. Start with a new workbook and import the file \Samples\Graphing\WIND.DAT.
  2. We will assume that column B contains Speed values in miles per hour (MPH). Click on the column heading for column C, then right-click and choose Insert. Origin inserts a new column C and moves Power values to column D.
  3. Now, we'll convert the MPH values in column B to kilometers per hour (KPH). Double-click in the F(x)= cell of column C and enter
    B*1.6
    and press Enter. Column C is filled with values in units of KPH.


Tutorial 2 : Computing Moving Average and Moving Standard Deviation

  1. Import the file Samples\Signal Processing\fftfilter1.DAT.
  2. Add two more columns to the worksheet by clicking the Button Add New Columns.png twice.
  3. Click on the header of the 3rd column to select it, then right-click and select Set Column Values... from the context menu.
  4. In the Set Values dialog, enter the following in the upper panel:
    movavg(B,5,5)
    and press Apply. Column 3 is filled with an 11-point moving average of the data from column B (note that you can insert functions such as movavg from the Function menu of the Set Values dialog box).
  5. Click the >> button above the edit box to switch to the 4th column.
  6. In the edit box for the 4th column, enter the formula:
    movrms(B,5,5)
    and press OK. This 4th column will be filled with root-mean-square (RMS) values, using a window size of 11 at each point.


Tutorial 3: Set Values for Multiple Columns

  1. Create a new project by clicking the New Project button Button New Project.png on the Standard toolbar.
  2. Click the Import Multiple ASCII button Button Import Multiple ASCII.png to import the files F1.dat and F2.dat in the <Origin Folder>\Samples\Import and Export\ path. In the impASC dialog, set Multi_File (except 1st) Import Mode to Start New Books and click OK.
  3. Two workbooks are created, named as F1 and F2. Click the New Workbook button New Workbook.png on the Standard toolbar to create another workbook.
  4. With the 3rd workbook active, click the Add New Columns button Button Add New Columns.png to add a column. Highlight all columns and select Column: Set Multiple Columns Values from the main menu or right-click the columns and select Set Multiple Columns Values from the shortcut menu to open the Set Values dialog box.
  5. Expand the bottom panel by clicking the Show Scripts button Button Show Scripts.png. Enter this script in the Before Formula Scripts edit box,
range r1=[F1]F1!wcol(j); //"j" is the column index.
range r2=[F2]F2!wcol(j);
  1. Enter (r1+r2)/2 in the Column Formula edit box
  2. Select Options: Direct Edit Formula Cell to clear this option.
  3. Select Options: Formula Text... and enter (F1+F2)/2 in the Formula Text dialog, then click OK.
  4. Click the OK button in the Set Values dialog box. You will see the results in the worksheet, and (F1+F2)/2 will display in the F(x)= column label row instead of the formula.

Setting Cell Values

Beginning with version 2018, Origin supports cell-level expressions similar to those used by spreadsheet programs. Cell-level expressions which return a single value (numeric, string or date/time) can be entered into any worksheet data cell or into cells in a User-Defined Parameter row of the column label row area. When Edit Mode (Edit: Edit Mode) is toggled on, cell formulas display. When Edit Mode is toggled off, the formula result is displayed. Cell content can be edited regardless of Edit Mode state.

Set Cell Values 1.png
  • To use cell formulas, Spreadsheet Cell Notation must be enabled (it is by default).
  • Cell formulas begin with an equal sign (e.g. =B1 - C1).
  • Cell formulas can return a numeric, a string or a date-time value.
  • Cell formulas can incorporate cell references, variables, operators, LabTalk-supported functions and constants.
  • Cell formulas can reference values in other sheets or books.
  • Cell formulas can be extended to other cells by dragging with your mouse.


To learn more, see Using a Formula to Set Cell Values.

Origin 2020 adds the Excel "$" cell-reference syntax for more intuitive absolute cell references. This should make constructing cell formulas easier, particularly for those who have used MS Excel. See Tutorial 1, next.

Set Cell Values Tutorials

Tutorial 1: Extending a cell formula to other cells

  1. Click the New Workbook button Button New Workbook.png to open a new book.
  2. Click on the column A heading to select, then right-click and choose Fill Column with Row Numbers.
  3. Click on cell B1 and enter:
    =A1+A$1
  4. Press ENTER. This adds the value in A1 to the value in A1.
  5. With the cell still selected, hover on the selection handle in the lower-right corner of the cell and when it looks like a "+", double-click to extend the cell formula to the bottom of the column.
  6. Click the Add New Columns button Button Add New Columns.png to add column C.
  7. Click on the Cell in C1 but this time enter (omitting the "$"):
    =A1+A1
  8. Press ENTER. This adds the value in A1 to the value in A1.
  9. With the cell still selected,hover on the selection handle in the lower-right corner of the cell and when it looks like a "+", double-click to extend the cell formula to the bottom of the column. Note that this time, the resulting values are different.
  10. Click Edit: Edit Mode to display the underlying cell formulas. Note that the "$" in column B "locked" the second cell A1 reference so that it didn't change but that the A1 reference in column C changed with the row index number as we extended the cell calculation to other cells.
Extending cell values.png

Tutorial 2: Quickly finding maximum values in multiple columns of data using special keyword "This"

The only place where you can use cell formulas in the worksheet column label rows (worksheet header rows), is in a User Parameter row.

  1. Create a new workbook and then choose Data: Import From File: Single ASCII and import the file \Samples\Import and Export\S15-125-03.dat.
  2. With your mouse, hover just to the left of the F(X)= row label and when the pointer becomes an arrow, right-click and Add User Parameters.
  3. In the dialog box that opens, enter "MaxValue" and click OK.
  4. In column A(X), in the MaxValue cell, enter:
    =Max(This)
  5. Click outside the cell and cell should now display "10".
  6. Click back on this cell, then grab the selection handle in the lower right corner of the cell and drag to the right to extend the cell formula to MaxValue cells in columns B(Y), C(Y) and D(Y). All MaxValue cells should now display the maximum values in their respective columns.

Tutorial 3: Use a column label row value in a cell calculation

All data in the worksheet column label rows, including User Parameter rows, is stored as string data. To use a "number" stored in a column label row in a cell calculation, you must convert the string to a numeric value. In the following example, we use the LabTalk value() function to convert column label row data to a numeric so that it can be used in a cell calculation:

  1. Create a new workbook and then choose Data: Import From File: Single ASCII and import the file \Samples\Import and Export\S15-125-03.dat.
  2. With your mouse, hover just to the left of the F(X)= row label and when the pointer becomes an arrow, right-click and Add User Parameters.
  3. In the dialog box that opens, enter "Correction" and click OK.
  4. In column D, enter the value "0.2" into the Correction cell.
  5. Click the Add New Columns button Button Add New Columns.png to add column E.
  6. In cell E1, enter:
     =D1+value(D[Correction]$)
  7. Press ENTER. This converts the Correction value to a numeric and adds the numeric to the value in cell D1. The cell should display 101.9.


NOTE: The "$" in the above expression does not function to create an absolute cell reference as in the first example above. In this context, the "$" syntax is used to express a string variable stored in a user-parameter cell, before converting that string to a numeric value.

Processing Worksheet Data

Origin provides a number of utilities for manipulating worksheet data. Most of these are found on the Worksheet menu while some are on the Edit, Column or Analysis menus (note that a worksheet must be active). Some utilities are available from a shortcut menu: select your data and right-click.

Utility Menu Access
  • Sorting Data

Worksheet: Sort Range
Worksheet: Sort Columns
Worksheet: Sort Worksheet
Worksheet: Sort Columns by Label

  • Find
  • Replace
  • Go To (sheet row/column)

Edit: Find in Project
Edit: Find in Sheets
Edit: Replace
Edit: Go To

  • Hide Column
  • Hide Rows

Column: Hide/Unhide Columns

Hide/Unhide Rows (shortcut menu only)

  • Move Columns
  • Swap Columns

Column: Move Columns or Column toolbar.

Column: Swap Columns

  • Query Worksheet Data

Worksheet: Worksheet Query

  • Copy Columns to (new locations)

Worksheet: Copy Columns to

  • Append Worksheet

Worksheet: Append Worksheet

  • Split Columns
  • Split Worksheet
  • Split Workbook

Worksheet: Split Columns

Worksheet: Split Worksheet

Worksheet: Split Workbooks

  • Worksheet Transpose

Worksheet: Transpose

  • Stack Multiple Columns into One with Grouping
  • Unstack Grouped Data into Multiple Columns

Worksheet: Stack Columns

Worksheet: Unstack Columns

  • Summarize Data with Pivot Table

Worksheet: Pivot Table

  • Filter data by defining conditions on one or more columns

Column: Filter menu, or Worksheet Data toolbar

See Also: Data Masking

Previously, filters needed to be reapplied manually after new data were imported. Filters will now run automatically on import. In addition, applying a filter to large datasets is significantly faster than it was in previous versions.

  • Data Reduction

Worksheet: Remove/Combine Duplicated Rows
Worksheet: Reduce Columns
Worksheet: Reduce Rows
Analysis: Data Manipulation: Reduce Duplicate X Data
Analysis: Data Manipulation: Reduce by Group
Analysis: Data Manipulation: Reduce to Evenly Spaced X

  • Apply Conditional Formatting to Worksheet Cells

Worksheet: Conditional Formatting: Highlight
Worksheet: Conditional Formatting: Heatmap
Worksheet: Conditional Formatting: Duplicates

  • Reverse Order

Column: Reverse Order

In addition to the above worksheet data utilities, the Origin worksheet supports Conditional Formatting. Conditional Formatting has three modes:

  • Highlight mode opens a dialog box with controls to apply color to worksheet cells based on one or more conditions (e.g. "equal to", "not equal to", "text that contains", etc).
  • Duplicates mode opens a dialog box with controls to apply to worksheet cells that contain duplicate values.
  • Heat Map mode opens a dialog box with controls to apply a color map to cells based on worksheet values. The worksheet Heat Map is zoomable and scrollable, making it easy to get a "big picture" overview of data variation in three dimensions.
US Temperature Data wMap Overlay.png

Workbooks for Analysis and Reporting

Apart from text and numeric data, the workbook can contain various other types of information -- graphs, notes and matrices; links to cell values in other books, project variables, documents or web pages; plus, import file metadata, variables and scripts -- making the workbook a flexible medium for collecting research data or for creating custom reports.

Another option for generating reports is to create HTML reports using Origin's Notes window. A Notes window can link to graphs, worksheet cells, etc., either directly or using a placeholder sheet. For more information, see HTML Reports from Notes Windows.

Further, as we will see, the workbook can "store" a complex sequence of analysis operations -- say, the application of a data filter and a fitting operation on the filtered data, combined with a customized plot of the results, into something that we call an Analysis Template. The Analysis Template makes it possible to automatically generate a custom report of results, simply by supplying new input data.


Custom worksheet report.png

One attractive option for generating reports -- there are others, see the "New" button at the bottom of this section -- is to export data to a custom MS Word template, and optionally, a PDF file. This is done by running an output-generating analysis in Origin, then associating key results with bookmarks in a Word template, and, finally, saving the workbook as an Analysis Template. To generate your report, you open the Batch Processing tool, point to both your Analysis Template and your Word template, run the batch process and generate your reports.

Word report template PDFout.png

Topics for Further Reading