How to split an Excel file by column value
You have one master sheet — every branch, client, or department in one place — and you need one file per value. Here are the three real options, with their tradeoffs.
Option 1: VBA macro (free, fragile)
A ~40-line VBA script can loop over unique values, copy matching rows to new workbooks, and save them. It works — until it doesn't. Common failure points: merged title rows above your header, formatting that doesn't survive Range.Copy, and the macro breaking silently when a column moves. You'll also need macro-enabled files, which many IT departments block.
Option 2: Power Query (free, steep)
Power Query can filter and load subsets, but it's built for pulling data in, not for producing dozens of formatted workbooks out. Generating 50 separate files means 50 queries or a scripted workaround. In the words of one 30-year Excel veteran on Reddit: "I never got the hang of it."
Option 3: A browser splitter (no setup)
ReportSplit does one thing: drop your .xlsx, pick the column, get a zip with one formatted file per value. Three things make it different from the scripts above:
- Formatting survives. It clones your original workbook and removes non-matching rows — colors, merged titles, number formats, and conditional formatting stay intact.
- Nothing is uploaded. The file is processed inside your browser. IT-friendly by construction.
- Recipes. Save the settings once; next month, drop the new file on the recipe and it reruns in one click.