9 Workbooks Worksheets Columns Show
Contents
Workbook, Worksheet and Column BasicsThe Origin workbook is a nameable, moveable, sizeable window that provides a framework for importing, organizing, analyzing, transforming, plotting and presenting your data.
Some Workbook, Worksheet and Column Limits
† > 255 sheets requires saving file to Unicode-compliant (e.g. *.opju) file format. Unicode formats not compatible with Origin versions prior to Origin 2018. Naming Workbooks, Worksheets and Columns
WorkbooksOrigin workbooks are highly customizable and can be saved with data (e.g. Workbook File) or without data (e.g. Workbook Template). Since they can be configured for many different applications, there is a good chance that you will collect a number of custom files over time. The New Book dialog is useful for organizing and choosing these files for use. New Book and Book TemplatesTo open the New Book dialog:
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).
What Types of Data Can I Store in the Workbook?The workbook serves as a flexible container for all of your work-related data -- not just text and numeric data. You can add graphs, matrices, images, notes; and store calculations, scripts and variables, text objects and programmable buttons, and create live links to other project data. Beyond its role as a flexible data container, the workbook can also serve as a medium for batch analysis and reporting.
This table summarizes the kinds of objects that can be saved in the workbook window at the workbook, worksheet and worksheet cell levels, and how to access them.
WorksheetsA workbook can have up to 1,024 sheets. A sheet has a single Name which can contain spaces and special characters. Optionally, you can add a Label and/or a Comment. To edit the sheet Name
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.
You can also (a) drag existing sheets between books or (b) drag and drop sheets onto an empty portion of the workspace, to create a new book.
Worksheet PropertiesTo open the Worksheet Properties dialog
For more information, see The Worksheet Properties dialog box.
Manipulating Sheets with Object ManagerUse the Object Manager's shortcut menu commands to manipulate display of workbook content:
Hide/Show Sheets
Worksheet Views: Split and FreezeOrigin has two utilities for locking the view in part of the worksheet, while allowing you to scroll through the remainder of the sheet. The two could be used interchangeably in some situations. Splitting the worksheet into panels using dividersThis places a moveable, vertical or horizontal divider at the selected row or column; or if a single cell is selected, both a vertical and a horizontal divider. This divides the worksheet into identical and scrollable views of the worksheet data area. The user is able to scroll within each panel while rows or columns in the other panel(s) remain visible.
Freeze rows or columns in the worksheet
The user can freeze the first 1 to 10 rows and/or columns in the worksheet, thus locking them in view while the remainder of the rows or columns remain scrollable. Locked row and column headers are shaded in a darker color to indicate freeze.
Worksheet Columns
Column DesignationsAs mentioned, worksheet Column Designations (aka "Plot Designations") generally determine how data are handled during analysis and plotting operations. For instance, you might select an X column + three Y columns to perform a simultaneous linear fitting of each Y dataset against a common set of X values. Or you might select the same columns to graph 3 line plots against a common set of X values. In addition, there are designations for Z values, for error data, for labels, etc. (for more information, see Column Designation in the Origin Help file).
The Column Properties Dialog BoxThe Column Properties dialog box is used to customize properties of the column including...
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 Column DataData 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:
Numeric Display Formats
Date and Time FormatsBy default, Origin stores date-time data as a modified Julian Day value and it uses this number for date-time calculations. Typically, however, you will prefer to display this Julian Day value in a more meaningful date-time format:
Color FormatOrigin 2021 introduced a new column and cell Format -- Color.
Other Custom Display FormatsOrigin 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)). 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.
Column Label RowsColumn 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).
Managing Display of Column Label RowsDisplay (showing or hiding) of column label rows is controlled by shortcut menu commands:
There is also a worksheet column label row Mini Toolbar for managing label rows. Use it to do such things as hide selected label rows, enable Rich Text or change label row order. Column List ViewOrigin 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. 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.
SparklinesNumeric 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.
The Workbook OrganizerAs 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 (or hide) a workbook's Organizer panel:
Managing Workbooks with Mini ToolbarsA number of common book-, sheet-, column and cell-level properties can be set or toggled ON/OFF with a Mini Toolbar button.
Find and Replace in Worksheets
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):
To use auto fill to extend a pattern in the data across a range of cells (numeric data only):
col(1)={0:0.01:4*pi}; col(2)=sin(col(1)); 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 expressions, a Before Formula Scripts panel (usage optional) intended for data pre-processing and defining of variables used in your one-line expression and for Python users, a Python Function tab for defining and using Python functions which can also be used in your expressions. 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:
Set Values Menu Commands
The "F(x)=" Worksheet Column Label RowFor 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)=:
Set Column Values Tutorials
Setting Cell ValuesOrigin 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.
The Formula BarWhen creating cell formulas, or column formulas using F(x)=, the Formula Bar makes it easier to find and insert functions, select cell ranges and view and edit expressions, particularly long expressions that exceed cell width.
Set Cell Values Tutorials
Naming Data RangesYou can assign a name to a worksheet data range and use the name in cell formulas or column formulas and to define Reference Lines in graphs.
Remove Formula/LinksRemoving formulas and links can make it easier to share project data with colleagues without having to share such things as externally-linked (DDE) Excel files. It is also useful for significantly reducing project size before archiving data.
for more information, see the Origin Help file. Cell NotesAny worksheet cell -- data row or column label row -- can contain a cell note; even those that contain data or other objects such as images or embedded graphs (Note: cells containing Links are not supported). Worksheet cell Notes support Rich Text, meaning you can style text using Origin Rich Text syntax. In addition, you can add images and graphs, and link to worksheet cell values, report table values, etc. See Notes Windows for Reporting.
Notes:
Text Styles ManagerIn addition to styling text with the Format toolbar, you can apply a simple set of styles to each line/paragraph. Manage styles with the Text Styles Manager dialog box.
Note that you can add styles by selecting <new> from the Styles to Modify drop-down list; or select a style and Delete. To apply a paragraph style to Notes window text:
Processing Worksheet DataOrigin 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.
Conditional Formatting of Worksheet DataIn addition to the above worksheet data utilities, the Origin worksheet supports Conditional Formatting. Conditional Formatting has three modes:
Manage conditional formatting in the active sheet using the Conditional Format Manager. Protecting Worksheet Data
You can apply blanket protections to one or more worksheets and, in the process, provide for a few exceptions.
Workbooks for Analysis and ReportingApart from text and numeric data, the workbook can contain various other types of information -- images, 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. Further, as we will see, the workbook can "store" a complex sequence of analysis operations -- for instance, the application of a data filter, plus 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. One attractive option for generating reports (there are others -- see the tip 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.
Topics for Further Reading
What lets you extend any data pattern involving dates times numbers or text in Excel?It is labeled with function symbol (fx). a feature that allows the user to extend (and fill) a series of numbers, dates, or even text to a desired number of cells. In the active cell of the spreadsheet, the fill handle is a small black box at the bottom right corner, as shown in the image to the right.
What is the AutoComplete feature of Excel *?AutoComplete helps you quickly insert functions and arguments while minimizing typing and syntax errors. The AutoComplete menu shows you available options based on context, and you choose what you want to insert into your formula.
Can be used to recognize a pattern in data and then automatically have the data entered for you?Flash Fill automatically fills your data when it senses a pattern.
Which of the following are functions you can use to calculate measures of central tendency?The three main measures of central tendency are Mean, Median and Mode. They all are valid measures of central location, but each gives a different indication of a typical value, and under different circumstances some measures are more appropriate to use than others.
|