How to Split an Excel File Into Smaller Files Safely

How-to basics · Updated November 20, 2026

To split an Excel file, first save a backup, then either copy each worksheet into its own workbook or filter a data sheet by category or row batch, copy the matching records into a new workbook, and save each workbook separately. Decide what should define each smaller file before you begin, and check that the finished files retain the headers and information people need.

“Split” can mean several things: separating tabs, making one file per customer or department, or dividing a long list into manageable row groups. Excel does not have one command that automatically creates all these kinds of files. The steps below cover dependable manual methods for Excel for Microsoft 365 on Windows, including a repeatable way to label row batches. Excel for Mac has similar workbook and filter tools, although some ribbon locations and keyboard shortcuts differ.

What You’ll Need

  • Excel for Microsoft 365 on Windows and the workbook you want to divide. The instructions also apply to many recent desktop versions of Excel.
  • A safe copy of the original file. Use File > Save As to create one before changing or moving sheets.
  • A clear split rule, such as one worksheet per file, one file per region, or 1,000 records per file.
  • A folder and a consistent naming plan for the output files, such as Sales_North.xlsx or Orders_Part_01.xlsx.

If the workbook contains confidential or linked information, also decide whether each recipient should receive only the relevant data or a full copy of the workbook. This affects which method is appropriate.

Step-by-Step

  1. Choose the kind of split. If each worksheet should become a separate file, use the worksheet method in step 2. If one data list needs to be divided by a field such as region, use the filter method in steps 3–6. If the list has no natural categories but needs to be divided into equally sized groups, use the batch method in step 7. You can use more than one method, but make a plan first so you do not omit or duplicate records.
  2. Make a separate workbook for each worksheet, if that is your goal. In the original workbook, right-click the tab of the sheet you want to separate and choose Move or Copy. In the dialog, choose (new book) from the To book list, select Create a copy, and choose OK. Excel opens a new workbook containing that sheet. Save it with a descriptive name using File > Save As. Repeat for each sheet. On Excel for Mac, right-click or Control-click the sheet tab and choose Move or Copy; the dialog may be laid out differently. Make sure Create a copy is selected if you want the original sheet to remain in the source workbook.
  3. Prepare a data list for splitting by category or rows. Work from a copy of the source file. Put one set of column headings in the first row, with one record per row beneath them. Remove completely blank rows within the list, check that each record has its key fields, and avoid merged cells in the data range. If the current sheet has a report title or notes above the column headings, copy the actual list to a clean sheet first. A simple, continuous table is easier to filter and copy reliably.
  4. Turn on filters and identify a group. Click a cell in the list, then choose Data > Filter. Excel adds drop-down arrows to the headings. Open the arrow on the field that defines each file, such as Department, and select one value. For example, choose North to show only North records. Check the visible results before copying; a misspelled category or an unexpected blank value can otherwise leave records out of the split.
  5. Copy the filtered records, including their headings. Select the heading row and the displayed data rows in the columns you want in the new file. To avoid copying rows hidden by the filter, choose Home > Find & Select > Go To Special > Visible cells only, then choose OK. Copy the selection, create a blank workbook with File > New, and paste into cell A1. Check that the headings appear once and that the pasted rows belong to the selected category. If you do not see the Go To Special command in the same place on Mac, use Excel for Mac’s Find & Select or Go To Special controls; the names and layout can vary by release.
  6. Save the new file, then repeat for every category. In the new workbook, choose File > Save As, select the intended folder, enter a name that identifies the category, and save as an Excel workbook (.xlsx) unless the recipient needs another format. Return to the source copy, clear or change the filter, and repeat the copy-and-save process for each group. Before moving on, compare the files made with the distinct category values in the source list so that every group is accounted for.
  7. For equal-sized row batches, add a batch-number column. Use this method when you want, for example, no more than 1,000 data rows in each output file. It assumes row 1 contains headings and data starts in row 2; put a heading such as Batch in an empty column, and enter the formula below in that column’s row 2. For this example, each batch contains 1,000 records, so rows 2–1001 are labeled 1 and row 1002 begins batch 2. The formula returns 1 in row 2. =ROUNDUP((ROW()-1)/1000,0)
  8. Fill the batch formula down and filter by batch. Fill the formula from row 2 through the last data row by dragging the fill handle, or select the relevant cells and use Home > Fill > Down. If the list is an Excel table, Excel may fill the calculated column automatically. Turn on the filter if it is not already on, then filter the Batch heading to show 1. Copy the visible headings and data columns into a new workbook as described above. Repeat for 2, 3, and each subsequent batch number. You can leave the Batch column out of the output if recipients do not need it.
  9. Check the saved workbooks before sharing them. Open each file and confirm it has the expected headings, records, and file name. Compare record counts with the source. If you used category filters, check each category once; if you used batches, the last batch may contain fewer rows. Also check for formulas that refer back to the original workbook, hidden columns, and sensitive data that should not be included.

Copying a filtered range is suitable for straightforward lists, but it may not reproduce every workbook feature. If a sheet has charts, buttons, complex formatting, pivot tables, or supporting formulas, copying the entire sheet with Move or Copy is usually more faithful. For a filtered data export, decide whether to preserve formulas or turn results into fixed values: formulas can bring references to the source file along when pasted into another workbook. Use Paste Special > Values when recipients only need the displayed results, and check the output before distributing it.

