How many different Excel files do you open on a single day? If you’re an analyst or administrator who collects information from across the company, chances are you spend a fair amount of time gathering and combining data from multiple files.
Excel has powerful data storage and management capabilities. One workbook alone can hold millions of cells and hundreds of sheets, limited only by your computer’s memory. And if you upload your Excel files to OneDrive, you can use Excel for the web’s co-authoring function to share files and collaborate with colleagues.
But when you want to combine multiple Excel sheets or merge multiple Excel files into one, there is no simple inbuilt solution.
If you’re preparing a report, it’s time-consuming to search for the right information and compile a new spreadsheet manually. You might have to ask colleagues to send you their latest file versions before copy-pasting data from multiple sheets.
Not only is that tedious, but it’s also easy to make mistakes. And by the time you’ve finished, the data might already be out of date.
Instead of combining Excel sheets manually, you can create connections between your spreadsheets. This allows you to merge multiple Excel files into one automatically. The connections will pull data from several sheets into a central master sheet. Once you’ve set up the system, you can generate new reports on demand.
A simple way to consolidate Excel files
By connecting your Excel files, you can sync data between them. This means that when you combine multiple sheets into one, changes in each individual file will be updated in the central file automatically.
An automated file merging system has many advantages. It enables you to:
- Combine multiple Excel sheets into one workbook
- Save time — no more copy-pasting
- Collect information from colleagues automatically
- Merge data from different sheets in Excel
- Share data without sharing the same spreadsheet
- Get accurate data that’s always up-to-date
- Pull data directly into reports
- Feed dashboards with data from multiple Excel files
- Dedicate one file to one task
- Avoid large, heavy spreadsheets
- Ensure better traceability and data governance
How to merge multiple Excel files into one
Sheetgo is a no-code tool that enables you to push and pull data between worksheets in different spreadsheets automatically.
Here I’ll show you how to consolidate three sheets (in three separate Excel files) into one central spreadsheet for analysis.
Changes to the data in the source sheets will be synced to the central spreadsheet each time there’s an update.
Step 1: Install Sheetgo
Click on the blue button below to install Sheetgo and start connecting.
Sheetgo allows you to connect Excel files stored on any of the main cloud storage platforms, so you’ll see that you have the option to log in with your Microsoft, Google, or Dropbox account.
Inside the Sheetgo add-in in Excel click on the Sheetgo logo to start building your workflow.
Step 2: Select the Excel files you want to merge
- Under Select source data, choose the Multiple files option.
- Click +Select file(s) to find the first Excel file in your cloud storage.
Sheetgo connects online spreadsheets.
If you’re working with the desktop version of Excel, it’s easy to sync your local files to OneDrive or another cloud storage platform. Just follow the steps provided here:
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.
When you’ve selected the first file you want to merge, it appears inside Sheetgo.
Under File tab, make sure you select the correct source tab. This is the specific tab (worksheet) that contains the data you want to merge.
Here I’ll select the source tab called Sheet1.
Now click +Add another source file to select the next file you want to merge.
Select the file and the same tab.
As you can see below, I am consolidating data from three separate workbooks: Leads Region A, Leads Region B, and Leads Region C.
I’m combining data from the same tab in every file (Sheet1).
Check your source tabs are the same
To consolidate data from multiple Excel sheets into one, the source tab must have the same name in every file.
Each tab should also have the same column structure and layout so that Sheetgo can combine the data correctly.
When you consolidate multiple sheets into one, you may want to be able to see which source file the data originated from. To do this, enable the Identify source setting.
- Under Source data > Settings, enable Identify Source.
- Under Identify source method, choose your selected identifier.
Sheetgo will create an additional column in the destination spreadsheet identifying which file the data was imported from.
Here I’ll select File name as the identifier.
Repeat the above steps until you’ve added all the Excel files you want to merge.
When they are all visible on the Sheetgo screen, click the Done editing button.
Step 3: Choose your destination file
The destination file will be the Excel file your working in, if you’re working from the Excel desktop app. If you want to send it to a new file you have to do it via the Web app.
The destination file is the spreadsheet where your source data will be consolidated or you can choose to send it to a new file once the connection is made.
Note that when Sheetgo transfers your data from sheet to sheet, your source file(s) remain unchanged.
Sheetgo will create a new tab in the destination file, containing the consolidated data.
I’ll name the new tab Consolidated funnel.
Click Finish and save and Sheetgo will create the connection.
Here I’ll take a look at my new destination file: Merge Excel File.
As you can see, Sheetgo has consolidated the data from my source files into a new tab in the destination sheet.
In Column J, Sheetgo has added a Column (called Source) indicating which file the data originated from.
Step 4: Update the connections
Now that your files are connected with Sheetgo, you can update the workflow at any time.
- Open the Sheetgo add-in and select the workflow from the list on the left.
- Click Run on the menu bar.
This will refresh all the connections, updating the destination sheet with the latest data from your source sheets.
Step 5: Automate the workflow
Your Excel file merging workflow is now up and running.
To save time and ensure you’ve always got the latest data, you can automate the entire system.
- Click Automate on the Sheetgo menu bar inside your workflow
- Choose how frequently you want updates: from once an hour, to once a month.
- Select which time of day, or day(s) of the week you want it to be updated.
Want to combine a large number of Excel sheets?
Sheetgo enables you to combine data from up to 80 different source files into one. Instead of selecting each file manually, you can save time and automate another stage of your work by consolidating from a folder.
To combine data from multiple Excel files in a folder,
- Open Sheetgo and create a new workflow > Connect.
- Under Source data, select An entire folder.
As before, each source file must have the same column format and source tab.
Whenever 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 combine and consolidate data from multiple Excel workbooks into a single file for automated reporting and analysis.
Looking for a way to transfer specific data from a worksheet? Learn how to filter Excel from one workbook to the other automatically.
Your content goes here. Edit or remove this text inline or in the module Content settings. You can also style every aspect of this content in the module Design settings and even apply custom CSS to this text in the module Advanced settings.