Don’t forget to share this post

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 keep 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.
How To Merge Google Sheets Manually Automatically Blank cell
How To Merge Google Sheets (Manually & Automatically) - Blank cell
  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”.
How To Merge Google Sheets Manually Automatically Cell reference
How To Merge Google Sheets (Manually & Automatically) - Cell reference

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.
How To Merge Google Sheets Manually Automatically References cell value
How To Merge Google Sheets (Manually & Automatically) - References cell value
  1. 4. To copy the rest of the data, select the cell from the bottom-right small blue square.
How To Merge Google Sheets Manually Automatically Grab small blue square
How To Merge Google Sheets (Manually & Automatically) - Grab small blue square
  1. 5. Drag it horizontally to copy the data across the columns and vertically to fill in the data rows.
How To Merge Google Sheets Manually Automatically Drag square to fill in data
How To Merge Google Sheets (Manually & Automatically) - Drag square to fill in data

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 Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE
How To Share Only One Tab in Google Sheets

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.
How To Merge Google Sheets Manually Automatically Existing spreadsheet
How To Merge Google Sheets (Manually & Automatically) - 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.
How To Merge Google Sheets Manually Automatically Select file
How To Merge Google Sheets (Manually & Automatically) - Select file
  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.
How To Merge Google Sheets Manually Automatically Confirmation message
How To Merge Google Sheets (Manually & Automatically) - Confirmation message
  1. 4. You should now see your tab appear in the destination spreadsheet. Here, “Copy of Company Info”.
How To Merge Google Sheets Manually Automatically Open spreadsheet
How To Merge Google Sheets (Manually & Automatically) - Open spreadsheet

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.
How To Merge Google Sheets Manually Automatically Type in formula
How To Merge Google Sheets (Manually & Automatically) - Type in formula
  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.
How To Merge Google Sheets Manually Automatically Import horizontally
How To Merge Google Sheets (Manually & Automatically) - Import horizontally

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 Merge Google Sheets Manually Automatically Imported data
How To Merge Google Sheets (Manually & Automatically) - Imported data
How To Use IMPORTRANGE Function In Google Sheets
How To Use IMPORTRANGE Function In Google Sheets?

Google Sheets allows you to import and link a specific range of cells from another spreadsheet. Here's how to use the IMPORTRANGE function in Google Sheets

READ MORE

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 “#”.
How To Merge Google Sheets Manually Automatically Copy URL
How To Merge Google Sheets (Manually & Automatically) - Copy URL
  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.
How To Merge Google Sheets Manually Automatically Enter formula
How To Merge Google Sheets (Manually & Automatically) - Enter formula
  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.
How To Merge Google Sheets Manually Automatically Enter URL
How To Merge Google Sheets (Manually & Automatically) - Enter URL
  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”.
How To Merge Google Sheets Manually Automatically Cell range reference
How To Merge Google Sheets (Manually & Automatically) - Cell range reference
  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”.
How To Merge Google Sheets Manually Automatically Press Enter
How To Merge Google Sheets (Manually & Automatically) - 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”.
How To Merge Google Sheets Manually Automatically Allow access
How To Merge Google Sheets (Manually & Automatically) - Allow access
  1. 7. Once access is enabled, you should see the imported values.
How To Merge Google Sheets Manually Automatically Imported values
How To Merge Google Sheets (Manually & Automatically) - 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.

How to merge Google Sheets automatically?

Layer is an add-on that equips finance teams with the tools to increase efficiency and data quality in their FP&A processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Sign up today and get free access to Layer, including all the paid features, so you can start managing, automating, and scaling your FP&A processes on top of Google Sheets!

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.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Jun 23 2022, Updated Sep 23 2022