How to Split an Excel File in Half: A Step-by-Step Guide

How-to basics · Updated November 20, 2026

To split an Excel file in half, make two copies of the workbook, keep the header row in both, and delete the unwanted half of the data rows from each copy. This creates two separate Excel files while leaving the original intact. The steps below are for Microsoft Excel for Microsoft 365 on Windows and assume you want to divide one worksheet’s records into two groups; splitting by rows does not necessarily make the files exactly equal in size.

What You’ll Need

  • Microsoft Excel for Microsoft 365 on Windows. Excel for Mac has similar Save As, copy, and delete options, though some ribbon labels and shortcuts differ.
  • The workbook you want to divide, plus enough disk space to save two copies.
  • A clear idea of which worksheet contains the records and which row contains the column headings.
  • A backup or untouched original workbook. Avoid making the only copy of your data the one you edit.

Step-by-Step

  1. Save and inspect the original workbook. Open the file and use File > Save As to save a working copy in a convenient folder. Give it a name such as Orders-original.xlsx. If the workbook is already saved, still keep that original unchanged; you will create the two split files from it. Check that you know which sheet contains the records and whether it has a single heading row. If the workbook has several sheets, decide whether the split should apply to one sheet or to all related sheets. The instructions below divide the records on one sheet.
  2. Make sure the records are ready to divide. Look for a heading row at the top, followed by one record per row. If a filter is active, clear it before counting or selecting rows: go to Data > Sort & Filter > Clear. A filtered view can hide records and make a visible selection look like the whole data set when it is not. Also check for blank rows inside the records, notes below the table, totals, and footers. Decide whether those rows belong in one of the output files. If the data is an Excel table, keep its header row and make sure the table includes all intended records.
  3. Find the last actual record row. Click a populated cell in the column that identifies each record, such as an order number or customer ID. On Windows, press Ctrl+Down to move through a continuous block of filled cells. Check the row number shown at the left of the worksheet, and verify that the cell you reached is the last record—not a note, a total, or a blank gap. Ctrl+Down stops at gaps, so if the identifier column has blanks, inspect the data and find the last record another way. Ctrl+End moves to Excel’s last used cell, but formatting or old content can make that cell lower or farther right than the real data; treat it as a clue and verify the boundary before deleting rows.
  4. Work out the dividing point. Count the data rows, excluding the heading row. If the headings are in row 1 and the last record is in row 1001, there are 1000 records. A clean split is 500 records in each file: the first part uses rows 2 through 501, and the second uses rows 502 through 1001. If the record count is odd, the two parts cannot have exactly the same number of records; put the extra record in either part. For example, 999 records can be divided into 499 and 500. Write down the boundary rows before editing so you do not have to recalculate while deleting.
  5. Create the first output copy. Open the original workbook and choose File > Save As. Save a copy with a clear name, such as Orders-part-1.xlsx, in the folder where you want the finished files. Confirm the new name in the title bar or window before making changes. This copy will retain the first half of the records. Do not overwrite the original.
  6. Remove the second half from Part 1. In the relevant worksheet, select the row headers for the records after the dividing point. In the 1000-record example, select rows 502 through 1001 by clicking row 502, then holding Shift while clicking row 1001. Check that the heading row and the first 500 records are not selected. Right-click the selected row numbers and choose Delete, or use Home > Delete > Delete Sheet Rows. Deleting entire worksheet rows removes those records across all columns, which is usually safer than clearing only the cells in one column. Save the workbook.
  7. Create the second output copy from the original. Reopen the untouched original file, rather than making Part 2 from the already shortened Part 1 file. Choose File > Save As and save it as Orders-part-2.xlsx. Starting again from the original prevents missing records or accidental overlap between the halves.
  8. Remove the first half from Part 2. In the same worksheet, select the data rows from the first record through the row just before the second half begins. In the example, delete rows 2 through 501, leaving the heading row and original rows 502 through 1001. Right-click the selected row numbers and choose Delete, or use Home > Delete > Delete Sheet Rows. Excel shifts the remaining records up, but their order stays the same. Save the second workbook.
  9. Check both files before sharing them. Open each saved file and confirm that its heading row is present, the first and last records are where you expect, and the two ranges do not overlap or leave a gap. Compare their record counts; for an even split, they should match, and for an odd split they should differ by one. Check filters, formulas, totals, charts, and any other sheets that depend on the removed rows. If you need each file to be a self-contained copy, verify that formulas do not refer to missing records or to the original workbook.

On Excel for Mac, use File > Save As to create each copy and select rows by their row numbers before choosing Delete from the context menu. The Windows Ctrl+Down shortcut is Command+Down on many Mac keyboards, but its movement can depend on the current selection and keyboard settings; verify the destination rather than relying on the shortcut. Excel for the web can edit and save workbook copies too, but its menus and file-saving flow differ. If the workbook is stored in OneDrive or SharePoint, confirm that each copy has a distinct filename and that the changes have finished saving.

