4.4.3 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

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 in Reference Columns. Note that list order is important when splitting by values in multiple 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.
Keep Reference Columns in Result Output Reference Column data with split results (applies to By Reference Columns only).
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 = Reference Columns:


%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").


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.