4.4.5 Splitting Worksheet into New Sheets

Description

SplitWorksheet.png

Origin has a tool for splitting a worksheet into multiple worksheets. You can specify that every n columns be split into a new sheet; that every n rows be split into a new sheet; or that columns that share a column header row label be grouped into a new sheet. The wsplit dialog box uses the wsplit X-Function.

To Split Your Worksheet
  1. Activate the intended worksheet.
  2. Click Worksheet: Split Worksheet... and open the wsplit dialog box.

Dialog Options

Recalculate

Specify the Recalculate Mode.

  • None
  • Auto
  • Manual
Input Worksheet

The worksheet to be split. Note that starting with Origin 2017b, you can CTRL + select a column range to be split (no need to split the entire sheet).

For help with range controls, see: Specifying Your Input Data

Split Mode

Determine how the worksheet is to be split:

  • By Number of Columns
    Every n columns will be split into a new worksheet. Specify n in the By Number of Columns box.
  • By Number of Rows
    Every n rows will be split into a new worksheet. Specify n in the By Number of Rows box.
  • By Column Label
    Columns sharing a specified header row label will be split into a new worksheet. Specify the label-containing header row in the By Column Label box.
  • By Reference Columns
    Columns will be split into new worksheets by values or conditions in Reference Columns. Note that list order is important when splitting by values in multiple reference columns.
Number of Columns Available when Split Mode = By Number of Columns. Specify every n columns to create a new worksheet (not available for By Reference Columns).
Retain 1st N Columns Available when Split Mode = By Number of Columns or By Column Label. Specify first n columns of the source Worksheet to keep them in all split result sheet. And these first n columns are excluded from splitting By Number of Columns or By Column Label.
Number of Rows Available when Split Mode = By Number of Rows. Specify every n rows to create a new worksheet (not available for By Reference Columns).
Column Label Available when Split Mode = By Column Label. Specify the column label row for grouping columns into new worksheets.
Reference Columns Available when Split Mode = Reference Columns. Specify column(s) containing grouping variable(s).

When multiple columns are chosen, list order determines how data are split.
When single column is chosen, a further control Split drop-down list will show up to let you decide whether split the columns by values or other conditions(such as reference values jumping or turning).

Split This control is only available for single reference column selected. Specify how to split the worksheet according to the selected reference column:
  • By Value
    Split the worksheet rows according to the values in reference column. The rows with same value in reference column will be split and extracted into a new worksheet. This is the default selection for the Split control of selected single reference column.
  • By Direction Change
    Split the worksheet rows when the direction of the values in reference column changed, from ascending to descending, or vice versa.
  • By Direction Reset
    Split the worksheet rows when the reference values finished going up/down and have been reset to the base.
  • By Value Change
    Split the worksheet rows when the reference value changes.

Split By Reference Columns.png

Include Turning Point in When you selected By Direction Change for the Split control, you can use this drop box to decide how to deal with the turning points, to include them in the previous group or next group.
Keep Reference Columns in Result Output Reference Column data with split results (applies to By Reference Columns only).
Sort Result by Reference Columns Specify whether sort the split results rows by the reference values when you checked Keep Reference Columns in Result.

This check box is only available for the Split control set to By Direction Change or By Direction Reset.

Exclude Missing Value Exclude empty cells or cells containing Origin's missing value character ("--"), from split result (applies to By Reference Columns only).
Keep Source Worksheet Keep the source worksheet intact. Otherwise the source worksheet is deleted.
Show Sparklines Show Sparklines for the columns in the output worksheets.
Output Worksheet Specify output worksheet.

For help with the range controls, see: Output Results

Preset Result Sheet Name Available when Split Mode = By Reference Columns and Split = By Value:


%N: Long Name or Short Name of Reference Column (e.g. LongName, LongName1, etc.)
%C: Dataset Name of Reference Column (e.g. Book1_A, Book1_A1, etc.)
%M: Comment of Reference Column (e.g. Comments, Comments1, etc.)
%U: Units of Reference Column (e.g. Units, Units1, etc.)
%V: Cell Value of Reference Column (e.g. Group 1, Group 2, etc.)

Note that names can be comprised of concatenated variables and include arbitrary text (e.g. "%N=%V" will produce sheets named as "Column Name=Cell Value").

Since Origin 2019b, this option has been set to %V by default to output each subgroup to a new sheet with the group-info sheet name.


Note: From Origin 2017 column Short Name is restricted to alphabetical order and it cannot be edited by default. For this reason, the Keep Short Name option is hidden in this dialog. If you are opening a pre-Origin 2017 project or if you have turned off Spreadsheet Cell Notation in the current book, and you want to keep customized column Short Names after splitting the worksheet, you need to:
  1. Open the Command Window or Script Window, type the following and press Enter:
  2. @SSG = 1
  3. When you open the wsplit dialog, check the Keep Short Name check box when specifying your options.