4.3.6 Displaying Supporting Data in Worksheet Header Rows
In addition to the Long Name, the Origin worksheet header area allows other supporting information that might be associated with the dataset to be displayed. By default, the workbook template that ships with Origin only shows the Long Name, Units, Comments and F(x) row headings.
In some cases -- for instance, when importing data files in which header information has been pre-defined -- these optional worksheet row headings and the supporting information will display automatically. The information contained in these header rows then becomes available for plotting and analysis operations.
Note, however, that once an optional header row has been displayed, you can manually enter information in any header row cell by double-clicking on the cell and editing the cell contents. Data in these fields do not have to derive from file import operations in order to be displayed or to be used in any of the enhanced worksheet and plotting operations that make use of header row (column heading) data. The text and numbers in these header rows -- like text and numbers in data rows -- can be formatted using the Style and Format toolbar buttons.
For information on the worksheet column Long Name, see Origin worksheet column naming conventions.
Also, be sure to see Simple Utilities for Filling Columns with Data.
|Note: The functionality discussed in this page is available to users writing LabTalk script. For more information, see Column Label Row Characters.
Displaying supporting information in column label rows
By default, the Origin worksheet template displays four column label rows -- Long Name, Units, Comments and F(x)= (column formula). These column label rows can be added to or hidden depending on your need to display supporting information in the worksheet.
There are two ways to control label row display:
- Right-click on (a) the window's title bar or (b) the empty space to the right of the worksheet columns (but inside the workbook window) and choose View from the shortcut menu (see the picture below) and make your row heading display selections.
- Right-click in the area of the worksheet column label rows and choose Edit Column Label Rows from the shortcut menu. This opens the Column Label Rows dialog box where you can select those label rows that you wish to display. Drag to reorder label rows or double-click to rename UserDefined parameters.
The Units row stores the units for the worksheet data columns. Origin can make use of this information to annotate graphs. For instance, if the Long Name and Units are specified, they can be used to automatically label the X and Y axes of a 2D graph.
Another possible use for the Long Name and Units information is in the graph legend.
If after import, Units are appended to Long Name, (1) select the Long Name label row and (2) on the Mini Toolbar that appears, click the Extract Units from Long Name... button . If you prefer to leave Units appended to Long Name while also copying Units to the Units label row, clear the Remove from source box.
Each worksheet column can have one or more lines of Comments. Multiple comment lines can be typed into the Comments cell by pressing CTRL + ENTER at the end of each line. If you are importing files in which you've pre-defined multiple lines of comments, these are preserved and placed in the Comments cell.
The F(x)= header row displays either:
- The column formula. This is a mathematical expression that is typically used to fill the column with values.
- The Formula Text. This is alternate text that can be typed directly into the cell or entered into the Formula Text dialog box to display in place of the column formula. For instance, when displaying a long formula in the cell is not practical, enable display of the Formula Text and display some meaningful indicator of the purpose of the column formula.
Direct editing of the cell can be turned on (default) or off. To turn off direct editing of F(x)=:
- Open Set Values (with the column selected, choose Column: Set Column Values).
- Click the Options menu.
- Clear the check mark next to Direct Edit Formula Cell.
For help with creating column formulas, see Set Values, Quick Start and Set Values, Options Menu.
Use the Hotkey CTRL + SHIFT + U to show/hide the F(x)= row. You also can hide/show this row using corresponding shortcut menus, see this section.
Creating the F(x)= expression
Expressions are entered into the F(x)= cell in one of three ways:
- Double-click on the cell and type an expression directly into the cell (assumes direct-edit is not disabled, as discussed in the previous section).
- Enter an expression in the upper panel of the Set Values dialog box.
- Enter an expression using a shortcut menu command (see next section).
Beginning with Origin 2017, you can use a simpler spreadsheet notation when defining your column formulas. This new notation supports references to data in other books and sheets. For more information, see Column Short Name Restriction and Column Formula Examples.
F(x)= shortcut menu commands
Assuming direct editing is enabled, you can right-click on the F(x) cell, and enter information into the cell using one of the following:
You can also load formulas directly in the column label row F(x)= cell by selecting the cell and choosing Column: Fill with User Formula from the main menu.
F(x)= shortcut menu commands for Python function calls
When the column formula calls a Python function, an additional Open Python File shortcut menu command is shown, if:
- The F(x)= cell formula begins with py..
- The Python function is saved to an external file. By default that file is named labtalk.py and is saved to your User Files Folder. See documentation for the LabTalk Python object for information on current working directory and working file).
Selecting an F(x)= cell that meets the above criteria will show the path to the current working directory and file, on the Status Bar.
The Categories column label row displays when you have designated the worksheet column as containing categorical data (select the column, right-click and choose Set as Categorical ). The cell contents will show (a) whether data are sorted or unsorted (<auto> is selected) or (b) a space-separated list of categories:
- If you double-click this cell, you open the Categories dialog box (same as the Column Properties, Categories tab), with controls for display of the categorical data in this column.
- If you right-click this cell, you open a "categorical" shortcut menu that duplicates some key Categories tab controls.
- Set or clear column as categorical
- Copy categories (includes setting the column as categorical)
- Paste categories (includes setting the column as categorical)
- Set as <auto> (displays sorting info, dynamically generates categories from list) or display space-separated categories (not dynamically updated)
- Load previously saved categories (default path is \User Files\Categories\)
- Save categories to file
- Choose Properties to open the Categories dialog box
For information on creating and customizing graph legends for plots of categorical data, see Legends for Categorical Data.
The Parameters rows can be used for displaying supporting data such as temperature, pressure, humidity, wavelength, etc. but since these built-in parameters cannot be renamed, most will find the UserDefined parameters to be more useful.
For an example of how worksheet Parameter information might be incorporated into an Origin graph, see Customizing Waterfall Plots.
- Right-click on any column label row to select Edit Column Label Rows... from context menu to open the Column Label Rows dialog box.
- Check the Show check box for the label Parameter#, click OK button to add the parameter into the worksheet as a column label row.
- Double-click on the parameter row heading you just added to open the Move to User Parameter dialog to change the parameter name and set it as a User Parameter.
In an effort to reduce file size, some data files -- particularly those consisting of data collected at regular intervals -- may exclude the independent variable (X column). When this is the case, the Sampling Interval row will display the sampling (X) interval. You can see a quick demonstration of how this works by dragging and dropping a .WAV file into the Origin workspace.
Sparklines can provide a quick view of a set of related datasets contained in the Y columns in a worksheet. This is much easier than creating individual graph windows for each XY plot and then attempting to arrange them in the workspace so that they can all be viewed simultaneously.
Each sparkline is an editable embedded graph which can be opened by double-clicking on the sparkline object (see the picture below) above each column of worksheet data. Additions to the graph are stored -- though not necessarily displayed -- in the embedded object. Hovering on a sparkline shows a preview of the data plot.
See the section on Embedding Graphs and Images in Worksheet Cells.
You can add any number of user-defined parameter rows and assign any name to them.
- Right click on any column label row and select Add User Parameters from the context menu.
- Activate the worksheet and select Format: Worksheet from the menu. This opens the Worksheet Properties dialog box.
- Click the Edit Column Label Rows button to open the Column Label Rows dialog box.
- Right-click in the empty space below the labels and choose Add User-Defined Parameter from the shortcut menu.
- Double-click on the word "UserDefined" to edit the label.
- Use the Show boxes to control which labels to show or hide.
- Drag the grid cells (see the picture below) to the left side of the Labels column to rearrange the label order in the worksheet header rows.
Note that user-defined parameter rows can also be created on data import, provided your data files contain metadata in the header and you have pre-defined parameters using the Origin Import Wizard.
- To move the contents of a built-in label row (e.g. Comments) to a user-defined parameter row, double-click on the built-in row heading; or right-click and choose Set As User Parameter. These actions open a Move to User Parameter Row box where you can enter the name of your user-defined parameter and move the row content to the user-defined parameter row.
- To rename a user-defined parameter, double-click on the parameter row heading and enter a name in the Name box of the opened Edit dialog. In this dialog, you can enable and enter a Formula for the user parameter for the purpose of calculating some column statistic, such as mean or standard deviation.
- Numbers in Parameters and User-Defined Parameters rows can be formatted like numeric data in the data portion of the worksheet. Right-click on a row header or cell, select Set Parameter/User-Defined Style: More. In opened Format Cells tab, you can set Format and Display. See the details in this section.
Formatting User Parameters Data
All worksheet column label row data are stored as strings, even data that appear to be numeric. User Parameter rows do support the formatting of data that have the appearance of being numeric (e.g. a Julian date value such as "2458395"). Other column label rows cannot be so formatted.
- Right-click on a row heading.
- From the shortcut menu, choose Set User-Defined Style: More. This opens the Worksheet Properties dialog box for the selected cells.
- Click the Format tab and set Format and Display drop-down lists to the desired format.
- When formatting column label row data as Text & Numeric, Date or Time data, you do not have the same Custom Display options that are available, for instance, in the Column Properties dialog box. You must choose an existing option from the Format Cells dialog's Display drop-down list.
- You can apply formatting to Date and Time data only if the string stored in the user-defined parameter row is in numeric form (i.e. a Julian Date value as used internally by Origin). Formatting such numeric strings will also allow you to use these column label row values for calculation purposes such as in a worksheet cell formula (e.g. =value(wcol(j)[D1]$)).
- The user should always remember that despite whatever formatting options are applied, column label row data are still string data -- not numeric data.
Define a Formula when you Add User Parameters
You can add a user-parameter row to the worksheet expressly for the purpose of calculating some column statistic, such as mean or standard deviation.
- Right-click on the column label row headings and choose Add User Parameters from the shortcut menu.
- In the Add User Parameters dialog that opens, enter a name for your user-defined row, then click the flyout menu to pick from a list of common statistics. The statistic will be calculated for each data-containing column in the worksheet.
- To make changes to a User Parameter row Name or Formula, right-click on the User Parameter row heading and choose Edit from the shortcut menu.
The above procedure automatically extends your formula across all worksheet columns. However, you can also extend a label row formula to all cells to the right of the formula-containing cell by hovering in the lower-right corner of the formula cell and double-clicking on the "+" icon.
Shortcut Menu for Column Label Rows
The right-click menu of the column label rows offers controls to edit worksheet headers.
Insert User Parameters
Select Insert:User Parameters from the right-click menu to insert a User Parameter above current column label row.
Hide/Show Column Label Rows
To hide one or more rows, you can right-click on column label row to select Hide or Clear and Hide from the context menu. If you select Clear and Hide, the information in the current label row will be cleared up and the entire row will be hidden.
To show the hidden column label rows, you can do selections from the View shortcut menu or go to the Column Label Rows dialog. See the details in this section.
Rename Row Label
The default displayed column label rows, Long Name, Units, Comments and F(x)= are not able to be renamed, also the Parameter row. Double-click on their row heading(right-click on the heading to select the Edit... context menu), the Move to User Parameter dialog will pop up to let you rename the row label, but convert it into a user parameter.
- Double-click on the row heading to open the Edit dialog.
- Enter a new name in the Name box, enable and enter a formula in the Formula box if needed.
- Click OK to close the dialog.
Edit Customize Label Rows
Right click on the column label row and select Edit Column Label Rows... from the context menu, it will open the Column Label Rows dialog. In this dialog, you can hide/show a column label, reorder label rows, add a user-defined parameter, and specify the height for each label row.
Move Column Label Rows
- Right click on the column label row, select Move Column Label Rows, then select Move to Top/to Button/Up/Down in context menu.
Set Other Rows as Label Rows
To set one or more rows (column label rows or data rows) as three of the standard column headings (Long Name, Short Name and Units), user-defined parameters, right-click and select Set As Long Name/Units/Comment/User Parameters from the context menu.
Append Rows to Label Rows
- Right-click on one or more rows (column label rows or data rows), select Append To Comment/Long Name from the context menu.
To change the text style in the label rows, right click on one column label row, point to the Set Label Name Style menu item, you can set the text in the label rows to rich text, wrap text and/or Float. By clicking the More... item, you can directly go to the format tab of worksheet properties.
Copy and Paste Column Label Rows
To copy column label row metadata along with your data selection, right-click and choose Copy (including label rows). Non-contiguous selections and worksheet subrange selections are supported.
- To paste only label row values, right-click on the cell in the upper-left corner of the label row area and Paste (Origin will not add User Parameter rows).
- To paste only data row values, right-click on the cell in the upper-left corner of the data area and Paste (Origin will add data rows, as needed).
- To paste both label and data values, select the left-most column by clicking on the column heading and Paste. Both metadata and data will be pasted to the worksheet. Columns, data rows and User Parameter rows will be added, as needed.