How to Split One Excel File into Separate Files by Category

The problem with splitting data manually

You've got one large Excel spreadsheet — maybe it's a sales export, a job list, a supplier register, or a client database — and you need to break it into separate files, one per region, department, product category, or team. So you start filtering, copying, pasting into a new workbook, saving it, going back, changing the filter, and repeating. Twenty categories means twenty rounds of that.

It's tedious, it's slow, and it's surprisingly easy to make mistakes. You paste the wrong rows, forget to clear the filter first, or save over a file you meant to keep. By the time you're done, you're not entirely sure every file is accurate — and neither is anyone you send them to.

Here are several practical ways to split a single Excel file into separate files by category, from fully manual to fully automated.

Method 1: Filter, copy, and save (manual — fine for a handful of categories)

If you only have three or four categories and this is a one-off task, the manual approach is perfectly reasonable. Here's how to do it cleanly:

1. Click the dropdown on your category column and select one value.
2. Select all visible rows (use Ctrl + Shift + End after clicking the first data row, then Alt + ; to select visible cells only — this avoids copying hidden rows).
3. Copy and paste into a new workbook.
4. Save the file with a clear name, such as Sales_North_2024.xlsx.
5. Return to the original, change the filter, and repeat.

The key mistake people make here is skipping the visible cells only step. If you don't select visible cells only, Excel copies hidden rows too — meaning every file ends up with the full dataset. Always use Alt + ; after selecting your range.

Method 2: Use a PivotTable to check your categories first

Before you split anything, insert a PivotTable with your category column as the row label and a count of records as the value. This gives you an instant list of every unique category in the file and how many rows each one contains.

This is worth doing because raw data is rarely as clean as you think. You'll often find entries like "North", "north", "NORTH", and "North " (with a trailing space) all sitting in the same column — which means your filter will miss rows, and your split files will be incomplete. Fix those inconsistencies before you start splitting, not after.

Method 3: Use a VBA macro to automate the split

If you have many categories, or this is a task you repeat regularly, a simple macro will save you a significant amount of time. The logic is straightforward: loop through each unique value in your category column, filter the sheet for that value, copy the visible rows to a new workbook, and save it.

Here's a basic structure you can adapt:

Sub SplitByCategory()
    Dim ws As Worksheet
    Dim CategoryCol As Integer
    Dim Categories As Collection
    Dim Cell As Range
    Dim CategoryValue As String
    Dim NewWb As Workbook

    Set ws = ThisWorkbook.Sheets(1)
    CategoryCol = 3 ' Change to your category column number
    Set Categories = New Collection

    ' Collect unique values
    On Error Resume Next
    For Each Cell In ws.Range("C2:C" & ws.Cells(ws.Rows.Count, CategoryCol).End(xlUp).Row)
        Categories.Add Cell.Value, CStr(Cell.Value)
    Next Cell
    On Error GoTo 0

    ' Loop through each category
    Dim Cat As Variant
    For Each Cat In Categories
        ws.UsedRange.AutoFilter Field:=CategoryCol, Criteria1:=Cat
        Set NewWb = Workbooks.Add
        ws.UsedRange.SpecialCells(xlCellTypeVisible).Copy NewWb.Sheets(1).Range("A1")
        NewWb.SaveAs ThisWorkbook.Path & "\" & Cat & ".xlsx"
        NewWb.Close False
    Next Cat

    ws.AutoFilterMode = False
    MsgBox "Done! Files saved to: " & ThisWorkbook.Path
End Sub

Paste this into the Visual Basic Editor (Alt + F11), adjust the column number to match where your category data sits, and run it. For a spreadsheet with 30 categories, this completes in seconds rather than the 45 minutes it would take manually.

A few things to check before running:

• Make sure your category column has no blank cells — blanks will create an unnamed file and may cause an error.
• Ensure the folder path exists and you have write permissions.
• Test on a copy of the file first.

Method 4: Use Power Query to split data without a macro

If you'd rather avoid VBA, Power Query offers a cleaner — though slightly less automated — route. Load your data into Power Query, group by your category column, and use the "Extract" function to produce a separate query per category. You can then load each query into its own sheet or workbook.

This method is best when you also need to transform the data as part of the process — for example, renaming columns, removing blank rows, or standardising formats before the split happens. Power Query handles transformation and splitting in the same workflow, which keeps things tidy.

The downside is that saving each query result as a separate file (rather than a separate sheet) still requires some manual steps. For pure file output, the VBA approach or a dedicated tool is more efficient.

Where things go wrong — and how to avoid it

Regardless of the method you use, the same handful of issues cause most problems:

• Inconsistent category values — "London", "london", "LONDON" are three different categories to Excel. Clean your data first.
• Special characters in category names — If a category contains a forward slash, colon, or asterisk, Excel will refuse to save a file with that name. Strip or replace those characters before splitting.
• Header rows missing from output files — Make sure your copy range includes the header row, or recipients won't know what each column means.
• Files overwriting each other — If two category values are identical after stripping special characters, they'll save to the same filename. Check for this edge case.

When the process needs to repeat regularly

If splitting a master file into category-based outputs is something you do weekly or monthly — for example, distributing regional reports, sending job packs to different teams, or feeding separate systems — setting this up as a repeatable, reliable process matters more than the one-off fix.

Tools like Smart Data Blender are built for exactly this kind of recurring data workflow: taking a source file, applying rules (including splitting by field value), and producing consistent output files without manual intervention each time — particularly useful when the source data itself changes format or structure between runs.

Quick summary

• For a one-off split with few categories: filter manually, use Alt + ; to copy visible cells only.
• Always audit your category column with a PivotTable before splitting — dirty data produces incomplete output files.
• For many categories or repeated tasks: use the VBA macro approach or Power Query.
• Watch for special characters in category names, missing headers, and blank category values.
• If the split is a regular part of your workflow, invest in automating it properly — the time savings compound quickly.