Excel Split Workbook: How to Separate Sheets

If you need to split a workbook in Excel, the fastest way is to use the built-in “Move or Copy” feature to move each sheet into its own new file, or use a simple VBA macro to automate the process for many sheets at once. This article explains both methods, along with how to split by tabs, by data, and how to handle common issues like broken links and formatting.

What Does “Split Workbook” Mean?

Splitting a workbook means taking one Excel file that contains multiple worksheets (tabs) and dividing it into several separate files. For example, a monthly sales report might have a sheet for each region: North, South, East, West. Splitting would create four new files, each containing one of those sheets. This is different from splitting a window or splitting a cell’s text into columns, which are unrelated features.

People typically split workbooks for sharing, archiving, or reducing file size. A workbook with 20 sheets can become slow to open and difficult to email. Splitting it into smaller files makes each one easier to manage.

Method 1: Manual Move or Copy (Best for a Few Sheets)

For a workbook with just a handful of sheets, the manual method is quick and requires no formulas or code.

1. Right-click on the sheet tab you want to split out (e.g., “North”).
2. Select Move or Copy from the context menu.
3. In the dialog box, under “To book,” choose (new book) from the dropdown.
4. Check the box Create a copy if you want to keep the original sheet in the current workbook. If you leave it unchecked, the sheet will be moved (removed from the current file).
5. Click OK. A new workbook opens with that sheet.
6. Save the new file (Ctrl+S) with a meaningful name.

Repeat for each sheet you want to separate. To move multiple sheets at once, hold Ctrl (or Cmd on Mac) and click each tab you want, then right-click and choose Move or Copy. All selected sheets will go into the same new file.

Method 2: VBA Macro (Best for Many Sheets)

If you have dozens of sheets, doing this manually is tedious. A simple VBA macro can split every sheet in the workbook into its own file in one click.

To use VBA:

1. Press Alt+F11 to open the Visual Basic for Applications editor.
2. Go to Insert > Module.
3. Paste the following code:

Sub SplitSheets()
    Dim ws As Worksheet
    Dim folderPath As String
    folderPath = ThisWorkbook.Path & "\"
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        ws.Copy
        ActiveWorkbook.SaveAs folderPath & ws.Name & ".xlsx"
        ActiveWorkbook.Close False
    Next ws
    Application.ScreenUpdating = True
End Sub

4. Press F5 to run the macro. Each sheet is saved as a separate .xlsx file in the same folder as the original workbook.

This macro does not remove the sheets from the original workbook; it only creates copies. If you want to also delete the original sheets after copying, add a line like ws.Delete inside the loop, but be careful—deleting is permanent unless you save a backup first.

Method 3: Split by Data Within a Sheet

Sometimes “split workbook” actually means splitting a single large worksheet into multiple files based on a column’s value. For example, a list of all customers might need to be split into separate files per state. This is not a built-in Excel feature, but you can do it with a pivot table or a VBA macro that filters and copies rows.

A common approach is to use the Subtotal feature (under Data tab) to insert page breaks between groups, then use “Save As” with the “Publish as Web Page” trick—but that is outdated. A cleaner method is to use the Filter feature manually: filter each unique value, copy the visible rows, paste into a new workbook, and save. For large datasets, a VBA macro that loops through unique values in a column and copies matching rows to new files is more efficient.

What Happens to Formulas, Links, and Formatting?

When you split a workbook, formulas that reference other sheets will break. For example, if a summary sheet has =SUM(North!B2) and you move the North sheet to a new file, the formula will show a #REF! error because the reference is lost. To avoid this, you have three options:

• Convert formulas to values before splitting (copy, paste special as values). This is safe but loses the ability to update.
• Keep the original workbook intact and only copy sheets (not move). The original still has the links, but the new file will still have broken references unless you break them.
• Update links manually after splitting. In the new file, go to Data > Edit Links and re-point to the original workbook if it still exists.

Formatting, column widths, and conditional formatting are preserved when you copy a sheet to a new workbook. However, page setup (print area, margins) may reset to default. Check the Page Layout tab before printing.

Common Problems and How to Fix Them

Here are frequent issues people encounter when splitting workbooks:

• File names with invalid characters: If a sheet name contains characters like / : * ? ” < > |, the macro will fail to save. Rename the sheet first or add a cleaning step to the macro.
• Too many files: Splitting a workbook with 100 sheets creates 100 files. This can clutter your folder. Consider splitting only the sheets you need, or use subfolders.
• Hidden sheets: The VBA macro above copies all sheets, including hidden ones. If you don’t want hidden sheets, add a condition like If ws.Visible = xlSheetVisible Then.
• External links: If your workbook has links to other workbooks, those links will remain in the split files. Use Data > Edit Links to break or update them.

Alternatives to Splitting

If your goal is to share only part of a workbook without creating separate files, consider these alternatives:

• Save as PDF: Export selected sheets to a PDF for viewing without editing.
• Protect sheets: Instead of splitting, protect the sheets you don’t want others to edit.
• Use a shared workbook: Excel’s co-authoring feature lets multiple people work on the same file simultaneously, avoiding the need to split.
• Group sheets: If you only need to print or view a subset, group the sheets (Ctrl+click tabs) and then print or export.

Frequently Asked Questions

Can I split a workbook without losing the original?

Yes. Use the “Move or Copy” dialog and check the “Create a copy” box. For VBA, the macro above creates copies and leaves the original untouched. Always save a backup before running any macro that deletes sheets.

Why does my macro save files with .xlsx but the original was .xlsm?

If your workbook contains macros, saving as .xlsx will strip them out. To preserve macros, change the file extension in the SaveAs line to .xlsm (e.g., ActiveWorkbook.SaveAs folderPath & ws.Name & ".xlsm").

How do I split a workbook by a column value (e.g., by department)?

Use a VBA macro that loops through unique values in that column, filters the data, and copies the visible rows to a new workbook. This is more advanced, but many free templates are available online. Alternatively, use the Filter feature manually for a one-time task.

Will splitting a workbook reduce its file size?

Usually yes, because each new file only contains a fraction of the data. However, if the sheets share a lot of formatting or images, the reduction may be less than expected.

Can I split a workbook on Excel for Mac?

Yes, the Move or Copy feature works the same on Mac. VBA is also available in Excel for Mac, but the keyboard shortcut is Option+F11 to open the editor.

What if I get a #REF! error after splitting?

This means a formula references a sheet that no longer exists in that file. Replace the formula with a static value (copy, paste special as values) or fix the reference manually by pointing to the correct sheet in the new file.

Related

• Batch Content Creation for Short-Form Video Workflow
• Use Alipay Without a Chinese Bank? Yes, Here’s How
• Partition Magic Alternative: Best Free Options