Advertisement
  1. Business
  2. Spreadsheets
Business

How to Link Spreadsheets & Share Data in Google Sheets

by
Difficulty:IntermediateLength:MediumLanguages:
This post is part of a series called How to Use Google Sheets (Essential Tutorial Guide).
How to Protect Sheets & Lock Cells in Google Sheets
How to Quickly Collaborate on Edits in Google Sheets (Online)

Spreadsheets are one of the best ways to log and organize data. Frequently, I use them to organize projects or take notes on something new I'm learning. It's easy to use a spreadsheet like a blank canvas, and then order the data into a structured format later.


Google Sheets makes it easy to capture and organize that data, right inside of a web browser. It's a free spreadsheet tool you can launch just by opening a new tab.

As your spreadsheet library grows and expands, you may want to pull in data from other files. It helps when you can link up multiple spreadsheets so that you can use data from other workbooks. Google Sheets can help you do just that. Let's learn how to link spreadsheets in Google Sheets.

Link Google Sheets

Watch & Learn

Join me in the screencast below to walk through the technique to link Google Sheets. You'll learn more about linking between separate Sheets workbooks, within single files and grabbing data from external services.

 

This tutorial is going to cover how to make a Google spreadsheet link to another sheet. Let's walk through the guide to learn several ways to do that.

1. How to Link Within Sheets

Whether you're using a spreadsheet app like Excel, or a browser-based tool like Sheets, you're likely to build out files with many different tabs inside.

Typically, we talk about workbooks as being single "files" for spreadsheets. The individual tabs inside the workbook are called sheets. Workbooks typically have many different sheets inside of them. 

Maybe you use some tabs to capture your inputs, and other tabs to build out charts that analyze them. It's a good idea to keep these sheets somewhat separate for simplicity and organization's sake.

It's common that you need to link data between tabs (sheets) in a Google Sheets file. To do that, let's start writing a formula by simply typing an equals sign ("=") in a cell.

Let's say that you want to simply bring what's in one cell on another sheet over. With the formula still open, I'll switch tabs by clicking on it. Then, let's click on that cell to reference it as you can see below.

Linked to separate cell
In this example, I've linked a cell on the first sheet to a cell on the Inputs tab so that I'm always using the most up-to-date data.

Once you press Enter, Sheets will take you back to where you started the formula, and you'll see the data from the linked cell. Now, you're linked to the cell on the second sheet. If the source cell changes, so will the linked cell.

Of course, you can also apply functions to the cell reference. When you're referencing a numeric value, you could multiply it on the fly for example.

Calculate on the fly
In this case, I'm taking the input cell and multiplying it by 50% to adjust a cell reference on the fly.

One popular setup for workbooks is to use a single sheet for inputs or setting variables on a single tab and have all other tabs do the data transformations and analysis. I think this is a great setup to keep things organized and tidy.

2. How to Link to Other Sheets Files in Google Spreadsheets

What if you want to make two entirely separate files talk to each other, referencing data back and forth between them?

To link Google Sheets, we'll need to learn about the IMPORTRANGE function. This is the best way to pull in data from separate files in Sheets and use that data inside the active sheet.

It helps to have both spreadsheets open while you're working with IMPORTRANGE so that you can easily check what cells need to be brought into your spreadsheet.

To get started with this function, let's type in =IMPORTRANGE. Next, grab the URL for the Sheet that you want to pull data from, and paste it in quotation marks in the first part of the function.

Next, you'll need to add the name of the sheet followed by an exclamation point. Then, specify the cells that you want to pull the data from.

ImportRange Example Image
Using IMPORTRANGE, I'm using data from a separate file into the active Sheets file.

A finished function could look something like this:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1xrGsOD-yXuORqd8cFg21XOo3ZIw9QbSiNDcnSEatlPM/edit#gid=0", "Sheet1!A:A")

The first time that you create a Google Sheet link to another sheet, you might see a warning like this one that requires you to grant access between these two files.

