Split Each Excel Sheet into Separate Files

Updated 6 November 2026 · Standard formula, shown below

If you have a workbook with multiple sheets and you need each sheet as its own standalone Excel file, the fastest method is to use Excel’s built-in “Move or Copy” feature, which lets you copy a sheet to a new workbook in a few clicks. For a single sheet, this takes about ten seconds; for many sheets, you can automate the process with a simple VBA macro that loops through all sheets and saves each one as a separate .xlsx file. This article explains both approaches, when to use each, and how to avoid common pitfalls like broken formulas or lost formatting.

Why You Might Need to Split Sheets

Workbooks often accumulate multiple sheets for different months, regions, departments, or clients. Splitting them into separate files is useful when you need to:

Send only one sheet to a colleague or client without exposing other data.
Reduce file size and improve performance, especially if the workbook has many heavy sheets.
Use each sheet as an input for another system or tool that expects a single-sheet file.
Archive or version-control each sheet independently.

Keep in mind that splitting creates copies, so the original workbook remains untouched. You can also split a sheet into a new file without removing it from the original, which is the safest way to work.

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

Excel has a built-in command that copies a sheet to a new workbook. Here are the exact steps:

Open the workbook that contains the sheets you want to split.
Right-click the sheet tab at the bottom of the window (e.g., “January”).
Choose Move or Copy from the context menu.
In the “To book” dropdown, select (new book).
Check the box Create a copy — this leaves the original sheet in place.
Click OK. Excel opens a new workbook with just that sheet.
Save the new workbook with File > Save As and choose a location and file name.

Repeat for each sheet you need. This method is straightforward, but if you have dozens of sheets, it becomes tedious and error-prone.

Method 2: VBA Macro to Split All Sheets at Once

For a workbook with many sheets, a small VBA macro can save each sheet as a separate file automatically. Here is a safe, general-purpose macro that copies each sheet to a new workbook and saves it in the same folder as the original file:

Press Alt+F11 to open the Visual Basic for Applications (VBA) editor.
Click Insert > Module to add a new code module.
Paste the following code:

Sub SplitSheets()
    Dim ws As Worksheet
    Dim newWb As Workbook
    Dim savePath As String
    
    savePath = ThisWorkbook.Path & "\"
    If savePath = "\" Then
        MsgBox "Please save the workbook first."
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
    For Each ws In ThisWorkbook.Worksheets
        ws.Copy
        Set newWb = ActiveWorkbook
        newWb.SaveAs savePath & ws.Name & ".xlsx", FileFormat:=51
        newWb.Close False
    Next ws
    
    Application.ScreenUpdating = True
    MsgBox "Done! Files saved in: " & savePath
End Sub

Press F5 or click Run to execute the macro.
Each sheet is saved as a separate .xlsx file in the same folder as the original workbook, named after the sheet tab.

This macro copies each sheet to a new workbook and saves it, leaving the original workbook intact. It uses FileFormat:=51 which is the code for the standard .xlsx format. If your sheets contain macros, you may need to save as .xlsm instead (FileFormat:=52).

What Happens to Formulas, Formatting, and Data Validation?

When you copy a sheet to a new workbook, Excel copies everything on that sheet: values, formulas, formatting, data validation, conditional formatting, and charts. However, there are important caveats:

Formulas referencing other sheets will break. If a cell in the copied sheet refers to a cell in another sheet (e.g., =Sheet2!A1), that reference will become invalid because the other sheet is not in the new workbook. The formula will show a #REF! error.
Named ranges that refer to other sheets may also break or become local to the new workbook.
External links to other workbooks will remain, but they will point to the original file path, which may not be desirable.
Print settings and page setup are copied along with the sheet.

To avoid broken formulas, you can either replace cross-sheet references with static values before splitting, or use a macro that converts formulas to values. A simple way is to copy the sheet, then in the new workbook select all cells, copy, and paste as values (using Paste Special > Values). But that loses formulas entirely, which may not be what you want.

Common Mistakes and How to Avoid Them

Here are the most frequent issues people run into when splitting sheets:

Forgetting to check “Create a copy” — if you don’t, Excel moves the sheet out of the original workbook, leaving it empty. Always check the box unless you intentionally want to move it.
Saving in the wrong format — if your workbook contains macros, saving as .xlsx will strip them and may cause a warning. Use .xlsm in that case.
File name conflicts — if two sheets have the same name (which Excel doesn’t allow) or if a sheet name contains characters like / or :, the Save As will fail. The macro above uses the sheet name directly, so avoid illegal characters like \ / : * ? " < > | in sheet names.
Running the macro without saving the workbook first — the macro checks for a path, but if the workbook is unsaved, ThisWorkbook.Path is empty and the macro stops with a message. Save the file first.
Not checking for hidden sheets — the macro above loops through all worksheets, including hidden ones. If you only want visible sheets, add a check like If ws.Visible = xlSheetVisible Then.

Alternatives: Power Query, Third-Party Tools, and Google Sheets

If you prefer not to use VBA, there are other ways to split sheets:

Power Query — you can load each sheet as a query and then output each to a separate file, but this is more complex and not a one-click solution.
Google Sheets — you can use the IMPORTRANGE function or manually copy sheets to new Google Sheets files, then download them as .xlsx. This works well for collaboration but requires re-uploading if you need local files.
Third-party add-ins — some Excel add-ins offer a “split workbook” feature. These are not built into Excel, so you must be cautious about installing third-party software. Always download from reputable sources and check reviews.

For most users, the built-in Move or Copy method or the VBA macro above is sufficient and does not require additional software.

Frequently Asked Questions

Will splitting break my formulas?

Only formulas that reference cells on other sheets will break. Formulas that reference cells on the same sheet will continue to work normally. After splitting, check for #REF! errors and replace those references with static values or update them to point to the new file.

Can I split a single sheet into multiple files?

Yes, but that is a different task. To split a sheet by rows (e.g., every 100 rows into a new file), you would need a more advanced VBA macro or use Power Query to filter and export. The methods above split by sheet, not by data within a sheet.

Does the original workbook change when I split?

No, if you use the “Create a copy” option or the VBA macro above, the original workbook remains unchanged. The macro copies each sheet to a new workbook and saves that copy, leaving the original intact.

How do I split sheets in Excel for Mac?

The Move or Copy feature works the same way on Mac. For VBA, the steps are identical, but the keyboard shortcut is Option+F11 to open the editor. The macro code above works on Mac as well, though file paths use a forward slash or the same backslash convention depending on your system.

Can I split sheets without saving each file manually?

Yes, the VBA macro automates the saving process. You run it once and it creates all files in the same folder as the original workbook. You do not need to click Save for each one.

What if my sheet names have spaces or special characters?

Spaces are fine in file names. Special characters like \ / : * ? " < > | are not allowed in Windows file names. If a sheet name contains one of these, the Save As will fail. Rename the sheet before running the macro or modify the macro to replace illegal characters.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.