How to split an Excel file by column — and email each part to the right person

You have one big spreadsheet and each region, warehouse, client or supplier needs only its own rows. Splitting the file is the part everyone talks about. Sending each part to the right person is the part that actually eats your morning — and the one most methods leave entirely to you.

Splitting is only half the job

Cutting a spreadsheet into groups takes a macro, a filter, or thirty seconds in a browser tool. Then the real work starts: twenty files to attach to twenty near-identical emails, each one to a different address, each one a chance to send the wrong supplier the wrong numbers.

That second half is why a forty-minute job stays a forty-minute job even after you automate the splitting. It is also the part Split & Send was built for: alongside each file it produces a ready-to-send email draft, with the right recipient, your message and that file already attached.

✋ Nothing is sent for you. The drafts land in your mail client and you send them. That is deliberate: when the attachment contains a supplier's figures, you want to see the message before it leaves. Tools that send automatically save you a click and take away the last checkpoint.

How it works, start to finish

🔒 Privacy: the spreadsheet is processed locally in your browser and never uploaded anywhere — you can check in your browser's network tab. That is what makes it usable with real supplier and payroll data.

Which method should you use?

There are four common ways to split an Excel file by a column. Note the last column: three of the four hand you the files and stop there.

If you only ever split two or three groups once, any of these is fine. The difference shows up when it is twenty groups, every week, to twenty different people.

Method 1 — Manual (filter and copy)

Good for a one-off with very few groups. Apply a filter on your column, select one value at a time, copy the visible rows into a new workbook, and save. It works, but it is slow and easy to get wrong once you have more than a handful of values — and you have to redo it every time the data changes.

Method 2 — VBA macro

If you are comfortable with code and run the exact same split repeatedly, a macro can automate it. Press Alt+F11, insert a module, and adapt a routine like this (set splitCol to your column number):

Sub SplitByColumn()
    Dim ws As Worksheet, data As Worksheet
    Dim lastRow As Long, i As Long, key As String
    Dim splitCol As Long: splitCol = 2   ' column to split by
    Set data = ActiveSheet
    lastRow = data.Cells(data.Rows.Count, splitCol).End(xlUp).Row
    For i = 2 To lastRow
        key = CStr(data.Cells(i, splitCol).Value)
        On Error Resume Next
        Set ws = Sheets(key)
        On Error GoTo 0
        If ws Is Nothing Then
            Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
            ws.Name = Left(key, 31)
            data.Rows(1).Copy ws.Rows(1)
        End If
        data.Rows(i).Copy ws.Cells(ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1, 1)
        Set ws = Nothing
    Next i
End Sub

Full working code that saves one file per value — not just sheets — is in the VBA macro guide.

The downsides: it creates sheets rather than separate files (you still have to save each one), it does not handle emailing, and every colleague who needs it must enable macros — which many companies block for security.

Method 3 — Power Query

Power Query (Get & Transform) can group rows and, with some setup, export groups. It is powerful if you already live in Power Query, but it is overkill for a quick split and still leaves the "one file per value" and emailing steps to you.

Whichever of the three you pick, you end up in the same place: a folder of files, and the emails still to write.

Frequently asked questions

Related: How to email each part of a spreadsheet separately · Mail merge with a different attachment for each recipient.