Online tool · Excel split

Split an Excel workbook

Choose a workbook and a column, and get one workbook per distinct value in that column. By default each new workbook keeps the source workbook's sheets, formatting, and supported workbook structure, removing only the rows that belong to other values. Pivot tables and their caches are removed and reported, so review complex workbooks (pivots, external links, charts, ActiveX controls) in Excel before you deliver them. Everything runs in this page using the same operation the ConsultChimps library uses

Read the split guide

Files are processed locally in your browser and are not uploaded to ConsultChimps

1. Choose a workbook

Source workbook

Drag an Excel .xlsx or .xlsm workbook here, or pick one with the button below. Only the first workbook is used

Look inside this workbook

See the worksheets, header rows, columns, and sample values this workbook holds, including hidden worksheets, which the default split filters too

2. Choose the split column

Type the column name exactly as it appears in the header row

Column name

Matching ignores surrounding whitespace and letter case, unless strict matching is turned on below

Advanced options

Leave the worksheet, table, and range fields empty to split every worksheet that contains your column and keep the rest of the workbook in each new file

Filename prefix

Optional. Defaults to the source filename. A split that keeps the whole workbook keeps the source extension with it, so `clients.xlsm` produces `clients-North.xlsm` with its macros; the small, plain workbooks are always `.xlsx` and carry no macros

Worksheet

Optional. Splits one worksheet by name, and the new files then hold only that worksheet's matching rows

Excel table

Optional. Splits a named Excel table, which gives the safest data boundaries. The rest of the workbook is kept unless you turn that off below

Named range

Optional. Splits a workbook-level named range instead, and the new files then hold only that range's matching rows. Cannot be combined with an Excel table

Header row

Optional one-based row number. Not available when an Excel table or a named range provides the headers

Keep the whole workbookOn by default. The source workbook's sheets, formatting, and supported workbook structure are kept, and only the rows that do not belong are removed. Pivot tables and their caches are removed, with a warning on the result. Turn it off to get small, plain workbooks holding just the matching rows of the source being splitInclude blank valuesOnly applies when you name a worksheet, table, or range, or turn off keeping the whole workbook. Otherwise rows with a blank value never get a workbook of their ownInclude hidden worksheetsOnly applies when you name a worksheet, table, or range, or turn off keeping the whole workbook. Otherwise hidden worksheets are always split too, and stay hidden in every new fileStrict matchingTreat differences in letter case, surrounding whitespace, and value type as different valuesValues onlyReplace every formula with its most recently saved result

3. Preview

Choose a workbook and a column to see the workbooks this task will create

4. Run

Each new workbook keeps your file's sheets, formatting, and supported workbook structure, holding only the rows for one value. Pivot tables and their caches are removed and reported; review complex workbooks in Excel before delivery