Consolidate Excel files automatically

How many different Excel workbooks do you open in a single day? If you’re a manager or administrator who needs to collect information from across the company, chances are you spent a fair amount of time gathering and collating 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 once your spreadsheets are stored online, you can use the co-authoring function to share files and collaborate with colleagues at the same time. But when you want to consolidate Excel files into one, there isn’t a simple inbuilt solution.

Instead of searching for files and manually copy-pasting information from one workbook to another, you can automate this process by creating connections between spreadsheets. Sheetgo is a no-code tool that enables you to consolidate Excel worksheets from multiple workbooks into one central file. This means you can

• Combine multiple Excel files into one
• Build a no-code, automated workflow that runs by itself
• Save time and eliminate manual work
• Pull live data from multiple sources into a central master sheet
• Share data with colleagues — without sharing your spreadsheet

Why use Sheetgo to consolidate Excel files

If you want to merge Excel files and combine data from multiple spreadsheets into a single worksheet, you might consider copy-pasting the data or using the consolidate feature.

However, none of these options seem to be a viable solution. While copy-pasting is neither practical nor scalable, the consolidate function in Excel is also not the best solution if you want to merge data from multiple spreadsheets.

The consolidate feature allows you to summarize data from multiple spreadsheets by utilizing one of the functions available: SUM, COUNT, AVERAGE etc. This doesn’t allow you to merge multiple worksheets by transferring all the data from the source files into a destination spreadsheet.

With Sheetgo, you can connect multiple spreadsheets and consolidate data automatically without copy-pasting or complex formulas. You will be able to create connections and automate data management with just a few clicks. 

For other ways to automate repetitive work in Excel, from VBA macros to Power Automate, see our guide to Excel automation.

How to consolidate Excel files into one

When you create Sheetgo connections, every time a team member edits their own spreadsheet, the data flows into your master sheet automatically. You get up-to-date, consolidated data, ready to generate automated dashboards or reports for management.

A consolidated Excel workflow is a time-saving, automated solution for many business processes including sales, finance, and project management. It enables you to gather data in one click, and keep the data flow running automatically. Plus connected spreadsheets mean lighter files, better access control, and more autonomy for team members. Here’s how to set it up:

Step 1: Use Sheetgo to consolidate the files

Sign up to Sheetgo by clicking the button below:

Step 2: Select multiple Excel files as your data source

Choose Multiple files as your data source. The source files are the spreadsheets you want to import and combine data from. Note that Sheetgo will pull data from your source files while leaving them intact. It won’t delete data from the original source files.

Click +Select file to find the Excel files in your cloud storage solution.

In this example, I am using my colleagues’ timesheets:

Select the source tab

Sheetgo sends data from one tab(sheet or worksheet) to another. If your source spreadsheets contain multiple tabs(sheets) it’s important that you select the correct source tab. Your source tabs should have the same structure so that Sheetgo can consolidate, or combine, the data correctly into your master sheet. For example, if you have a column called “Name” it should be in the same position in every source tab.

Step 3: Identify source (optional)

Go to Settings.

When you are combining data from multiple files, it’s useful to enable the Identify source feature so you can trace your data easily. Sheetgo creates an extra column to your destination file, to identify where the data came from. You can identify the data by Source location, File name, File creation date, or File update date.

In this example I have chosen File name. This shows me which employee’s timesheet each report originates from.

Step 4: Choose your destination file

Click Continue.

Now choose your destination file (your master sheet, or the file you want to merge your Excel sheets into). This can be an existing spreadsheet already stored in your cloud storage, or if you want to use a new spreadsheet, Sheetgo will create one for you automatically.

Here I will consolidate multiple Excel sheets into a new Excel file. I give this spreadsheet a name (“Team 1 Timesheets”) and also choose a name for the destination tab. This is the tab(sheet) where all the consolidated data will appear (“Consolidated Timesheets”).

Step 5: Automate the workflow

Last, but definitely not least, automate your workflow. This will ensure your destination file is automatically regularly, pulling the latest changes from your source files into the master sheet.

Go to the floating menu bar on your workflow and click Automate. Choose to update the connections hourly, daily, weekly, or monthly.

Automated data consolidation in Excel

That’s how you automatically consolidate data from multiple Excel workbooks into one — with a simple one-time setup, your automated workflow runs like clockwork.

If you’re interested in more ways to work smarter and more productively with Excel files, read how to append Excel and create a historical track of your data.

To automate Excel tasks inside Microsoft 365, such as getting data from a table or exporting it to SharePoint, see these Power Automate and Excel examples.