Common Mistakes

Counting the heading as a record: If row 1 contains column names, it is not part of the record count. Keep that row in both files, otherwise the second workbook may have no labels to identify its columns. When the headings occupy more than one row, keep all of them in both copies and adjust the row ranges accordingly.

Deleting cells instead of complete rows: Clearing selected cells can leave empty rows behind or shift only part of a record, misaligning values across columns. Select the worksheet row numbers and delete entire sheet rows when each record spans the full width of the sheet. If the sheet contains unrelated content in other columns, pause and decide whether deleting whole rows would remove that content too.

Splitting by the visible rows only: A filter or hidden rows can make the worksheet appear shorter than it is. Clear filters and account for hidden rows before calculating the boundary. Do not assume that selecting visible cells alone will produce a complete half of the underlying data.

Saving over the source: Editing the original or creating Part 2 from Part 1 can permanently remove records from the only complete copy. Use distinct filenames and create both output workbooks from the unchanged original.

Expecting equal file sizes: “Half” usually means half the records, not half the megabytes. Images, formatting, embedded objects, formulas, and workbook structure can make one copy larger than the other even when each has the same number of rows.

Tips

Use names that describe both the source and the range, such as Sales-2026-rows-1-to-5000.xlsx and Sales-2026-rows-5001-to-10000.xlsx. Clear names make it easier to identify the files later and reduce the chance of sending the same half twice. For larger datasets, include the record count in a note or in the filenames.

Keep a small log of the split: record the worksheet name, the original first and last record, the boundary, and the number of records in each output. This is especially useful when someone else needs to combine the files later. If a unique ID column exists, note the first and last ID in each half as well as the row counts; IDs can reveal a gap or overlap that a quick visual check misses.

If you are splitting a formatted range, check the results after deletion. Excel tables usually resize when their rows are deleted, but verify the table boundary and any total row. Review formulas, named ranges, charts, pivot tables, and summaries that relied on the full data. A formula in a split file may still calculate, but it may now show a result for only that file’s portion. Refresh pivot tables if appropriate, and make sure a displayed total is not being mistaken for a record.

For a repeated monthly task, consider using a repeatable process rather than deleting rows by hand each time. A separate copy of the original and a written boundary rule are simple safeguards. If the split must be based on a category, date, or customer rather than an equal number of rows, sort or filter using that rule and check that each record belongs in exactly one output. Keep the original data intact until both outputs have been checked.

Troubleshooting

Ctrl+Down stops too soon: There may be a blank cell in the column you used. Choose a different column that is filled for every record, or inspect the gaps and find the last record manually. Do not delete based on a row number until you have confirmed the actual last record.

The last used row seems much too low: Excel may remember formatting or old content below the data. Use the populated record column and inspect the rows around the suspected end. If you find obsolete formatting or content, remove it only after confirming it is not needed, save, and check the boundary again.

The two files have a gap or repeated records: Compare the last unique ID in Part 1 with the first unique ID in Part 2. Return to the original and recalculate the split boundary; do not try to repair both files independently if you are unsure which records are missing. Recreate the two outputs from the original so they form a clean, verifiable pair.

Formulas or summaries changed: Deleting rows can change formula results, chart ranges, or totals. Check whether a formula intentionally summarizes only the records remaining in that file. If you need the original overall total in both files, preserve it separately as a documented value or retain a summary sheet that does not depend on the deleted rows.

The file is read-only or will not save: Use File > Save As and save the copy to a folder where you have permission, with a new filename. If the workbook is shared, protected, or locked by another user, ask the owner for edit access or make an authorized copy first. Wait for a cloud save to finish before closing the workbook.

Frequently Asked Questions

Does splitting an Excel file in half mean splitting the rows or the file size?
Usually it means dividing the records into two groups. Equal record counts do not guarantee equal file sizes, because formatting, images, formulas, and other workbook contents affect size.

Should I copy the header row into both files?
Yes. Keep the column headings in both workbooks so each part can be understood and filtered on its own. If the original has multiple heading rows, retain all of them in both copies.

What if the number of records is odd?
One file must contain one more record than the other. For example, split 101 records into groups of 50 and 51, and note which file has the extra record.

Can I split one workbook into two files by worksheet instead?
Yes, if each worksheet is already a separate unit of data. Save a copy for each output, then remove the worksheets that do not belong in that copy. On Windows, right-click a sheet tab and choose Delete to remove a sheet; check formulas and links first, because other sheets may refer to it.

Will formulas, formatting, and charts remain in both files?
They generally remain in the workbook copies, but references and results can change when rows are deleted. Inspect formulas, charts, tables, and summaries in both outputs, especially if they were built to cover the complete original dataset.

Can I split the data without changing the original?
Yes. Save two separate copies from the original and make the row deletions in those copies. Keep the source untouched until you have opened and checked both finished files.

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.