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.

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