How to Split a Large Excel File Into Multiple Files

Handling large Excel files: Detailed explanation of multiple segmentation methods

Officially, Microsoft Excel files can contain up to 1,048,576 rows and 16,384 columns. You might think no one needs an Excel file larger than this, but that's not the case. Even if your Excel sheet is far from this size, you may still want to split it into smaller pieces that are easier to manage.

This article will introduce several different ways to segment large Excel files.

How to split a large Excel file into multiple files

Let's look at several different ways (official and third-party) to split large spreadsheets into smaller spreadsheets.

Manual segmentation

Perhaps the easiest (although not the most convenient) way is to manually split the file using just mouse and keyboard shortcuts. The operation steps are as follows:

1. Drag to highlight the rows and columns you want to split and copy. You can do this by selecting Copy from the context menu (right-click) or using the keyboard shortcut (Ctrl C).

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

If your data format is different from the blank spreadsheet you paste into, you may need to keep these settings, in which case you need to perform a special paste. The operation steps are as follows:

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

2. In the new spreadsheet, click the down arrow below the Paste button in the Start tab.

3. Select an option that keeps the format constant. Hovering over the option will display a preview of the spreadsheet. In this example, we selected "Reserve source column width".

Using Excel VBA

For one or two spreadsheets, manually splitting the workbook may work, but what if you need to do the same for 30 or 40 worksheets in one workbook? Fortunately, Excel's own programming language, Visual Basic for Applications (VBA), can solve this problem. It is more complex, but it can automate the process and save a lot of time. The operation steps are as follows:

1. Start by adding VBA to your Review tab. Right-click the tab and select Custom Ribbon.

2. In the pop-up window, in the rightmost window, select the "Development Tools" check box.

3. Click OK to confirm. The Development Tools tab will now appear in your Excel menu.

Now you can open VBA and write a special process to export the worksheet to a separate file:

1. You need to enable the VBA code for your workbook by saving the file in the correct format. Save your file as a Macro-enabled workbook. You can select this option from the drop-down menu in Explorer.

2. In the Development Tools tab, click the Visual Basic button to open the code editor.

3. When a new window opens, right-click Sheet 1, then click Insert, and then click Module.

4. A new "module" will appear in the menu. Rename it to describe the task it will perform, such as "Export_Sheet". Spaces cannot be used.

5. Type the following into the editor:

6. Then select the sheet you want to export to a separate file, open Visual Basic again, and click the Play icon to run the process.

7. If successful, you will receive a message and your worksheet will be saved separately.

Using third-party tools

If the encoding looks a bit too complicated and manual segmentation is not convenient enough, you can also turn to third-party applications. There are many such applications (Split CSV, CSV Splitter and SplitSpreadsheets) that automate the process for you. There are a variety of free and paid options, but like all third-party options, use with caution.

The above is the detailed content of How to Split a Large Excel File Into Multiple Files. For more information, please follow other related articles on the PHP Chinese website!