Split an Excel workbook by column value

Write one workbook per distinct value found across the source's worksheets, keeping its sheets, formatting, and supported structure.

The Excel splitter creates one workbook for every distinct, non-blank value in a selected column. It edits copies of the original OOXML workbook instead of rebuilding worksheets, so the source file remains unchanged and each copy keeps the source's worksheets, formatting, and embedded objects. See Preservation and known limitations for what it changes, removes, or leaves for you to review.

Basic usage

consultchimps sheets split "Sprint 2 Datasets.xlsx" \
  --column "Entity Name" \
  --output-dir "./entities"

ConsultChimps searches every worksheet for Entity Name, even when the column is in a different position. It collects the union of values across those worksheets. Every output contains all original worksheets in their original order:

A worksheet containing Entity Name retains its header and only rows for the output entity.
A worksheet without Entity Name is copied unchanged.
Hidden and very hidden worksheets are retained with the same visibility.
Content above an automatically found header is retained.

The automatic header search selects the first cell whose trimmed, case-insensitive text equals the requested column name. Use --header-row 3 to restrict matching to a known one-based row when a workbook is ambiguous.

Matching and blank values

Default matching is designed for business exports:

surrounding whitespace is removed;
letter case is ignored;
Unicode text is normalized;
ordinary numeric text, such as "100", matches the number 100;
the original cell content is left unchanged in the output; and
blank and null values never create an output workbook.

Consequently, North, north, North , and North create one workbook. Use --strict when case, surrounding whitespace, and value type must remain distinct.

For plain worksheet ranges, every used row below the header is treated as a data candidate. A row with a blank split value is removed. A named Excel Table provides safer data boundaries when a sheet contains a footer or a second data block.

Formulas and values-only output

Formulas remain formulas by default, and existing workbook calculation settings are retained. Use --values to remove formulas across every worksheet while keeping their most recently saved cached results:

consultchimps sheets split "Sprint 2 Datasets.xlsx" \
  --column "Entity Name" \
  --output-dir "./entities" \
  --values

ConsultChimps does not calculate formulas. If Excel did not save a cached result, the values-only cell becomes a formatted blank, and a split value with no cached result reads as blank, so its row joins no group. The result counts such cells in formulaCellsWithoutCachedValues and warns with the affected worksheet and cell. Open and recalculate the source in Excel, save it, and run the split again when that value is required.

Filenames and overwrite safety

