How to Split a Large Excel File Into Multiple Files

Quick Tips

• There are many ways to split an Excel workbook into smaller files.
• You can chop up individual sheets by manually copying and pasting them into new files.
• Excel’s VGA code editor allows you to automate the process of exporting multiple sheets.
• You can also choose from multiple third-party solutions online.

Officially, a Microsoft Excel file can only be 1,048,576 rows by 16,384 columns. Surely nobody needs an Excel file larger than that, right? Well, you’d be surprised. Even if your Excel spreadsheet isn’t nearly that massive, you might still want to break it up into more manageable chunks.

This article will show the various ways of doing just that.

How to Split a Large Excel File Into Multiple Files

Let’s cover several different ways (official and third-party) of breaking up large spreadsheets into smaller ones.

Manually

Perhaps the simplest (though not the most glamorous) way to split up files is manually using only your mouse and keyboard shortcuts. Here’s how to do so:

1. Drag to highlight the rows and columns you wish to split off and copy them. You can do this by choosing “Copy” from the context menu (Right Click) or by keyboard shortcut (Ctrl + C).

2. Open up a new workbook (Ctrl + N) and paste the cells into the new spreadsheet. Again, you can use the context menu or the keyboard shortcut (Ctrl + V).

If your data has been formatted in a manner different than the blank spreadsheet you’re pasting into, you may want to keep these settings, in which case you’ll need to perform a special paste. Here’s how:

1. Copy your data from your existing worksheet (Ctrl + C) and open a new workbook (Ctrl + N).

2. In the new spreadsheet, click the Down arrow under the “Paste” button in the Home tab.

3. Choose the option that’ll keep your formatting intact. Hovering your mouse over an option will show you a preview of the spreadsheet. In this instance, we chose “Keep Source Column Widths.”

Excel VBA

Splitting workbooks manually for one or two spreadsheets may be doable, but what if you need to do the same for 30 or 40 sheets in one workbook? Luckily, Excel’s own programming language, Visual Basic for Applications (VBA), has got you covered. It’s more complicated but can automate the process and save you a lot of time. Here’s what to do:

1. Start by adding VBA to your “Review” tab. Right-click the tab and choose “Customize the Ribbon.”

2. In the window that pops up, in the right-most window, check the “Developer” box.

3. Click “OK” to confirm. The “Developer” tab will now be visible in your Excel menu.

Now you can open VBA and code a special process to export worksheets to separate files:

1. You’ll need to enable VBA code for your workbook by saving it in the right format. Save your file as an “Excel Macro-Enabled Workbook.” You can choose this option from the dropdown menu in the Explorer.

2. In the “Developer” tab, click the “Visual Basic” button to open the code editor.

3. When the new window opens, right-click “Sheet 1,” then “Insert,” then “Module.”

4. A new “Module” will appear in the menu. Rename it to describe the task it’ll perform, e.g. “Export_Sheet.” No spaces can be used.

5. Type the following into the Editor:

6. Then choose the sheet to export to a separate file, open Visual Basic again, and click the “Play” icon to run the process.

7. If successful, you’ll get a message and your sheet will be saved separately.

Third-Party

If the coding seems a little too daunting and manual isn’t cutting it, then you can also turn to third-party applications. There are plenty out there (Split CSV, CSV Splitter, and SplitSpreadsheets) that’ll automate the process for you. There are various free and paid options, but as with all third-party options, exercise caution.