How To Merge Google Sheets (Manually & Automatically)

Google Sheets is a cloud-based platform that continues to make it easier to share and collaborate on spreadsheet files. Although we might think there’s no downside to this, having too many people editing in real-time can get confusing and slow down work. This is why most users end up keeping separate files for each user or at least keeping one as a backup. Whichever the reason, it’s likely that you’ll end up having to merge or combine data for better visualization and analysis.

In this article, you’ll learn how to merge data in Google Sheets manually and automatically within the same file, as well as across multiple worksheets. Alternatively, Layer can help you combine data within and between Google Sheets and even automate this process. All your data is protected in a master sheet which only updates with new data when you allow it.

How to merge Google Sheets manually?

If your Google Sheets contain data that is not too large or spread out across different spreadsheets, then these manual methods can be useful.

How to merge data in Google Sheets by pulling data from another tab?

If you need to merge data from different sheets within one spreadsheet, you can pull data by copying the cell reference in the target sheet.

1. 1. Click on the first empty cell after your dataset or table.

1. 2. Enter the cell reference that contains the data you’d like to pull. The reference includes the sheet name, an exclamation mark, and the first cell from the entire range you will be copying. Once you’ve entered it, press “Enter”.

Please note: If your sheet name contains space characters, wrap the name in single quotation marks.

1. 3. As shown below, Google Sheets returns the value corresponding to the referenced cell.

1. 4. To copy the rest of the data, select the cell from the bottom-right small blue square.

1. 5. Drag it horizontally to copy the data across the columns and vertically to fill in the data rows.

As simple as that! Note that this still requires manual input. As a result, it’s a method that’s only convenient if you don’t have a lot of data and would like to copy data within the same spreadsheet.

How to merge data in Google Sheets by copying the tabs?

The following is another standard manual way to copy the tabs within the same spreadsheet or different ones.

1. 1. Open the document containing the tabs you’d like to export. Right-click on the tab and select Copy to > Existing spreadsheet.

1. 2. Select the file to which you’d like to copy the tab data. Google offers three to browse the file in, “My Drive”, “Shared with me” and “Recent”. Click on the file and then press the “Select” blue button.

1. 3. Once copied, you should get this confirmation message “Sheet copied successfully”. Press “OK” to continue or click on the link “Open spreadsheet”. The last option will open the destination file with the tab you just copied.

1. 4. You should now see your tab appear in the destination spreadsheet. Here, “Copy of Company Info”.

How to combine data in Google Sheets using a formula?

Although this method still involves some manual work, it’s more suitable for larger datasets. Here, you’ll learn how to combine data using two different formulas: IMPORTRANGE and QUERY.

How to combine data in Google Sheets using QUERY?

You can use the QUERY formula to combine data from multiple sheets into one.

QUERY formula syntax

QUERY(data, query, [headers])

• data refers to the range of cells you wish to import.
• query is the action you will perform with the data. It should be typed enclosed in quotation marks and can be a reference to the cell range containing the data to pull.
• [headers] is an optional argument that can be used when you want to specify the number of header rows to include above the data. If left unspecified or set to “-1”, Google Sheets will guess the value based on the content.

1. 1. Open Google Sheets and enter “=QUERY” in the first empty cell below or to the right of the existing data.

1. 2. The first argument corresponds to the sheets you’ll be importing data from. Type the curly bracket “{“, sheet name, exclamation mark, and cell range. Separate sheets with a semicolon “;” to import vertically; to import horizontally, use a comma “,”. Then, press “Enter” to import.

Note: If your sheet name includes space characters, remember to wrap it in single quotation marks.

1. 3. You should now see your imported data from different sheets into one, as shown below.

How to combine data in Google Sheets using IMPORTRANGE?

If you need to pull data from different spreadsheets, you’ll need to use the IMPORTRANGE formula.

IMPORTRANGE formula syntax

=IMPORTRANGE(spreadsheet_url, range_string)

• spreadsheet_url is the spreadsheet you’ll pull data from. You’ll refer to it through the URL and place it between double quotation marks.
• range_string is the cell range that contains the data to import into the active sheet.

1. 1. Open the spreadsheet from which you'll pull the data. Select the URL link from the browser, up to the hashtag “#”.

1. 2. Open the spreadsheet where you’ll combine the data onto. Type in “=IMPORTRANGE” in any empty cell where you’d like to place the imported data.

1. 3. Enter the first argument for the IMPORTRANGE formula syntax. Don’t forget to place between double quotation marks and then enter a comma before the next argument.

1. 4. For the second argument, type in the name of the sheet and the exact range that you want to pull. You can do this by selecting the range of data, and checking the cell range reference in the cell range tab, as shown below. Here, “A1:E101”.

1. 5. Go back to the spreadsheet where you’re entering the formula. Type in the sheet name, exclamation mark, and cell range. Don’t forget to wrap it in double quotation marks. Once you finish, press “Enter”.

1. 6. If this is the first time you’re combining data from these two spreadsheets, then you will get a “#REF” error in the formula cell. Click on “Allow access”.

1. 7. Once access is enabled, you should see the imported values.

Note how IMPORTRANGE only imports the values, not the original formatting. This is something you’ll have to do manually. What’s more, for larger datasets, this process can take longer. Any data that is changed in the original spreadsheet will be updated automatically in the destination file.

Want to Boost Your Team's Productivity and Efficiency?

Transform the way your team collaborates with Confluence, a remote-friendly workspace designed to bring knowledge and collaboration together. Say goodbye to scattered information and disjointed communication, and embrace a platform that empowers your team to accomplish more, together.

Key Features and Benefits:

• Centralized Knowledge: Access your team’s collective wisdom with ease.
• Collaborative Workspace: Foster engagement with flexible project tools.
• Seamless Communication: Connect your entire organization effortlessly.
• Preserve Ideas: Capture insights without losing them in chats or notifications.
• Comprehensive Platform: Manage all content in one organized location.
• Open Teamwork: Empower employees to contribute, share, and grow.
• Superior Integrations: Sync with tools like Slack, Jira, Trello, and more.

Limited-Time Offer: Sign up for Confluence today and claim your forever-free plan, revolutionizing your team’s collaboration experience.

Conclusion

If you need to combine small amounts of data in Google Sheets, then using one of the manual processes outlined here should be enough. To speed up this process a bit more, you can use the Google Sheets QUERY function or the IMPORTRANGE function. This last formula is especially useful in case you’d like to merge data from different spreadsheets. However, these methods are still prone to error since there is still some manual work involved.

You should now feel comfortable merging and combining data in Google Sheets using built-in features as well as formulas that can be used in many other scenarios. Alternatively, we recommend using Layer to avoid using workaround solutions that are only applicable in specific cases.