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 by merging 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 Excel files: how to combine data automatically
Step 1: Install Sheetgo
Once inside the web application, click +Create workflow and then Connect to start merging your Excel files.
At the top of the screen, give your Untitled Workflow a name so you can identify it later.
Step 2: Select your data source
Choose Multiple files as your data source. Your source files are the spreadsheets containing the data you want to merge.
Click +Select file(s) to find the first Excel file in your cloud storage. Click Done.
Click +Add another source file and repeat the process.
When you have added all your source files, click Continue.
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.
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.
Step 3: 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.
Customize your connection
Under Settings you will 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
When combining multiple files into one, you may want to be able to identify which source file the data originated from. Under Settings enable Identify Source and Sheetgo will create an additional column in the destination spreadsheet identifying which file the data was imported from.
Step 4: Choose your destination file
Under Send data to, select your destination file.
Data from your source files will be merged, or consolidated, into this location each time the connection is updated. Note that your source files will remain unchanged.
You can send the data to an existing file or a new file. In both cases, Sheetgo will create a new tab in this destination file, containing the consolidated data.
- Choose a file from any of your cloud storage solutions.
- Optional: under New File Tab give the new tab a name of your choice. If you leave this blank, Sheetgo will name the tab/connection Sheetgo_SourceFileTab.
With this option, Sheetgo will create a new file for you automatically.
- Under Destination select which file type you want to send the data to (this can be a different format to your source files).
- Choose where you want the file to be saved under Change destination folder (this can be any cloud storage).
- Give the file a name in the New File Name box.
- Optional: give the new tab a name in the Tab Name box.
Here, I want Sheetgo to create a new Excel file, saved in Google Drive. I name the file “Sales Leads All Regions” and the tab “Merged leads”.
Click Finish and save to start the connection.
Check out your destination file: Sheetgo has consolidated the data from the source Excel worksheets into a new tab.
Step 5: Automate the workflow
Your workflow is now up and running. To pull fresh data from the source files into the destination tab, click Run on the floating workflow menu. Any changes to the data in the source files will appear in the destination, whilst leaving the source files intact.
To save time and ensure you’ve always got the latest data, automate data transfer between the files. Click Automate on the floating menu bar inside your workflow and choose how frequently you want updates: hourly, daily, weekly, or monthly.
Merging a lot of Excel files?
Sheetgo enables you to combine data from up to 80 different source files into one. To save time and automate another stage of your work, try consolidating from a folder. Under Source data, select An entire folder.
When you add a new file to the folder, it will be automatically included in the consolidate connection with the next update.
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.