Allow Access to a cell
The first time that you connect to an external Sheet, you'll need to click on Allow Access to connect the two sheets.

One option I'd recommend is to include entire columns when you're pulling data between Sheets. This will automatically bring over any new data that's added to the source spreadsheet.

This is an easy way to bring data together from multiple spreadsheets. Using the IMPORTRANGE function, there's no reason that you can't create systems of interlinked spreadsheets that work together.

3. How to Get Data from Google Services

One of my favorite things about Sheets is that you can insert data that's not even stored inside of a spreadsheet. Because Google's Sheets app is connected to other services that Google operates, you can get data that's stored online or in Google's services. With this step, you can skip the work of linking a Google Sheet link to another sheet

Basically, these functions are actually a method to skip putting data in a spreadsheet altogether. The idea is the same though; simply use these functions and grab data from other sources right inside of the active sheet. Let's take a look.

1. GOOGLEFINANCE

For finance professionals, the =GOOGLEFINANCE function is a game-changer. You can grab current and historical data from Google Finance.

In Excel, doing analysis on stocks involves finding a data source, downloading the data, cleaning it up so that it's useful, and then writing formulas on it. Sheets skips that work by automatically fetching the data from the Google Finance service.

Here's an example of how you can use the GOOGLEFINANCE function to grab the stock price over a period of time for Amazon's stock:

=GOOGLEFINANCE("AMZN","price","1/1/2018","3/15/2018","Daily")

Here are the results, right inside of a spreadsheet. Sheets generated an array of days and closing prices for the stock:

Google Finance function example
The GOOGLEFINANCE function can pull in data about equities like closing price easily.

I just scratched the surface of how the GOOGLEFINANCE function. Check out the full tutorial below for more ideas on how you can apply this function to grab up-to-date financial data.

2. GOOGLETRANSLATE

Another amazing function right inside of Sheets is the GOOGLETRANSLATE function, which you can use to translate to and from any language that Google Translate supports. It's far easier to perform those translations inside of a spreadsheet instead of jumping back and forth between tabs.

To use this function, try out my suggested syntax I show below:

=GOOGLETRANSLATE("je ne sais quoi","fr","en")

Google Translate Example
You can type a string of text to translate, right inside of the GOOGLETRANSLATE function.

This syntax will take the text that I've typed into quotes and translate it from French ("fr") to English ("en"). This part of the formula is optional (Sheets will automatically guess at what language to translate from), but makes it easy to remember other options for translation.

You could also use this function to translate data that's already typed into a cell. Instead of typing the text in quotation marks, use a cell reference, such as:

=GOOGLETRANSLATE(A2,"en","es")

This function would translate text in cell A2 from English to Spanish.

Google Translate Reference
You can also specify cells to translate using the GOOGLETRANSLATE function.

Either way that you apply the function, this function is like pulling data from another source because you're feeding data through the Google Translate service.

3. IMAGE

The image function can help you insert an image directly into a spreadsheet, using the IMAGE function.

First, make sure that you grab the URL address to the image. As a reminder, the image has to be hosted online in order to pull it directly into a Google Sheet.

Here's an example of using the function to pull in the current Google doodle that was posted:

=IMAGE("https://www.google.com/logos/doodles/2018/celebrating-george-peabody-4943849993535488-l.png")

Google Doodle Example
The IMAGE function can easily transform a URL into an image, placed right inside of your spreadsheet.

One helpful use for this feature is if the image will be updated, but the URL won't change. You could always pull the latest version of an image at the address.

Recap & Keep Learning

Don't stop here! There's so much more to learn when it comes to using Google Sheets to store and work with data. A spreadsheet is one of my favorite ways to log data in a structured way and analyze it to find meaning inside of it.

Here at Envato Tuts+, we've been building out a deep library to help you learn Google Sheets from front to back. Check out the tutorials below to learn more:

How do you like to interconnect your Google Sheets and share data between them? Let me know in the comments section below.

Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.