Merge workbook tabs

The workbook merge copies every worksheet from the source workbooks into one workbook, keeping each as its own separate tab. Nothing is stacked into a single table: formatting, formulas as stored, and layout travel with their worksheets.

Which one do I want?

Both operations combine several Excel workbooks into one file; they differ in the shape of the result. sheets merge keeps every source worksheet as its own tab, while sheets consolidate stacks all rows into a single table.

Rule of thumb: tabs kept means merge; rows stacked into one sheet means consolidate. Consolidation has its own guide, and both operations also run in your browser: merge and consolidate.

Basic usage

consultchimps sheets merge "inputs/**/*.xlsx" --output outputs/all-sheets.xlsx

This copies all worksheets with their separate tabs and formatting/layout supported by Excel. A visible Sheet Index tab records the source file, original and final tab names, and hidden/visible status. Duplicate worksheet names receive a suffix; use --no-index to omit the index.

What carries over

The merge copies each source worksheet as it is stored rather than rebuilding it, so conditional formatting, data validation, merged cells, hyperlinks, cell comments, Excel Tables, defined names, number formats and cell styles arrive with their worksheets. Names that have to stay unique across one workbook are made unique: a duplicate Excel Table or defined name takes a numeric suffix, and formulas that referenced a renamed worksheet or table are updated to follow it. Four things are removed, each reported as a warning in the result: pivot tables and their caches (a cache holds a private copy of its source rows), external links (they are addressed by position in a single workbook's list), the calculation chain (Excel rebuilds it, and the merged workbook asks it to), and a macro project unless the first workbook you list is the only one carrying macros and the output is named .xlsm. That last case is reachable from the library and the browser today, not from this command: sheets merge collects .xlsx inputs only, so an .xlsm handed to it is filtered out before the merge starts.

What the Excel operations preserve sets these promises out structure by structure, beside what the split and the workbook inspection do with the same structures.

The workbook merge is also the tool the "Try Merge tabs online" button at the top of this page opens: merge workbooks in your browser to combine tabs without installing anything. The browser tool takes .xlsx and .xlsm workbooks and follows the same output rule as the command line: the output filename decides the type, so end it in .xlsm to keep a macro project and leave it alone for an .xlsx. Only the first workbook's macro project can travel, and only when no other workbook carries one. Consolidation has its own guide and its own browser tool.

Replace formulas with values

Add --values when the merged workbook must contain no formulas:

consultchimps sheets merge "inputs/**/*.xlsx" \
  --values \
  --output outputs/all-sheets-values.xlsx

ConsultChimps replaces each formula with the result stored in the source file. It edits the workbook cell in place, so number formats, fonts, fills, borders, alignment, row heights, column widths, hidden state, and workbook layout are preserved. A formula that has never been calculated and therefore has no stored result becomes a blank cell with its formatting intact; the result counts such cells in formulaCellsWithoutCachedValues and names them in a warning. Excel Table calculated column and totals formulas are also removed so Excel does not recreate them.

TypeScript API

The library operation accepts resolved input paths in the order they should appear:

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

const result = await mergeWorkbooks(
  ["north.xlsx", "south.xlsx"],
  "outputs/all-sheets.xlsx",
  { includeSheetIndex: true, overwrite: false, values: true },
);

console.log(result.metrics.outputSheets);