How to merge data from multiple Google Sheets
Spreadsheets are one of the most flexible, accessible and universal tools we have for managing data. And Google Sheets has pioneered the transition from single-user offline spreadsheets to multi-user online spreadsheets. We can now store spreadsheets in the cloud and share them with colleagues using access controls (Sheets’ share function to allow other people to view or edit the file). While this shift has made it a lot easier to share data in a team, many of us still spend a lot of time gathering and collating information from different files. If you’re a manager, you might need to collect data from colleagues or multiple departments. Plus it’s not always wise or practical to create one huge spreadsheet; if you’re a finance or HR administrator you’ll need to keep data separated for privacy reasons. In these cases, you may need to merge data from multiple Google Sheets into one central file.
Why merge Google Sheets into one?
- Collect data from different colleagues, departments or clients
- Create a master sheet for reporting
- Power an automated dashboard from multiple source files
- Eliminate copy-pasting
- Give team members autonomy to manage their own data in their own spreadsheet
- Keep data separated for enhanced security and access control
Save time and combine spreadsheet data automatically
Here we’ll show you how to use Sheetgo’s Consolidate function to merge data from multiple Google Sheets together into a single tab in another Google Sheet. In this example, we will use five sales-related spreadsheets.
- All Regions
We will consolidate data from the regional source files into the destination file. The screenshot below shows the sample sales data for four weeks across each region.
Sample sales data across all regions:
Prepare your source spreadsheets
For the consolidate function to work correctly, the following criteria must be met:
- The source spreadsheets must have the same columns. Since we’re consolidating multiple sheets into one, the columns in the source sheets must match. For example, the “Name” column should be in the same position in every sheet.
- Only the first
rowin the source sheets should be the header. Sheetgo assumes that the source sheets have standard format with the first row as the header. Data appears in row 2 and below.
How to merge Google Sheets automatically
Step 1: Start Sheetgo
If you don’t have the Sheetgo add-on for Google Sheets installed, just click the button below.
Open a new Google Sheet or the file you want to import data into.
In this example, we’re using our Sales – All Regions spreadsheet.
To start the add-on, go to Add-ons > Sheetgo > Start.
Sheetgo opens as a sidebar on the screen, as shown in the image below. Click on the green button + Import data.
Please note that, when using the Sheetgo add-on for Google Sheets, the destination file is always the file you are working in. So in this case it’s Sales – All Regions.
Step 2: Select your source files
Select the files that you want to consolidate (merge) into your destination sheet by clicking Select source.
- Sheetgo displays your most recently updated files by default. If you can’t see the file, click on the search icon at the top of the add-on to find the file in Google Drive.
- Select your first source file, and if the file contains multiple tabs, make sure you select the correct source tab. This is the tab/sheet containing the data you want to consolidate.
- Click add more and repeat the steps for your other source files.
- When all the source files and tabs are loaded onto the add-on, click Save connection.
Take a look at your destination spreadsheet. Sheetgo has created a new tab containing the combined data from your source files.
In this example, we have consolidated our regional sales spreadsheets (North, South, East and West) into the central All Regions file.
The consolidate connection is active
The Sheetgo connection is now merging data from multiple Google Sheets into one. Here’s our destination spreadsheet containing the imported data:
Automate data consolidation in Google Sheets
Your Sheetgo connection is now set up. To refresh the data in the destination sheet, just open the add-on and click update.
Save time and make sure you always have the latest data with automatic updates. Under Settings, schedule your consolidate connection to update as frequently as you like — from once an hour to once a month.
- Sheetgo can merge a maximum of 80 sheets. For efficiency, it may be better to consolidate from a folder. Talk to Sheetgo’s support team if you need help managing large volumes of data.
- You can merge a maximum of 5 million cells. Google Sheets currently has a limit of 5 million cells per sheet so you cannot exceed this limit while merging the sheets.
Merging Excel to Sheets
If you need to consolidate data from Excel files into Google Sheets, the process is exactly the same with Sheetgo. For more information on how to Import from Excel, check out this blog post.