How to split an Excel sheet into multiple worksheets

Written by
Laura Tennyson

As the most widely-used spreadsheet program on the planet, many companies store huge volumes of data in Excel files. Microsoft Excel has a fantastic range of functions and that’s what makes it such a popular piece of software. Anyone can turn a simple spreadsheet into a powerful and complex system to manage any aspect of their daily work.

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 with the Sheetgo add-in for Excel.

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.

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.

Let’s use an example of when I might want to split data in a spreadsheet to share specific data and control access. Let’s say I am a HR manager for a company. I have created an Excel spreadsheet containing the data of every single employee that works in the company on a global scale. I need to send the employee information of those who work in the Brazil offices to a colleague. I don’t want to share the entire Excel file, as it contains personal and private information. Instead, I can use the Sheetgo add-in in Excel to split my spreadsheet into multiple spreadsheets based on the countries where each employee works. Then, I will be able to send my colleague a newly created Excel that contains just the Brazilian employee data. 

Step 1: Install the Sheetgo add-in for Excel

  • 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.

Sheetgo add-in

Once signed up, it’s time to install the Sheetgo add-in to your Excel application. Click here to install the Sheetgo add-in from Microsoft Appsource.

 You can now find Sheetgo add-in logo on the top, right-hand side of your menu whenever you open an Excel file, as shown below.

how to split an excel sheet into multiple worksheets 2

Step 2: Select the Excel file you want to split

Open up the Excel file containing the data you wish to split. Make sure that the Excel file is saved onto a cloud storage folder to be able to begin your connection process. 

Open the Sheetgo add-in and click Select data under the Export data.

how to split an excel sheet into multiple worksheets 3

Now choose the tab where your data is located. Once finished, click Continue.

how to split an excel sheet into multiple worksheets 3

Note that your spreadsheets must be available online in order to split data with Sheetgo. If you’re working with local Excel files saved to your computer, that’s not a problem. Just follow the simple steps below to back up your Excel files to the cloud.

Are your files stored on your computer?

If you want to connect files that are stored locally (on your computer) you can set up an automated system to back up and sync files from your desktop to your online cloud storage service.

This enables you to create automated data flows using Sheetgo. It also keeps your files secure and allows you to access them from anywhere. Learn more.

Step 3: Select the destination 

To split your data into multiple files, under the Destination section, select Multiple spreadsheets in a folder..

how to split an excel sheet into multiple worksheets 4

Next, you can choose whether to create a new folder for your new files, or to add your files to an existing folder. 

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 new folder named “Employee information”.

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.

how to split an excel sheet into multiple worksheets 5
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.

Step 4: Select the source column

    Now it’s time to decide how we will split our data. Sheetgo allows you to choose which column will determine how your data is split.

    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, as I want to split my data based on the location of the employees, I will select Column G. 

    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 new destination spreadsheets named after each country. Then, data from each row in the source file will be distributed to the corresponding destination sheet.

    Now you can enter a new tab name for your new worksheets. 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. Once you’re happy with everything, click Finish and save. 

    how to split an excel sheet into multiple worksheets 6

    Step 5: Your Excel file splitting system is up and running

    The workflow has been created. You will be able to see an overview of your connection within the add-in. You can now run the workflow to start the data transfer process. Simply click the Run button. 

    how to split an excel sheet into multiple worksheets 7

    To find your newly created Excel files, simply search for the new folder in the storage folder you selected. In my case, when I open my OneDrive folder, I can clearly see my new folder containing all of my newly created files.

    how to split an excel sheet into multiple worksheets 8

    If I open my Brazil file, I can clearly see that in Column G, only the rows relating to the Brazil employees have been imported from my central source file.

    how to split an excel sheet into multiple worksheets 9

    Step 6: Schedule automatic updates

    You can now schedule automatic updates so that your new files always have the latest data. For example, if I add more employee information for the Brazil offices to my original Excel file, Sheetgo will automatically transfer this data to the corresponding Brazil Excel that I have just created. That way, my colleague still has access to the most recent data insights.

    To access this feature, head back to your Excel add-in and click the Automate button.

    how to split an excel sheet into multiple worksheets 10

    Simply choose the days and times you wish to update your workflow. Once you’re happy with your custom schedule, click Save.

    how to split an excel sheet into multiple worksheets 11

    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 Employee information database for new offices in Turkey, 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.

    You may also like…