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.


Workbooks

By default, when you start an Origin session, Origin loads an empty project with a single, one sheet, two-column workbook created from the default ORIGIN.otwu workbook template. You can add workbooks to your Origin project file 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 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 the book (applies to all sheets). Spreadsheet cell notation is useful for defining column formulas and must be enabled to make use of cell-level formulas. This is explained in more detail, below.

The Window 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 Find in Project tool.

Worksheets

Prior to Origin 2018, an Origin workbook could 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 using one of the new Unicode 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, 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.

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 Properties

The Worksheet Properties dialog box is used 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).


Note that many of the sheet customizations can be applied at the cell level.

For more information, see The Worksheet Properties dialog box.

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.

Column Label Rows

Column label rows store metadata -- data that is used to describe other data. Typically, though not necessarily, that data will be contained in the data columns immediately below the column label rows. 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.

Column label row information is often used in plotting operations (e.g. worksheet Long Names used as graph legend text). The F(x)= row is typically used in performing math operations on columns of data. 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.

align="center"

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.


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.

Sparklines column label row.png

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.

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.

The Workbook Organizer

As mentioned, the workbook commonly stores metadata, some of which is visible in the column label rows and some of which is hidden and not necessarily visible. 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
25,000,000
4,860,000
65,500
65,500
65,500

1024†
90,000,000
90,000,000
7,300,000
65,500
65,500
65,500

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

Workbook Templates

Origin workbook windows are created from a workbook template file with an .otwu extension. By default, Origin uses the ORIGIN.otwu template when creating new workbook windows. ORIGIN.otwu 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.

  1. To save the active workbook as a template, choose File: Save Template As. This opens the template_saveas dialog box.
  2. Use Category = UserDefined or create your own.
  3. Give the template a name. By default, the name is the name of the template used to create the window. If the template is a System template (e.g. the Origin.otwu file used to create new workbooks), saving the file to your UFF will make this your new default template.
  4. Provide a Template Description if desired and save either to File Path = User Files, or browse to a new location.

The System templates that ship with Origin are stored in the EXE folder and cannot be overwritten. If you have saved a customized template to your User Files Folder but want to restore the original System template, simply remove your customized template from the UFF.

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

As mentioned, Origin ships with a number of System Templates (workbook, matrix, graph) that are installed to the Origin EXE folder and cannot be overwritten. When you customize a window that is created from a System Template and save the template with the same name to your User Files Folder, this customized template then becomes the new default template associated with a particular toolbar button or menu command. This applies to the workbook template that is opened when you click the New Workbook button Button New Workbook.png on the Standard toolbar.

Other methods of creating a window from a customized workbook file:

  • 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 (*.otpu, *.otwu, *.otmu) 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 (*.otwu), 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 .otwu file, then select the worksheet template from the Name drop-down list. Optionally, specify this as the default workbook template by clicking Set Default.

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 (e.g. "col(A)" is now simply "A" and "col(A)[1]" is now 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.


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

Setting Cell Values

Origin 2018 now supports cell-level expressions similar to those used by spreadsheet programs.

Cell-level expressions which return a single value 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 resulting value 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 (Spreadsheet cell notation mode.png appears in upper-left corner of sheet).
  • 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.

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 drag to the end of the column, extending your cell calculation to other cells. This will add the quantity in A1 to values in column A.
  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:
    =A1+A1 // omit the square bracket
  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 drag to the end of the column, extending your cell calculation to other cells. Note that this time, the resulting values are different.
  10. Click Edit: Edit Mode to display the underlying cell formulas. Note that the square brackets in column B "protected" the second cell A1 reference so that it didn't change but that the A1 reference in column C changed as we extended the cell calculation to other cells.
Extending cell values.png

Tutorial 2: Quickly finding maximum values in multiple columns of data

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 File: Import: 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 File: Import: 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.

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

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

Topics for Further Reading

 

© OriginLab Corporation. All rights reserved.