Linking Data Between Multiple Google Sheets: Ultimate Guide
does linking data between multiple google sheets take too much time and energy?
The good news is that we are here to share several solutions with you.
we will cover several methods that will allow you to merge your data between multiple google sheets so you don’t need to spend a portion of your time and resources doing this process manually.
By the time you finish reading this guide, you’ll be equipped with some proven techniques for combining your data and Google Sheets smoothly and efficiently.
why link data between multiple google sheets?
If you don’t link your spreadsheets, you manually collect and consolidate data from separate sheets and files.
managing data across multiple google sheets can eat up tons of your time and resources, keeping you and your teams from working at optimal efficiency.
Without linking your multiple spreadsheets, you’ll end up copying and pasting thousands of rows and columns of data, which just won’t cut it.
Also, if your source data changes, you’ll need to manually update your datasets 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 years before someone can find the data they’re looking for. manual processing of volumes of data can also lead to errors, causing you to work with inaccurate information.
That’s why it’s crucial to link your multiple spreadsheets to help you develop a master view of your consolidated data. This allows you to easily view and manage your data and saves you the laborious and time-consuming process of manually copying and pasting data from multiple Google Sheets.
tl;dr: The coefficient simplifies and automates data linking between multiple spreadsheets
coefficient makes linking data from multiple google sheets much easier, more direct, not to mention automatic.
with coefficient, you can choose the data source file from google sheets, add filters to refine the information you want to extract, and import the data with a few clicks.
updating your linked spreadsheets is also very easy, as the coefficient allows you to set automatic update schedules.
You won’t need to manually update every time your linked source sheet changes or gets new information.
configuring the data
For our example, we will use two spreadsheets from google sheets. we will have a spreadsheet called sales data that contains two sheets:
- corporate subscriptions. this includes a list of companies and their subscription numbers.
- subscription details. it contains a list of subscription numbers and their remaining terms and amount still owed.
The second, user subscriptions, is in a completely separate spreadsheet. contains a list of users and their corresponding corporate subscription numbers.
import data from another sheet into the same google sheets spreadsheet
The steps below describe the ways you can move data from one sheet to another seamlessly.
cell by cell reference
Using the cell-by-cell reference to get data from another sheet in the same spreadsheet is pretty straightforward.
In the first cell where you want the data to appear, type =.
then, you can enclose the sheet name in single quotes, followed by ! and the cell number, like so:
=‘corporate subscriptions’!a2
or you can click on the sheet and then on the cell. you should see the floating formula box telling you the current formula.
Drag that cell down and to the right to include your desired dataset cells in the row.
While the dataset might look similar to the original data, changes to the data within that range will appear in the sheet that looks up to it.
note: cell numbers do not have to match between two sheets. you could import cell a1 from the corporate subscriptions into cell d7 of the worksheet by putting the same formula in d7 instead of a1 as we did in the example:
Range Reference
You can extract an entire column or a specific range without doing all the work in the previous step using this formula:
={‘subscription details’!b2:b52}
The interesting thing about this approach is that the connection is governed by the cell in which the formula is entered, but the other values are still updated.
search function
The lookup function is an invaluable tool for comparing data between two sheets where the data may not be in the same order or in a one-to-many relationship.
For this to work, you must have a field that you can use as a key (unique for each record you’re searching for) to get perfect results using the search.
To do this, we’ll go to our corporate subscriptions sheet and add a column for the remaining term and remaining value, similar to what we have on the subscription details sheet.
First, click C2, the first cell under our new Remaining Term column, and enter a formula that looks something like this:
=search($a2,‘subscription details’!$a$2:$a$52 ,‘subscription details’!b$2:b$52)
the search function requires three parts:
- the cell we want to search for (orange)
- the range of cells in which we expect to find the value (purple)
- the range of cells that contain the value that we want to return (blue)
We’ve used the same syntax above to select cells on the other sheet, except now we’ve done it with ranges. specify the use of $ to lock certain cells.
also, since we want to find the remaining value data and the same subscription number, add a $ to lock that column.
drag this value down through the rest of the cells and all future rows, so that it doesn’t need to be used in the row number.
use the $ for the search range columns and rows (purple) as they will not change by column or row.
apply the $ for the row number in the result 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 columns you might have afterwards).
this is what we have:
You can drag that formula down and to the right:
Voila! You’ve joined the data from both those sheets together. Only this time, you can reorder things in the looked-up sheet (Subscription Details) without affecting the data in the Corporate Subscriptions sheet. Updates to data will still reflect, including the data that doesn’t match.
however, the search function is not perfect. if you change row 52 to subscription 1060 instead of 1050, we still get the result of 1050, because the search doesn’t return exact matches.
returns the closest match, which in this case would be the value of 1050:
The advantage of using lookup is that the lookup and return ranges don’t have to be in the same rows or the same size. the catch is that you’re likely to only get an approximate match.
this is where hlookup and vlookup come in handy.
vsearch/hsearch function
vlookup and hlookup have similar functions, but find values in different ways.
In a nutshell, vlookup looks for values within a column (vertical), and hlookup does the same for values within a row (horizontal).
Using our original dataset, you can override the search function using this formula:
the formula looks a bit different from the search 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 search for (orange)
- the entire range (including the columns we want to search for), the values we want to return, and everything else (purple )
- the index of the column we want to return values from, starting with 1 (the lookup column, 2 (the next column to the right), etc.
- (optional) google describes this fourth variable like if the lookup range is sorted. however microsoft’s description is about if you want an approximate match. if you don’t enter this value then vlookup and hlookup will assume the value is true.
- when true, google sheets will assume your dataset is sorted. if it finds a value greater than its lookup cell, it will return the value just before. this may mean that if your values are not in order, google sheets may return wrong answers.
- when false, the program will assume that the values may be out of order and might find a value greater than the lookup value before finding the lookup value. it needs to keep searching until it finds an exact match, and the cell will return an error if it doesn’t find an exact match.
specify your $ 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 and get the second result.
block the column letters and row numbers of the search range, so you know you’re fetching all the data.
You’ll notice that if you’ve copied these values directly, you won’t see any changes.
is a quirk of vlookup, and since the function is based on a single range and column index, you can’t just scroll your data. you will need to update the index of your column.
We entered a 1060 value earlier, and because we require exact matches, VLOOKUP and HLOOKUP show an error (unlike LOOKUP, where we get an approximate value) since it isn’t in the looked-up table.
Connecting to data in another Google Sheet
Now that you know how to combine multiple spreadsheets into one by linking data within the same sheet, let’s learn how to link data between two Google Sheets.
use the importrange function, which looks like this:
=import range(“https://docs.google.com/spreadsheets/d/1k2pygyhtkmrvyqf3jkl4kkjquzgwyoccd1-fedlemia/edit”, “user subscriptions!a1:c10001”)
the formula requires two variables:
- the url of the spreadsheet you want to get data from. copy it from the address bar of the sheet you are extracting data from and enclose it in quotes.
- the name of the sheet and the range (also in quotes) using a syntax similar to the one 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 own both sheets or have edit access to both), the data is pulled into the sheet:
changes in the linked sheet are eventually updated (not instantly, but almost).
importing data from another google sheet with coefficient
Let’s go over how the ratio can help you combine multiple spreadsheets into one using a simpler, more streamlined process.
open the google sheets add-ons menu, click coefficient:salesforce, hubspot data connector, then launch.
Click Import Data on the Coefficient pane.
then select the google sheet.
choose any google sheets you want from your google drive or other documents you have shared access to.
For this example, let’s open 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 the column information. You’ll then see an import panel and the option to filter the data as it comes in.
For this example, add a filter for subscription numbers greater than 1025. You can use this to filter inactive subscriptions or those with more than a certain number of users. doing this with the 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 need to deal with functions and formulas throughout the process.
perhaps now you are wondering, “how can I make google spreadsheets automatically update a reference to another sheet?”
coefficient has you covered, as it allows you to automatically update that data on a schedule, so you don’t have to do it laboriously and manually.
The IMPORTRANGE solution allows you to import entire columns at once, but you’ll need to specify bounds for it or it will disrupt your dataset if your data moves too much.
Also, importrange doesn’t offer a built-in way to filter data, so you’ll need to do it separately.
coefficient takes care of both of these issues by incorporating the filter into the import, including importing populated rows or columns seamlessly.
Another benefit of using the ratio is when you want to generate reports for your presentations and you don’t want your data to update in real time.
Your data is updated automatically when you use the importrange function, and it happens as soon as the source spreadsheet data is modified.
This means that if you’re preparing a presentation from an active document, you’ll need to create a copy or the owner will need to stop updating the file while the task completes.
You can already guess the confusion and interruption that can occur when many people work on the same document at the same time.
however, the coefficient can be synced on demand, saving you from these challenges. you can do a final sync and then prepare your presentation materials without the source data changing or the linked tables being rewritten and blowing up your spreadsheet in the process.
conclusion
The methods presented in this guide help you simplify the process of linking spreadsheet data between multiple Google Sheets.
as you link multiple google sheets, allowing files to interact with each other automatically, you can increase your productivity and the accuracy of your data.
With the amount of stress, frustration, and labor expense you’ll eliminate by automating the process of linking your sheets, you can focus on running your business better.
try coefficient for free today!