The normalized entity display value becomes the filename. Unicode and Arabic text are retained. Windows-invalid characters (< > : " / \\ | ? *), control characters, trailing spaces or periods, and reserved device names are handled safely. If two values sanitize to the same name, a stable -2, -3, and so on suffix is added.

All destinations are validated before any output is written. Existing files stop the operation unless --force is present:

consultchimps sheets split "Sprint 2 Datasets.xlsx" \
  --column "Entity Name" \
  --output-dir "./entities" \
  --values \
  --force

Outputs are staged in the destination directory and committed only after every workbook is created successfully. The source workbook is never overwritten.

Windows, macOS, and Linux examples

PowerShell on Windows:

consultchimps sheets split "C:\Work\Sprint 2 Datasets.xlsx" `
  --column "Entity Name" `
  --output-dir "C:\Work\entities"

macOS or Linux:

consultchimps sheets split "$HOME/work/Sprint 2 Datasets.xlsx" \
  --column "Entity Name" \
  --output-dir "$HOME/work/entities"

Target one table, range, or worksheet

The established single-source modes remain available for narrower jobs:

consultchimps sheets split clients.xlsx \
  --table ClientData \
  --column Region \
  --output-dir outputs/by-region

--table, --range, or --sheet selects the legacy one-source grouping path. A table split keeps the whole workbook by default and compacts matching rows while resizing its table and AutoFilter range. --no-preserve-workbook creates compact data-only outputs. Named-range and selected-worksheet modes are data-only.

Split a CSV file

consultchimps sheets split export.csv --column Region --output-dir outputs/by-region

A CSV file is one worksheet named after the file, with no workbook to keep, so it splits into one CSV file per value, or into compact workbooks with --output-format xlsx. --output-format csv splits a workbook into CSV files the same compact way. With no source named, every visible worksheet that carries the column gives one file per value, named <prefix>-<value> - <sheet>.csv when more than one worksheet does (orders-North - Returns.csv), and a worksheet without the column is left out with a warning. Hidden worksheets are included only with --hidden. Asking to keep the workbook with CSV output is refused. On the command line, rows with a blank value get a file of their own unless --skip-blank is given; the browser skips them unless asked. Fields stay text unless --csv-numbers or --csv-dates asks otherwise, and the encoding and delimiter are detected, with --csv-encoding and --csv-delimiter to override them; a byte order mark wins over a contradicting encoding. Each CSV output is UTF-8 with a byte order mark (--csv-bom false leaves it out), ends rows with CRLF, writes dates as ISO text, and puts an apostrophe before text that would start a formula. In the browser, "Output files" chooses CSV files or workbooks, and "Reading CSV files" appears for a CSV input. The CLI reference covers every flag under CSV files.

What the online tool does

The in-browser splitter runs the same all-worksheet split as the command line, so each workbook it creates keeps the source workbook's sheets, formatting, and supported workbook structure with the other values' rows removed, and removes the same pivot tables, described under Preservation and known limitations. Naming a table, a range, or a worksheet narrows it to that one source there too. Only the names of the finished files differ: the command line saves into the folder you choose and names each file after the value alone, such as North.xlsx, while the browser hands you downloads and keeps the source name in front, such as clients-North.xlsx. You can change that prefix in the tool. The browser accepts .xlsm workbooks as well, and both surfaces follow one rule. A split that preserves the workbook (the default, and a named Excel Table) copies the source package, so clients.xlsm gives you clients-North.xlsm with the macro project carried into each one, and a package whose declared type contradicts its name is refused before any file is written. A split that rebuilds instead (a named worksheet, a named range, or preservation turned off) writes fresh workbooks from the rows it kept, which are always .xlsx and carry no macro project.

Preservation and known limitations

The all-worksheet mode retains the OOXML package and changes only affected worksheet row XML, table ranges, formula metadata in values-only mode, calculation-chain references, and the pivot parts described below. This preserves sheet order and visibility, cell styles, number formats, row heights, column widths, merged cells, freeze panes, filters, conditional formatting, data validation, print settings, hyperlinks, comments, images, charts, workbook properties, and VBA project parts in .xlsm files.

Pivot tables are the one structure the split deliberately removes. A pivot cache is a private copy of every source row, and it travels inside the workbook whether or not anyone opens the pivot, so leaving it in place would hand each output the rows belonging to every other value. The split therefore strips every pivot table and its caches, counts them in metrics.pivotTablesRemoved, and says so in the result's warnings. Rebuild the pivot in Excel from an output's own rows when you need it.

Plain worksheet rows retain their original Excel row numbers instead of being compacted. This deliberately keeps formulas, drawings, validations, conditional formatting, and named-range coordinates stable; deleted rows may therefore appear as blank visual gaps. Excel Tables are compacted and resized. External links, ActiveX controls, unsupported extension parts, chart source ranges, defined names, and formulas that depend on removed rows are preserved but are not semantically rewritten or recalculated. Review complex workbooks in Excel before delivery.

What the Excel operations preserve is the full, structure-by-structure table, generated from the conformance contract these operations are tested against.

TypeScript API

import { splitWorkbookByColumn } from "@consultchimps/xlsx";

const result = await splitWorkbookByColumn({
  input: "Sprint 2 Datasets.xlsx",
  outputDirectory: "entities",
  column: "Entity Name",
  values: true,
});

console.log(result.metrics.outputFiles);
console.log(result.outputs);

result.outputs contains the retained and deleted row counts for each filtered worksheet in each output workbook. Progress callbacks receive the same counts while large jobs are staged.

Split an Excel workbook by column value

On this page