How to split an Excel sheet into multiple worksheets

Despite this, there’s no inbuilt function to send data from one Excel file to another or transfer data from a master sheet to populate other files.

You might think that copy-pasting or writing code are your only options. Luckily, that’s not the case. Instead, you can send filtered data from one Excel workbook to other files automatically with Sheetgo.

Sheetgo is a no-code automation tool that enables you to split one Excel sheet into multiple worksheets or combine data from multiple files into one.

In this guide, I’ll show you how to distribute data from Excel to multiple other Excel workbooks, Google Sheets files, or CSV files.

The system is easy to set up and it will help you save a lot of time on repetitive spreadsheet tasks.

How to split an Excel sheet into multiple worksheets

1. Why split an Excel workbook?
2. Automation in spreadsheets: How it works.
3. How to split an Excel file automatically.
4. How to increase the number of sheets.

Why split an Excel workbook?

Share data and control access

It’s common to have one central file where you store and manage a lot of your key data. If you’ve spent a lot of time building the perfect spreadsheet and creating all your own formulas, you might not be happy to give others access to that file. Equally, it might contain sensitive information that can’t be shared with others.

Distributing or filtering some of that data to other spreadsheets allows you to share only the information that you want to share.

Data and task management

Imagine you have a database of total sales and you want to create a summary sheet for each product or each month of the year. Or you’re a project manager and you want to allocate tasks to specific colleagues.

You can automate all of these tasks with Sheetgo, saving you time and preventing mistakes. Here’s how it works:

Automation in spreadsheets

Microsoft has recently been making more of its desktop software cloud-compatible.

That’s great news for Excel users as it’s now possible to backup your local Excel files to OneDrive, or work on your spreadsheets directly from your browser using Excel for the web.

Excel for the web allows you to collaborate online with colleagues without sending file attachments back and forth.

Once your Excel file is synced to the cloud you can:

• Create connections other spreadsheets 
• Divide one Excel sheet into two
• Distribute Excel data to other spreadsheets automatically
• Split an Excel sheet and populate other files with filtered data
• Automate to save time on manual work and copy-pasting
• Control access to confidential data
• Allocate tasks to colleagues automatically
• Send specific data to other files on a regular basis

How to split an Excel file automatically

Follow the instructions below to learn how to split an Excel sheet into multiple worksheets.

Step 1: Open Sheetgo

• Sign up for Sheetgo via the button below.

You have the option to sign in with your Microsoft, Google, or Dropbox account. It’s possible to connect spreadsheets stored on any of those platforms, and to link files in different cloud storage folders.

Step 2: Select the Excel file you want to split

• Click the blue Connect button.
• Give your Untitled workflow a name at the top of the screen so you can identify it later. In this example, I’ll call my workflow Excel data distributor.
• Under Select source, select Sheet file(s).

The source file is the Excel workbook containing the data you want to split into multiple other sheets.

Step 3: Select the sheet you want to split

When you’ve selected your Excel source file, it appears on the screen inside Sheetgo.

Under File tab, make sure to select the sheet containing the data you want to divide and distribute to other sheets.

Here I have a file containing a list of my current stock. The source tab I want to distribute data from is called Total inventory.

 Step 4: Select a data processor

When you’ve selected the correct source file and tab, click the blue Next step button.

Under Select a data processor, choose Split.

Step 5: Select the source column

• Now select the source column in your source file. The values in this column define which file the data is distributed to.

Each unique value in the selected column will create and populate a different spreadsheet. This criterion could be an employee name, a date, a month of the year, or a client email address, for example.

Let me show you how this works.

In my example, I will select Column H: Demand from my source sheet.

Choose your recipient files

• Under Destination folder, select where you want Sheetgo to send the data to.

Sheetgo can distribute data from your source sheet to a folder of new files, or a folder of existing files.

Option A: Distribute data to new files

• Select New folder.

This folder will appear in your main (root) folder in the cloud storage platform indicated on the right of the screen.

• If you want to save the new folder to a different cloud storage platform, or inside a specific sub-folder, click Change destination folder.
• Enter a name for the new folder.

Here I want the files to be saved to my main OneDrive folder, in a folder named Inventory split per demand.

Note that if you select to send the data to a folder in Google Drive, Sheetgo will automatically create Google Sheets files. If you select OneDrive, SharePoint, or Dropbox, Sheetgo will create XLSX files.

Option B: Distribute data to files you’re already using

• Select Existing folder.
• Click +Select file to locate the destination folder.

Note that your destination files must be stored in the same folder.
Each destination file should have exactly the same name as the value, or criterion, in the source column.

If you have an entry in the source column and no corresponding destination file with the same name, Sheetgo will automatically create a new spreadsheet for you in the destination folder.

As I am selecting this as my source column, Sheetgo will create a new file for each value in this column.

In other words, it will create five new destination spreadsheets named Very Low, Low, Medium, High, and Very High.

Data from each row in the source file will be distributed to the corresponding destination sheet.

• Under Destination tab, enter a name for the new worksheet.

This is the name for the sheet containing the exported data from the source file. The name will be the same in every destination file. It will also be the name of the Sheetgo connection.

Here I’ll name it Exported from inventory

• Click Finish and save.

Your Excel file splitting system is up and running

The workflow has been created.

My new connection Exported from inventory appears inside Sheetgo:

Here you can see Sheetgo has created a new folder in my OneDrive called Inventory split per demand.

Inside that folder, it has created five spreadsheets: High, Low, Medium, Very High, and Very Low.

Let me open the first of the new spreadsheets, “Very high”.

The file contains a tab called Exported from inventory.

As you can see in Column H, only the very high-demand products have been imported from my central source file Inventory list.

Step 6: Update the connections

Now that you’ve created a workflow, you can update the connections at any time.

This will distribute fresh data from the central source file to the folder of destination files.

• Open Sheetgo, select the workflow from the list on the left.
• Click Run on the menu bar.

Step 7: Automate the workflow

To save time and ensure that you and your colleagues always have the latest data in your spreadsheets, schedule automatic updates.

• Open Sheetgo and click Automate on the menu bar.
• Enable Update automatically.
• Choose how frequently you want the data to be updated, or refreshed.
• Select which days of the week, or times of the day, you need updates.
• Click Save.

How to increase the number of sheets

The Sheetgo workflow we’ve created is an automated data distribution system.

So, if a data entry appears in the Excel source file and there’s no corresponding destination sheet, Sheetgo will create it automatically inside the destination folder.

For example, if I add a data entry to my Inventory List file with a Demand value of “Extremely high”, Sheetgo will create a new spreadsheet for me in the destination folder.

This enables you to send data to files for new colleagues, products, or categories automatically.

Automate your Excel work

That’s how to split an Excel worksheet into multiple worksheets automatically! Head over to Sheetgo to start connecting your files.

Want to do the reverse and combine data from multiple sheets into one Excel file? Learn how to merge Excel files here.