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. Show 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. 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 spreadsheetsMicrosoft 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:
How to split an Excel file automaticallyFollow the instructions below to learn how to split an Excel sheet into multiple worksheets. Step 1: Open Sheetgo
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. SheetgoStep 2: Select the Excel file you want to split
The source file is the Excel workbook containing the data you want to split into multiple other sheets. 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 sheet you want to splitWhen 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: Distribute the data to other sheetsWhen you’ve selected the correct source file and tab, click the blue Continue button. Under Send data, choose Multiple spreadsheets in a folder. Choose your recipient files
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
This folder will appear in your main (root) folder in the cloud storage platform indicated on the right of the screen.
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
Note that your destination files must be stored in the same folder. 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 5: Select the source column
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. Take a look at my source sheet. As you can see, Column H displays the level of demand for each product in my inventory. 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.
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
Your Excel file splitting system is up and runningThe workflow has been created. My new connection Exported from inventory appears inside Sheetgo: Click on Workflow to see how your files are connected. From the Workflow or Files menu, double-click on the new folder to view the spreadsheets. 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, “High”. The file contains a tab called Exported from inventory. As you can see in Column H, only the high-demand products have been imported from my central source file Inventory list. Step 6: Update the connectionsNow 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.
Step 7: Automate the workflowTo save time and ensure that you and your colleagues always have the latest data in your spreadsheets, schedule automatic updates.
Get started and create a Sheetgo account now. SheetgoHow to increase the number of sheetsThe 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 workThat’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. How do you split data into multiple worksheets by rows count in Excel?In the Split Data into Multiple Worksheets dialog box: Select Fixed rows from Split based on option, and enter the number of rows count that you want to split for each worksheets. (If your data has headers and you want to insert them into each new split worksheet, please check My data has headers option.)
How do I split two Excel workbooks at once?Compare two or more worksheets at the same time. On the View tab, in the Window group, click New Window.. On the View tab, in the Window group, click View Side by Side .. In each workbook window, click the sheet that you want to compare.. To scroll both worksheets at the same time, click Synchronous Scrolling.. |