Split an Excel Sheet Into Multiple Files Based on a Column Value

Suppose one workbook holds every region and you need a separate file for each region. Splitting by column value groups rows by Region, then creates one output for West, East, and each other value. Decide whether you need separate files or simply separate worksheet tabs.

A worked example

Start with a table containing Customer, Region, and Amount. Filter Region to West, copy the visible rows into a new workbook, and name it clearly. Repeat for the remaining values. Count the rows before and after: the total across outputs should equal the original row count, including rows with a blank region.

Method 1: Filter and copy

This is quickest for two or three groups. Select the whole table, turn on filters, choose one value, and copy only visible rows. Include the header in each output. Recheck the filter each time so a hidden row does not disappear from all files.

Method 2: PivotTable report filter pages

Excel's Show Report Filter Pages can make a worksheet for each filter value. It produces PivotTable summaries rather than copies of every original row, so choose it when a separate report per group is the goal.

Method 3: Power Query or a script

Power Query is useful for repeatable imports and transformations. Producing a separate physical workbook per category needs an export step beyond an ordinary query. A reviewed script can do that when there are many groups, but test the output names and row counts first.

What to check before sending the files

• Decide what blank category values mean and give them a named output if they should be kept.
• Check whether formulas or charts depend on rows outside a group.
• Use safe, unique filenames for category names containing slashes or punctuation.
• Reconcile the row count across all outputs with the source.

Split by column value in TidyCell

Open Split Workbook, choose an XLSX or CSV file, and select Region as the split column. Preview each group and check the row totals, then create a ZIP containing one XLSX file per region. You can also choose one workbook with a sheet per group. Title and header rows repeat in every output; blank categories go to Unassigned unless you explicitly exclude them.

This mode exports the selected worksheet as values. It does not carry over live formulas, formatting, or charts. The tool shows its token cost before you confirm.

Open Split Workbook →

Common questions

Should I use separate files or separate worksheet tabs?

Use tabs when one person reviews everything in one workbook. Use files when each group goes to a different owner or system. Keep the source workbook so you can repeat the split if a group or rule changes.

What happens to rows with a blank category?

Give them a named group such as Unassigned instead of silently dropping them. Reconcile the row count: all outputs together should contain every source data row once.

Will formulas and charts survive the split?

Formulas can still refer to the original workbook or fail when their dependencies move. Charts based on the full table do not automatically become a chart for each group. Check references and chart source ranges in every output before sharing it.