OriginLab Corporation - Data Analysis and Graphing Software - 2D graphs, 3D graphs, Contour Plots, Statistical Charts, Data Exploration, Statistics, Curve Fitting, Signal Processing, and Peak Analysis     
 
Skip Navigation Links
All BooksExpand All Books
User GuideExpand User Guide

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 Names 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 should be limited to 359 characters (including spaces).
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 32 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.
Columns
  • A Column has a Short Name and an optional Long Name.
  • The Short Name must be unique within the worksheet, cannot contain special characters, must begin with a letter or number, use only alphanumeric and limited to 18 characters.
  • A Column Long Name is optional, need not be unique within the project file, can use any characters in any order.
  • If the Auto update column Short Name to follow Long Name check box is selected (Tools: Options: Miscellaneous),column Short Name tracks column Long Name, removing spaces/special characters, truncating to 17 characters, as needed. Check box is cleared by default.
  • 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 (Tools: Options: Miscellaneous). Otherwise, Short Names are used.
Note: Origin has a formal notation for addressing columns which makes use of the Workbook Short Name, the Column Short Name and the Worksheet index if larger than 1:

WorkbookShortName_ColumnShortName@SheetIndex

as in

Book1_C@200.

This notation is limited to 24 characters and Origin adopts various strategies in limiting Short Names ( Book or Column ) to allow user flexibility and still allow operation flexibility.

For example, this explains why you can create longer column short names in books with shorter book short names.

Workbooks

When you start an Origin session, Origin loads an empty Project and adds the default ORIGIN.OTW workbook template (You can modify this behavior by clicking Tools : Options > Open/Close tab > Start New Project). Add workbooks to your Origin project file at any time by clicking the New Workbook button Button New Workbook.png on the Standard toolbar.

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 worksheet Window Properties dialog has a Spreadsheet Cell Notation check box that is enabled by default. When enabled, the Spreadsheet cell notation mode.png icon displays in the upper-left corner of each sheet in the book. This spreadsheet cell notation is useful for defining the column formulas used in math operations on datasets. For more information, see Setting Column Values, below.

Note: The workbook has a Comments property that you can access by right-clicking on the workbook title bar and choosing Properties.... These Comments display as a workbook window tooltip in Project Explorer and become searchable when using the Find in Project tool.

Worksheets

An Origin workbook can have up to 255 worksheets. 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.OTW file, specifically the version of ORIGIN.OTW that is saved to your User Files Folder if you have customized this file. If you wish 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.

To control worksheet tab name display:

  1. Right-click on the worksheet tab.
  2. Select Sheet Name to Show: Long Name if available/ Short Name/ Long Name from the shortcut menu.


To change the Short Name or Long Name that displays on the worksheet tab:

  1. Double-click on the worksheet name or right-click on the worksheet tab and choose Name and Comments. See the above table for rules on worksheet naming. If you are editing the Short Name and you exceed 32 characters, Origin automatically truncates your entry to create the Short Name. If Long Name did not previously exist, the unmodified text becomes the Long Name.


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

Worksheet Columns

  • To add a new column to 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.


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...

  • Long Name, Short Name, Units, Comments, etc.
  • Format (Numeric, Text, Time, Date, etc.).
  • Plot Designation.


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 appropriate cell.

Note that the worksheet column can be configured for different data formats, including Text & Numeric, Numeric, Text, Time Date, Month, and Day of Week. A column can contain data of only one format type. It is important to know that the default Format is Text & Numeric and unless you are using a custom workbook template, you may need to manually set Format so that column data display and are handled properly. For instance, many users get tripped up when they enter date or time data into a worksheet column but fail to set the necessary Format and Display options. Column Properties is where you do that.

We should also point out the worksheet column Plot Designation. Columns are designated as X, Y, Z, Label, Disregard, Y Error or X Error. A column's Plot Designation determines how the selected data will be handled, by default, during plotting and analysis operations. As Origin has evolved, the Plot Designation has become somewhat less important than it once was. For instance, the Plot Setup dialog and many Analysis dialogs allow you to select data interactively, thus overriding the Plot Designation. Still, Plot Designation is an important concept and you should be aware of its effects on plotting and analysis operations. To learn more, see Plot Designation (Column Properties).

You can customize the display of individual cells or groups of cells in a column, editing such things as font, cell fill color, or number of displayed decimal digits:

Column Label Rows

align="center"

All columns have fixed properties (i.e. metadata) that display in the column label rows. These include the Long Name, Units, and Comments. The values of these properties are used to address and represent data columns both internally and in the Origin GUI.

Display 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 for global control of row labels and row and column header display.


Column label rows store information which can be used when plotting, such as for group plotting and for setting legend text. Label row data can also be incorporated into data analysis operations (for instance, in math operations on worksheet columns), or in data manipulation operations (for instance providing grouping information when stacking worksheet columns).

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). Sparklines can also be plotted as a histogram or as a box chart, providing a "thumbnail" view of column statistics. When importing data, Origin displays sparklines by default when the number of columns is less than 50.

Turn on sparklines for selected columns by clicking Column: Add or Update Sparklines or for all columns by right-clicking on the Sparklines row label and choosing Add or Update Sparklines. Sparklines can be deleted by clicking the Sparklines row label and pressing the delete key. Note that sparklines can be edited graphically. Double-clicking on a sparkline pops open a graph window where you can customize the plot. When you close the pop-up window, your customizations are applied to sparkline.

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

255
90,000,000
25,000,000
4,860,000
65,500
65,500
65,500

255
90,000,000
90,000,000
7,300,000
65,500
65,500
65,500

Workbook Templates

