Are you spending too much time and energy linking data between multiple Google Sheets? Are you in need of a quick way to link two Google Sheets? Well, you’re in luck. We are here to share several solutions with you.
In this guide, we will cover multiple methods that will allow you to merge your data between multiple Google Sheets, saving you time and resources. By the end of this guide, you will be equipped with proven techniques to seamlessly and efficiently combine your data and Google Sheets.
Why Link Data Between Multiple Google Sheets?
If you don’t link your Sheets, you’ll find yourself manually gathering and consolidating data from separate files and sheets. Handling data across multiple Google Sheets can be time-consuming and resource-intensive, hindering you and your team from working with optimal efficiency.
Without linking your multiple Sheets, you’ll find yourself copying and pasting thousands of lines and columns of data, which just isn’t enough. Plus, if your source data changes, you’ll have to manually update your data sets and reports by going through multiple tabs and sheets, which is as easy as finding a needle in a haystack.
Files like these can be difficult to navigate, and it can take ages for someone to find the data they’re looking for. Manual processing of large volumes of data can also lead to errors and can cause you to work with inaccurate information.
That’s why it’s crucial to link your multiple Sheets. It helps you develop an overview of your consolidated information, allowing you to easily visualize and manage your data while avoiding the tedious and time-consuming process of manually copying and pasting data from different Google Sheets.
TL;DR: Coefficient Simplifies and Automates Data Linking Between Multiple Google Sheets
Coefficient makes linking multiple data in Google Sheets much easier, simpler, and even automatic.
With Coefficient, you can choose your Google Sheets data source file, add filters to refine the information you want to extract, and import the data with just a few clicks.
Updating your linked Sheets is also a breeze with Coefficient, as it allows you to set up automatic refresh schedules. You won’t need to manually update every time your linked source sheet changes or receives new information.
Data Setup
For our example, we will be using two Google Sheets. We’ll have a spreadsheet called “Sales Data” which contains two sheets:
- Enterprise Subscriptions: This includes a list of companies and their subscription numbers.
- Subscription Details: This contains a list of subscription numbers, their remaining validity, and the amount still due.
The second sheet, “User Subscriptions,” is in a completely separate spreadsheet. It contains a list of users and their corresponding enterprise subscription number.
Importing Data from Another Sheet in the Same Google Sheets File
The steps below describe different ways you can seamlessly move data from one sheet to another. You can also refer to our video tutorial for a step-by-step guide.
Cell-by-Cell Reference
Using a cell-by-cell reference to extract data from another sheet within the same spreadsheet is quite simple.
In the first cell where you want the data to appear, type “=”.
Then, you can either type the sheet name in single quotes, followed by “!” and the cell number, like this:
='Enterprise Subscriptions'!A2Or you can click on the sheet, then on the cell. You should see the floating formula box that shows you the current formula.
Drag this cell down and to the right to include the desired cells from your data set.
While the data set may look like the original, any changes made to the data within that range will appear in the referencing sheet.
Note: The cell numbers don’t need to match between the two sheets. You could import cell A1 from the Enterprise Subscriptions sheet into cell D7 of the working sheet by placing the same formula in D7 instead of A1, as shown in the example.
Range Reference
You can extract an entire column or specific range without performing the work in the previous step using this formula:
={'Subscription Details'!B2:B52}What’s interesting about this approach is that the connection is governed by the cell where the formula is entered, but the other values will still update.
VLOOKUP Function
The VLOOKUP function is a valuable tool for matching data between two sheets when the data is not in the same order or is in a one-to-many relationship.
To make this work, we’ll go into our Enterprise Subscriptions sheet and add a column for Remaining Duration and Remaining Value, similar to what we have in the Subscription Details sheet.
First, click on C2, the first cell under our new Remaining Duration column, and enter a formula that looks something like this:
=VLOOKUP($A2,'Subscription Details'!$A$2:$B$52,2)The VLOOKUP function requires three parts:
- The cell we want to look up (orange).
- The range of cells where we expect to find the value (purple).
- The range of cells that contain the value we want to return (blue).
We used the same syntax as before to select cells in the other sheet, except that now we did it with ranges. Specify the use of “$” to lock certain cells.
Also, as we want to find the Remaining Value data and the same Subscription Number, add a “$” to lock that column.
Drag this value down and the rest of the future cells, so there’s no need to use it on the line number.
Lock the columns and rows of the search range (purple) as they won’t change per column or per row.
Apply the “$” to the row number of the results range (blue) if you don’t want the top and bottom of the range to change. This allows you to get the Remaining Value without rewriting the formula (or any other column you might have after it).
Here’s what we have:
You can drag this formula down and to the right:
There you go! You’ve linked the data from both sheets together. This time, you can rearrange things in the search sheet (Subscription Details) without affecting the data in the Enterprise Subscriptions sheet. Data updates will still be reflected, including non-matching data.
However, the VLOOKUP function is not perfect. If you change the row 52 for subscription 1060 instead of 1050, we still get the result for 1050 because VLOOKUP doesn’t give exact matches. It gives the closest match, which in this case would be the value for 1050:
The advantage of using VLOOKUP is that the search and return ranges don’t need to be in the same rows or the same size. The downside is that you’re likely to get an approximate match.
That’s where HLOOKUP and VLOOKUP come in handy.
HLOOKUP/VLOOKUP Functions
HLOOKUP and VLOOKUP have similar functions but search for values in different ways.
To simplify, VLOOKUP searches for values in a column (vertical), while HLOOKUP does the same with values in a row (horizontal).
Using our original data set, you can replace the VLOOKUP function using this formula:
The formula looks a bit different from the VLOOKUP function.
=VLOOKUP($A2,'Subscription Details'!$A$2:$C$52,2,FALSE)The VLOOKUP function requires three parts and includes an optional component:
- The cell we want to look up (orange).
- The full range (including the columns we want to look up), the values we want to return, and everything in between (purple).
- The index of the column from which we want to return the values, starting from 1 (the searched column), 2 (the next column to the right), etc.
- (Optional) Google describes this fourth variable as indicating whether the search range is sorted. However, Microsoft’s description refers to whether you want an approximate match. If you don’t enter this value, VLOOKUP and HLOOKUP will assume the value is TRUE.
When TRUE, Google Sheets assumes that your data set is sorted. If it finds a value greater than your search cell, it will return the value just before it. This can mean that if your values are not sorted, Google Sheets may return the wrong answers.
When FALSE, the program assumes that values may be unsorted and that it may encounter a value greater than the search value before encountering the search value. It continues searching until it finds an exact match, otherwise, the cell will return an error.
Specify “$” to lock your ranges. Lock the column letter (orange), but leave the row number alone so we can drag it down and to the right to get the second result.
Lock the column letters and the row numbers of the search range to ensure capturing all the data.
You’ll notice that if you copied these values all the way to the right, you won’t see any changes.
This is a peculiarity of VLOOKUP, and since the function relies on a single range and a column index, you can’t simply drag your data. You’ll have to update your column index.
We entered a value of 1060 earlier, and because we need exact matches, VLOOKUP and HLOOKUP display an error (unlike VLOOKUP, where we get an approximate value) as it’s not in the lookup table.
Linking Data to Another Google Sheet
Now that you know how to combine multiple Sheets by linking data within the same Sheet, let’s learn how to link data between two Google Sheets.
Use the IMPORTDATA function, which looks like this:
=IMPORTDATA("https://docs.google.com/spreadsheets/d/1K2pyGYhTkMrvYqF3JKL4KKjqUzgWYOCcd1-fedLEMiA/edit","UserSubscriptions!A1:C10001")The formula requires two variables:
- The URL of the spreadsheet from which you want to extract data. Copy this from the address bar of the sheet you’re extracting data from and wrap it in quotes.
- The name of the sheet and range (also wrapped in quotes) using a syntax similar to what we saw in the cell-by-cell reference section. You’ll likely see a reference error, so you’ll need to grant access to the current sheet.
Once you click Allow access (assuming you’re the owner of both sheets or have edit access to both), the data will be extracted into the sheet:
The linked sheet’s changes will finally be updated (not instantly, but pretty quickly).
Importing Data from Another Google Sheet with Coefficient
Let’s go through how Coefficient can assist you in combining multiple Sheets into one using a more streamlined and user-friendly process.
Open the Google Sheets Add-ons menu, click on Coefficient: Salesforce, HubSpot Data Connector, then click Launch.
Click on Import Data in the Coefficient pane.
Next, select Google Sheet.
Choose any Google Sheet you want from your Google Drive or other shared documents.
For this example, let’s fetch the same list of user subscriptions we used in the previous steps and click Select.
Coefficient takes a few seconds to access the data and read column information. You’ll then see an import panel and the ability to filter data during the import process.
For this example, add a filter for subscription numbers greater than 1025. You could use this to filter out inactive subscriptions or those with more than a certain number of users. Doing this with Coefficient is faster and simpler than the manual process in Google Sheets.
Once you’re done, click Import.
There you have it. Coefficient imports your data almost instantly, and you don’t even have to deal with functions and formulas throughout the process.
You might be wondering now, “How can I make the references between Google Sheets automatically update?”
Coefficient has got you covered because it allows you to automatically update that data on a schedule, so you won’t have to painstakingly do it manually.
The IMPORTDATA solution allows you to import entire columns at once, but you have to specify bounds, or it will disrupt your data set if your data moves too much.
Additionally, IMPORTDATA doesn’t have a built-in way to filter data, so you’ll have to do that separately.
Coefficient addresses both these issues by integrating filtering into the import process, including importing all filled rows or columns seamlessly.
Another advantage of using Coefficient is when you want to generate reports for your presentations and don’t want your data to be updated in real-time.
Your data is automatically updated when using the IMPORTDATA function, and it happens as soon as the data in the source sheet changes.
This means that if you’re preparing a presentation from a live document, you’ll either have to make a copy or ask the owner to stop updating the file while you finish your task.
You can already imagine the confusion and disruption that can occur when multiple people are working on the same document at the same time.
However, Coefficient can sync on-demand, saving you from these challenges. You can perform a final sync, then prepare your presentation materials without the source data changing or linked tables being overwritten and damaging your Sheet in the process.
Conclusion
The methods presented in this guide streamline the process of linking data between multiple Google Sheets.
By linking multiple Google Sheets together and allowing files to interact automatically, you can increase your productivity and data accuracy.
With the amount of stress, frustration, and human resources you’ll save by automating the process of linking Google Sheets, you can focus on better managing your business.
Try Coefficient for free today!