Common Mistakes

  • Copying only what is visible without the headings. Each smaller file should have its own header row so it remains understandable and can be sorted or filtered later.
  • Assuming a filter deletes the other records. Filtering only hides nonmatching rows. Copy visible cells to a new workbook; do not delete rows from the source just to create an output file unless you are working on a separate copy.
  • Overlooking blank or inconsistent category values. A filter may show a blank option, or values like “West” and “West ” may be treated as different entries. Review category values before splitting.
  • Including extra material unintentionally. A worksheet may contain hidden columns, notes, or other data outside the main table. Check the copied range and the saved workbook, especially when files will go to different people.
  • Expecting formulas and workbook features to transfer cleanly. Formulas may keep links to the original file, while a simple copy of cells may not include every chart, pivot table, or control. Choose a whole-sheet copy when those features matter, and test the result.
  • Saving every output under the same name or location. Use a consistent naming scheme and verify the destination in Save As. Otherwise, a later file may overwrite an earlier one or become difficult to identify.

Tips

  • Use names that sort naturally, such as Orders_Part_01.xlsx, Orders_Part_02.xlsx, and Orders_Part_03.xlsx. Add a category or date when it helps recipients identify the contents.
  • Keep the original workbook unchanged and save the split files in a separate output folder. This makes it easier to compare results and redo the split if a group was missed.
  • For row batches, choose a size that is convenient for the people using the files, not just a round number. If a recipient needs a maximum of 500 records, use 500 rather than assuming a larger batch will be acceptable.
  • For category splits, sort or review the source values first so you know how many distinct files to expect. Include a file for blank categories only if those records are meaningful and should be delivered.
  • Where formulas are important, keep the source workbook’s dependencies available or deliberately convert formula results to values. Open the output on the computer that will use it and check for broken links or warnings.
  • If a workbook is too large to handle comfortably, splitting it by rows may help with sharing or performance, but it does not guarantee a smaller file. Images, formatting, formulas, and embedded objects can account for much of a workbook’s size.

Troubleshooting

The new workbook includes hidden or unwanted rows. Return to the source, reapply the filter, and select only visible cells through Home > Find & Select > Go To Special > Visible cells only before copying. Check the selected range before pasting.

Some records do not appear in any output file. Clear the filters on the source and inspect the distinct values, including blanks and spelling variations. Compare the total number of source records with the sum of the records in the output files. If records are assigned by category, confirm every category was processed; if using batches, make sure the formula was filled to the last record.

The batch numbers do not start at 1 or contain uneven groups. Confirm the headings are in row 1 and the first record is in row 2, as assumed by the formula. Remove extra title rows or adjust the layout before filling the formula. Blank rows can also make a batch contain fewer actual records than expected, so remove them or account for them when checking counts.

Formulas show errors or ask for the original file. The copied cells may contain formulas linked to the source workbook. If only the current displayed answers are needed, copy the range again and use Paste Special > Values. If the calculations must remain live, keep the required source data and links available, then check them in the new workbook.

The saved workbook is still large or slow. Check for unused formatted rows and columns, large images, pivot caches, and other embedded objects. Copying a smaller data range can reduce content, but saving a sheet with substantial formatting or objects may not reduce the file much. If your goal is to get under an upload limit, check the service’s allowed file size and test the saved output rather than relying on row count alone.

Frequently Asked Questions

Can Excel automatically split a workbook into one file per sheet?
Excel for Microsoft 365 does not provide a standard one-click command to save every sheet as a separate workbook. Use Move or Copy to create a new workbook for each sheet manually. For a recurring large-scale task, automation may be appropriate, but review any macro carefully and work on a backup because macro changes cannot be undone with Ctrl+Z.

Can I split one worksheet into separate files by category?
Yes. Turn on filters, select one category at a time, copy the visible headings and records into a new workbook, and save it with a descriptive name. Repeat for every category, including blank or unusual values only if they belong in the outputs.

How do I split a worksheet into files with a set number of rows?
Add a batch-number column, fill a formula down the records, and filter for one batch number at a time. Copy each batch to a new workbook. Check whether your requested row limit includes the heading row; the example formula assigns 1,000 data records per batch, plus one heading row in each output.

Will splitting the file reduce its size?
Often it reduces the amount of data in each individual file, but the result depends on what makes the original large. Images, formatting, formulas, and workbook objects can remain substantial. Save and check each output to see whether it meets your size limit.

Can I split a file in Excel for the web?
You can filter and copy data in Excel for the web, but some desktop workbook commands and options may differ or be unavailable. For the most reliable sheet-copy workflow, open the workbook in the Excel desktop app and use Move or Copy. If using the web version, verify the saved files and copied content carefully.

Should I save the smaller files as XLSX or CSV?
Use XLSX when you need multiple sheets, formatting, formulas, or Excel features. CSV is a plain-text format for a single table and does not preserve formatting, formulas as formulas, or multiple worksheets. Save as CSV only when the person or system receiving the file specifically needs it.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.