Origin workbook windows are created from a workbook template file with OTW extension. By default, Origin uses the ORIGIN.OTW template when creating new workbook windows. ORIGIN.OTW contains a single, two-column worksheet. You can customize an Origin workbook and save it as a new template file. Customized templates get saved to your User Files Folder (UFF) by default. Built-in template files can't be overwritten; instead a template file with same name will be saved to your UFF. The UFF version then becomes the template used by Origin's menu commands and toolbar buttons.

  • To save the active workbook as a template, choose File: Save Template As. This opens the template_saveas dialog box.


Note: Data are NOT saved with a template file.


The following table lists some of the kinds of things that are saved with the workbook template file. Click the dialog box link for more information.

Dialog Box What is Saved? Menu Command
Worksheet Properties
  • Display of row/column heading labels.
  • Display of worksheet grid lines.
  • Number of rows/columns in the worksheet.
  • Auto addition of worksheet rows, when needed.
  • Creation and naming of worksheet column heading user-defined parameters.
  • The order of worksheet column heading row labels.
Format: Worksheet...
Column Properties
  • Column Short Name, Long Name, Units, Comments.
  • Formula (if any) used for calculating column values.
  • Column width settings.
  • Column Plot Designation. (X,Y,Z, Label, etc.).
  • Column data Format (text, numeric, date, etc.).
  • Column number Display (decimal, scientific, engineering).
  • Column Digits and data storage requirements.
Format: Column...
Column Formulas and Scripts
(Set Column Values)
  • Formulae used to create or transform column values.
Column: Set Column Values...
Properties
  • Any annotations along with their settings in the Properties dialog box.
  • Any scripts associated with the object's Properties dialog, Programming tab.
Format: Text Object
Format: Object Properties
Worksheet Query
  • Equations in the Worksheet Query dialog box.
Worksheet: Worksheet Query

Worksheet Properties, Script tab
or
Script Panel

  • Scripts entered in the Script tab of the Worksheet Properties dialog box.
  • Scripts in the worksheet Script Panel.
Worksheet: Worksheet Script
Show Script Panel
Query Builder
  • Option to save database queries with the workbook template.
File: Database Import: New

Creating a New Workbook from a Custom Template

There are numerous ways to create a workbook from a customized template:

  • If the template was recently saved, look under File: Recent Books or go to the Books tab in Origin Central (Help: Origin Central or press F11).
  • Click File: Open..., set file type to Origin Template (*.otp, *.otw, *.otm) and browse to the file.
  • Click the Open Template... Button Open Template.png button on the Standard toolbar and change the file type to Workbook Template (*.otw), then browse.
  • Click File: New: From Template: More... This menu command opens the Load Template dialog box. In the Type group, select Workbook. If necessary, use the browse button in the Path group to locate the .otw file, then select the worksheet template from the Name drop-down list. Optionally, specify this as the default workbook template by clicking Set Default.

By default, the New Project and New Workbook buttons will use the Origin.OTW template located in the Origin EXE folder. You can customize a workbook and save it as Origin.OTW. Your customized file will be saved in the User Files Folder, and will be used from then on as the default template. If you wish to roll back to your original Origin.OTW, browse to your User Files Folder in Windows Explorer and rename or delete the ORIGIN.OTW that is saved there.

Worksheets for Analysis and Reporting

Apart from containing text and numeric data, worksheet cells support storage and display of various other types of data such as graphs, graphic objects, notes, hyperlinks to documents and web pages, and links to project variables.

This makes the worksheet an ideal medium for presenting analyses and reporting results. You can link to cells in other worksheets and report sheets. You can also embed graphs, images from external disk files, or image plots corresponding to matrix objects in your Origin Project. Groups of cells in a worksheet can be merged, allowing for larger objects to be displayed fully. Graphs can also be placed on worksheets as floating objects.

Once created, such custom reports can be exported as image files (for instance, as PDF or JPEG), and they can also be used in the creation of an Analysis Template.

Custom worksheet report.png

You have the option of generating custom reports by exporting 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. For more information on batch analysis and generating custom reports, see the Batch Analysis section of the "How to Handle Repetitive Tasks" chapter of this Guide.

Word report template PDFout.png

The Workbook Organizer

The workbook typically stores metadata, including such things as import file path and name, date and time of data import, file header information, variable names and values, etc. Such 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

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 column formula box (the upper box) in Set Values now supports a simplified spreadsheet cell notation like is used in MS Excel and Google Sheets (e.g. "col(A)" becomes simply "A" and "col(A)[1]" becomes simply "A1"). When this spreadsheet cell notation is enabled (default), you will see an Spreadsheet cell notation mode.png icon in the upper-left corner of the worksheet. This 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 prefer to use the old notation, you may enter it as you always have.

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.
SetValues.png


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.


The F(x)= Worksheet Column Label Row

You can also use the F(x)= row to enter an expression for setting 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 noted above, also works in F(x)=:

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

Set 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, select Start New Books for the Import Mode drop-down list.
  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.

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 the active window.

Utility Menu Access
  • Sorting Data

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

  • Find
  • Replace

Edit: Find in Project
Edit: Find in Sheets

Edit: Replace

  • 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

  • Append Worksheet

Worksheet: Append Worksheet

  • Split Worksheet
  • Split Workbook

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 using conditions on one or more columns

Column: Filter menu, or Worksheet Data toolbar

  • 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

  • Reverse Order

Column: Reverse Order


Some of these worksheet data processing utilities are available from a shortcut menu. For access, right-click on your data selection.

Topics for Further Reading

 

© OriginLab Corporation. All rights reserved.