Even though Excel workbooks are not the most collaborative spreadsheets to work on, there are some great ways to share data across your entire team — without sharing too much information. Keep confidential data to yourself, and only share what’s necessary with your team members.
In this post, I go a bit deeper into how to split an Excel sheet into multiple worksheets in Google Sheets. Control access to sensitive data and share data with employees, clients or customers automatically.
Why split Excel into multiple worksheets?
Imagine you are a Project manager and you keep all tasks inside an Excel file, which you regularly update with new to-do’s. Your team consists of 4 employees, each one has a different set of tasks to finish the overall project. Manually copy/pasting each task for the correct employee, and updating them via email is time-consuming and very unnecessary.
What if you could automatically distribute the tasks per owner to the correct team member’s Google Sheets spreadsheet?
How to distribute the Excel sheet
Sheetgo’s distribute feature allows you to split your Excel sheet into multiple worksheets in Google Sheets. In other words, it automatically creates multiple new Google Sheets with filtered information coming from your master Excel worksheet.
Start from your Excel sheet that holds the entire database. After that, distribute selected data into multiple other worksheets. Sheetgo automatically creates the new worksheets, based on your filter requirements.
Step 1: Excel to Google Drive sync
Firstly, it is necessary to automatically sync your Excel sheet to your Google Drive. To do so, use Sheetgo’s integration and automatically upload Excel to Google Drive. If you are unsure how to set it up, go through the first step of this article.
Note that if you have your Excel file stored on Dropbox, you can automatically access it from inside Sheetgo.
Step 2: Install Sheetgo
Install Sheetgo here. Log in with your Google or Dropbox account.
Step 3: Excel as your data source
Once you are inside the web application, click on Connect to start creating the connection.
Select your Excel sheet as the data source inside the Sheetgo web app, either straight from your Dropbox or from inside your Drive.
Step 4: Distribute to multiple Google Sheets
As a data destination, choose to Distribute to multiple Google Sheets in a folder.
Choose if you want the distributed worksheets to enter into a new or an already existing folder in your Drive.
Step 5: Select your source column
In this step, select the source column. The name or value in this column defines which spreadsheet the source data is distributed to.
Each unique entry in the selected column will create and populate a different spreadsheet. This criterion can be an employee name, a date, a client email, or any unique identifier.
Step 4: Name your connection
Finally, give the newly created (and distributed) sheets an appropriate name. Under Destination tab, enter a tab name (in this example: Sheetgo-Sheet1). This will be the same in every destination sheet and it will also be the name of the connection.
To finish, click on Finish and save. Sheetgo automatically creates new worksheets inside a folder with the distributed data coming from your Excel worksheet.
Step 5: Automate the workflow
If you wish to automate the data distribution from your Excel file to the different spreadsheets in your Google Drive folder, click on Automate on the floating bar of your workflow.
Choose to have automatic updates with an hourly, daily, weekly, or monthly frequency.
That’s it, this is how to split the Excel sheet into multiple worksheets, using our distribute feature. You will find the folder in your Google Drive with one spreadsheet linked to each unique source entry. The destination spreadsheets will be updated with filtered source data every time your connection runs.
With Sheetgo you can transfer data between Google Sheets, Excel, CSV, and TSV files. Create a Sheetgo connection and watch your data move from one sheet to another instantly. Learn here how to track changes in Excel files and create a historic record of your data.