Merging of Excel files: combine multiple worksheets
If you spend a large proportion of your day working in Excel, a significant chunk of that time will probably be spent gathering, moving and organizing your data. Spreadsheets are one of the most flexible, accessible and powerful tools we have for managing information— which means that many companies store most, if not all, of their data in them. This can pose a challenge when it comes to extracting, analyzing and reporting. It’s often time-consuming and difficult to locate and combine the information you need from a vast sea of data and duplicated files scattered across the company. Thanks to online file sharing via OneDrive and SharePoint we can now collaborate with colleagues online, but the cloud enables us to go a step further with the merging of Excel files. What does that mean?
Once your Excel files are stored online, you can create connections between them with Sheetgo. This allows you to move and filter data between workbooks automatically. This enables you to:
- Combine multiple Excel workbooks into one spreadsheet
- Save time — no copy-pasting or email attachments
- Collect information from colleagues automatically
- Create an automated workflow that moves your data at regular intervals
- Generate automated reports and dashboards
Merging of Excel files: how to do it
Step 1: Install Sheetgo
Once inside the web application, click +Create workflow and then Connect to start merging your Excel files.
Step 2: Select your data source
Choose Multiple files as your data source.
Click +Select file(s) to find the Excel files in your cloud storage. After loading each file, click +Add another source file.
As you can see in the screenshot below, I have added 3 files to merge: Sales leads Region A, Sales leads Region B, and Sales leads Region C.
Choose the source tab
This is an important step. Make sure you select the tab (worksheet) in the source file that contains the data you want to merge. To consolidate data from multiple sheets into one, your source sheets should all have the same column structure. For example: the “Name” column should always be in the same place, so that data can be combined correctly.
Under Settings, you’ll find other features to help you transfer your data easily:
- Identify which row(s) contains your header
- Choose how you usually format your date
- (For Google Sheets only) transfer formatting
- Identify source: register where your data comes from
Step 3: Choose your destination file
Click Continue to go to the next step.
Choose which file you want to send the data to. Data from your source files will be merged, or consolidated, into this location. Select an existing Excel file from your Drive, or let Sheetgo automatically create a new one for you.
Please note that your source files will remain intact.
Finally, click Finish and save to merge all the Excel files into one worksheet. Sheetgo consolidates the 3 different Excel worksheets into a new one worksheet (“Sales Leads All Regions”).
Step 4: Automate the workflow
Your workflow has now been created. You can manually click to update the connections at any time, and fresh data will be pulled from the source sheets to the destination sheet.
To automate data transfer between the files, click Automate on the floating menu bar inside your workflow.
Choose how often you want updates: hourly, daily, weekly, or monthly.
Automated Excel merging
That’s how to merge and combine data from multiple Excel workbooks into a single file for automated reporting and analysis.
Are you looking for other ways to connect and organize your Excel files? Learn how to filter Excel from one workbook to the other automatically.