4.4.3 Splitting Worksheet into New Sheets

Description

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 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 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. Available when Split Mode = By Number of Columns. Specify every n columns to create a new worksheet (not available for By Reference 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. Available when Split Mode = By Number of Rows. Specify every n rows to create a new worksheet (not available for By Reference Columns). Available when Split Mode = By Column Label. Specify the column label row for grouping columns into new worksheets. 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. Output Reference Column data with split results (applies to By Reference Columns only). Exclude empty cells or cells containing Origin's missing value character ("--"), from split result (applies to By Reference Columns only). Keep the source worksheet intact. Otherwise the source worksheet is deleted. Show Sparklines for the columns in the output worksheets. Specify output worksheet. For help with the range controls, see: Output Results 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"). 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: Open the Command Window or Script Window, type the following and press Enter: @SSG = 1 When you open the wsplit dialog, check the Keep Short Name check box when specifying your options.