Customizing a worksheet in Origin is fast and easy with the array of Origin’s toolbar options or in Worksheet Properties dialog at one’s disposal. For most people, these options are enough to satisfy all of their customization needs. But what if you want to change or delete some built-in styles but couldn’t find the corresponding control on toolbar or in dialog, or what if you want to clear all styles you applied? What if you want to save a customized style for future worksheets? In this blog post we will show you how to do that with the Worksheet Theme Editor so you can take your data presentation to the next level.
The Worksheet Theme Editor option is hidden by default since it’s an advanced tool that most people would never have to use. To turn it on, go to Preferences: System Variables and enter WEF for variable and 1 for value— click OK.
Now, you can right-click on the worksheet title bar or in the gray space and see that there is a new option called Edit Formatting… which opens the Worksheet Theme Editor.
This dialog shows worksheet properties tree and the nodes are self-explanatory.
- Dimensions: Not recommended to do style customization here.
- Name Styles: Set styles of different aspects of worksheet, such as headers, label rows, data area, etc.
- Range Styles: Each time user select a range in worksheet and customize, a range will be created here.
- Individual entries below Range Styles node: Common settings of the sheet
Changing Built-in Styles
Let’s take a look at how we can change the customizations that are inaccessible by toolbar. For example, how to set row header to be left-aligned or how to show Ellipsis (…) if the row header is too long? It’s simple—we can change the row header alignment option and add an ellipsis style. See below:
Let’s scroll down to the bottom of the editor to find the Root worksheet settings. Some of these properties such as the background color (Bk Color), default row, and column size, also are only customizable by the editor. We encourage exploring the properties in the editor to see how they affect the worksheet and gain a deeper understanding of how they work.
Clearing Customizations in Built-in Result Sheet
Some Origin analysis tools will output the results in a built-in customized sheet. For example, in the Descriptive Statistics results, the worksheet will have a black border line on the right. That border will show up when the worksheet is exported as in image and may be undesirable. To clear it, find the \uid: node under Name Styles and delete it.
Clearing User’s Customizations
Worksheets with heavy customizations can sometimes be overwhelming in both the technical and the aesthetic. On the technical side, worksheets with many different overlapping ranged styles may have conflicting parameters and can cause slowness, unintended effects, and in extreme cases—crashes. On the aesthetic side, too many clashing colors and arrangements does a disservice to clear and concise data presentation. Additionally, you may also want to clear the customization in Origin generated reports. In any case, we can use the Worksheet Theme Editor and LabTalk commands to clear some customizations.
A worksheet may already have toolbar customizations applied to certain ranges within the data. These will show up in the Theme Editor under a node called Range Styles. These customizations are named after the range the apply to. For example, R1C3:R4C7 covers the enter range starting from row 1 column 3 to row 4 column 7 of the worksheet. The row and column index includes the headers. The customizations can be edited in the Theme Editor and the node can simply deleted through the right-click menu if they need to be cleared.
These range styles can also be cleared with LabTalk commands:
|Clear all the named ranges and range styles in the active worksheet
worksheet -scf [FILTERSTYLE]
|Clear specified cell formats FILTERSTYLE of the selected range(s). If no FILTERSTYLE is specified, all formats will be cleared. The FILTERSTYLE list can be found in oc_const.h flie (which is located in <exe folder>/OriginC/system/). The number indexes from 0. For example, index of fill color (OFST_Interior) is 6 and font (OFST_Font) is 8. Multiple FILTERSTYLE can be separated by a space
worksheet -crr [rowIndex]
|Clear the old banded row format (filling cell background color) in tables (worksheet table or intermediate table inserted in Graph window). Clears the formatting top down starting from the rowIndex of the entire worksheet (includes column labels)
For example, here is an older project that changed the background cell colors to imitate Banded rows. But this customization is incompatible with the Banded Rows feature added in Origin 2023. To clear the old color customization and make way for the new, we can use
worksheet -crr 3 to clear starting from the 3rd row of the worksheet.
Saving a Theme
Once you have customized your properties in the Worksheet Theme Editor to your liking, you can save the properties as a theme file. This theme file can then be reused to quickly apply its settings to other worksheets. Simply click ‘Save As…’ and name the theme file. Check the ‘Set as System Theme’ to make the settings default for every new worksheet you create.
The theme files you save can be accessed from the Theme Organizer. You can find it by going to Preferences: Theme Organizer or by pressing F7. This dialog controls every type of theme file in Origin. We will navigate to the Worksheet tab to to see our saved worksheet theme. Right-click on a theme for some basic management operations. such as duplicate, add, and delete. Saved themes can applied to certain scopes in the project by selecting them in the ‘Apply Theme To’ dropdown.
To edit a theme in the Theme Organizer, simply double-click on the listed theme and a very familiar property tree will pop up.
Theme editor is a powerful tool for customizing your worksheets and requires some understanding of how the worksheets are built from a technical level. The same can be done for graph themes in the Graph tab of the Theme Organizer. But that is an area we’ll leave for you to explore.