Consolidate Excel files automatically

Written by Valentine Schelstraete

Jul 2, 2020

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

How to consolidate Excel files into one

Merging of Excel files

Imagine you’re an HR manager and you oversee the timesheets for a team of staff. Each employee keeps track of their hours worked and tasks completed in their own Excel file. To get a clear overview of all your employees’ timesheets, you want to merge these individual 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:

Once inside the web application, click +Create workflow > Connect.

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.

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.

Select a file and then click +Add another source file until all your source files are loaded.

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

Consolidate Excel data sources
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.

Consolidate Excel source data settings

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”).

Consolidate Excel data destination

Click Finish and save. Sheetgo will now consolidate, or merge, data from your source Excel files into the destination spreadsheet.

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.

Consolidate Excel automatic updates Sheetgo

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.

You may also like…