How To Link Data Between Multiple Spreadsheets

You are viewing: How to Link Data Between Multiple Spreadsheets At HTTLEN: Share Good Articles

There comes a time in every Google Sheets user’s life when they need to reference a certain range of data from another sheet, or even from a spreadsheet, to create a combined master view of both. This will allow you to consolidate information from multiple worksheets into one.

Another common case may be the requirement of a backup spreadsheet that would be copying the values and format from the source file, but not the formulas. Some of the users may also want their master document to update automatically, on a set schedule.

So, if you are having difficulty finding the solution to the above tasks, keep reading this article. You’ll find tips on how to link data from other sheets and spreadsheets, as well as discover alternative ways to do it. In the end, I’ll provide a full comparison of the approaches mentioned so you can evaluate and choose.

Referencing data from other sheets or tabs: what are the options?

There are several cases and ways to reference data in Google Sheets. You can reference another sheet in Google Sheets, a cell or a range of cells, as well as columns and rows. Also, you may need to import data from one sheet/spreadsheet to another based on certain criteria or even combine data from multiple sheets into a single view.

Google Sheets offers some native options for data referencing, including the IMPORTRANGE function. Note, however, that

Google Sheets’ native functions and options only allow you to reference the data, not import it.

Yes, Google Sheets does not provide a function that actually imports data from one sheet/spreadsheet to another, although the name of the IMPORTRANGE function suggests otherwise. They only reference a specific range, i.e. if the data in your source sheet is not available, you won’t have access to it in your destination sheet. This is inconvenient. So, if you need to import data (range of cells, columns, or rows) from one sheet to another, you’ll need to go with a third-party solution, either a web app or a Google Sheets plugin. Or you can just copy data from another sheet into Google Sheets, but it’s more for newbies 🙂

Next, we cover native Google Sheets options for referencing data and third-party tools to import data between Google Sheets. . Let’s start with the native ones.

If you’re interested in Excel spreadsheets, head over to our blog post on how to link sheets in Excel.

# 1 – How to link cells in Google Sheets

How to link cells from one sheet to another tab in Google Sheets

Use the instructions below to link cells in Sheets Google Spreadsheets:

Open a sheet in Google Sheets.
Place the cursor in the cell where you want the imported data to appear.
Use one of the formulas below:

=Sheet1!A1

where Sheet1 is the exact name of the sheet being referenced, followed by of an exclamation point and A1 is a specific cell that you want to import data from.

=’Sheet_1′!A1

If the cell name the sheet includes spaces or other characters such as ):;” |-_*& etc., you need to put the name in single quotes.

In my case, let’s reference a cell B4 on the sheet called student list.

The out-of-the-box formula for referencing another sheet in Google Sheets will look like this:

=’student list’!B4

Note: if you want to link a range of cells from one sheet to another, simply place your cursor in the cell of your data destination worksheet that already contains one of the formulas mentioned above (=’Sheet_1′!A1 or =Sheet1 !A1) . Then drag it in the direction of your desired range. For example, if you drag it down, the data in these cells will automatically display in your spreadsheet. The same can be done in any other possible direction of your current document.

How to link a cell in the current sheet to another tab in Google Sheets

Follow this guide to reference data from the current sheet and others:

Open a sheet in Google Sheets.
Place the cursor in the cell where you want the data to appear reference.
Use one of the formulas below:

To link a range of cells on the current sheet, use the following formula:

= {cellrange}

Where cellrange is the cellrange of your current active sheet. Use square brackets around this argument.

Use the following formula to link to another tab in Google Sheets:

={Sheet1!cell-range}

Where Sheet1 is the name of your sheet reference and cell range is a specific range of cells from which you want to import data. Use square brackets around this argument.

Note: don’t forget to enclose the sheet name in single quotes if it includes spaces or other characters like ):;”|-_ *&, etc

This is what it looks like in my example:

#2 – How to link columns in Google Sheets

Basically, to link columns in Sheets Google Calculation, you simply need to select a range of cells that make up a column or a few columns and reference them as described above. However, there is a slightly better way to do it.

How to link a sheet column to another tab in Google Sheets

To link a sheet column or columns to another tab in Google Sheets, use the following formula:

={Sheet1!columns}

Where Sheet1 is the name of your sheet and referenced columns is a range specifying that will extract the data from A. Use square brackets for this argument.

In my case, the out-of-the-box formula to reference another sheet in Google Sheets will look like this

={‘student list’!R:D}

#3 – Linking Rows in Google Sheets

To link a row or rows from a sheet to another tab in Google Sheets, use the following formula:

={Sheet1 !rows}

where Sheet1 – is the name of the sheet being referenced and rows is a range specifying the rows being referred to. Use square brackets around this argument.

In my case, the out-of-the-box formula to reference another sheet in Google Sheets will look like this

={‘student list’!2 :5}

#4 – Linking Sheets in Google Sheets

You can use the column or row reference formulas presented above to link cell ranges between different sheets of the same sheet calculation. For more advanced tasks, see the following use cases.

Import data from one Google sheet to another based on criteria

Suppose you want to filter your dataset by specific criteria and import the filtered values on another sheet. You can do this using the FILTER function that was introduced in the previous example. This is the syntax:

=FILTER(data_set,criterium1, criteria2,…)

data_set – a range of cells to filter on.
criteria: The criteria to filter the dataset.

As an example, let’s filter users by country, Australia, and import the results into another sheet.

Import data from one Google sheet to another based in criteria

This is what our formula will look like:

=filter(‘Source Data’!A2:A,’Source Data’!C2:C=”Australia ” )

Read about the FILTER feature in Google Sheets to discover more filtering options.

How to import data from multiple sheets into one column

Let’s go over an example when one needs to link data from multiple columns on different sheets into one.

In my example, I have three different tabs with sales data: Sales 1, Sales 2 and Sales 3. My task is to collect all the customer names in the sheet called All Customers.

To do this, I’ll use this formula:

={ “All customers”; FILTER(‘Sales 2’!C2:C, LEN(‘Sales 2’!C2:C) > 0); FILTER(‘Sales 1’!C2:C, LEN(‘Sales 1’!C2:C) > 0); FILTER(‘Sales 3’!C2:C, LEN(‘Sales 3’!C2:C) > 0) }

Where:

“All customers” – is a name stack of my column,
FILTER(‘Sales 1’!C2:C, LEN(‘Sales 1’!C2:C)> 0) – this expression means that I take all the data from the column C of “Sales 1”, excluding values that are equal to or less than 0.

As a result, I get the names of all my customers from three different sheets gathered in one column.

One of the advantages of this approach is that I can change the names of my data source sheets (where I get the data from), and they will automatically update in the formula! !

See how it works:

At the same time, there’s a better option to consolidate your data from multiple Google spreadsheets into a single master view; we cover that in this section.

#5 – How to reference another spreadsheet/workbook in Google Sheets via IMPORTRANGE

The options presented above work to reference data between sheets of a single Google Sheets document. If you need to link to another spreadsheet (sheet or tab in a different Google Sheets document), then you need IMPORTRANGE. It’s a Google Sheets feature that allows you to import a range of data from one spreadsheet to another. However, it doesn’t actually import data, it just references it.

To reference another sheet in a Google Sheets spreadsheet, follow these instructions:

Navigate to the spreadsheet you want to export data from. Copy its URL.
Open the sheet where you want to load the data.
Place the cursor in the cell where you want the imported data to appear.
Use the following syntax:

=IMPORTRANGE(“spreadsheet_url”, “range_string”)

Where spreadsheet_url is a link from Google Sheets to another sheet that you previously copied from where you want to extract the information.

range_string is an argument that is enclosed in quotes to define from which sheet and range the data is loaded.

For example:

Use “new students!B2:C” to name the sheet and range to get information from.
Use “A1:C10” to indicate a range of cells only. In this case, if you don’t define the sheet to import from, the default behavior is to load data from the first sheet in your spreadsheet.

You can also use

=IMPORTRANGE(B19, “B2:C6”)

if B19, in this case, implies the URL of the spreadsheet needed to link the data.

Note: using IMPORTRANGE anticipates that your destination spreadsheet must get permission to extract data from another document (the source). Every time you want to import information from a new source, you will be prompted to allow this action to take place. After you provide access, anyone with edit rights to your target spreadsheet will be able to use IMPORTRANGE to import data from the source. Access will be valid for as long as the person who provided it is present at the data source. To learn more about this Google Sheets feature, read our IMPORTRANGE tutorial.

In my case, my formula looks like this:

=IMPORTRANGE(“spreadsheet_url”,”new students !B2:C”)

=IMPORTRANGE(“spreadsheet_url”,”B2:C”)

because new students is the only sheet I have in my spreadsheet calculation.

However, the IMPORTRANGE solution has several drawbacks. The one I would mention relates to a negative impact on the overall performance of the spreadsheet. You can search Google IMPORTRANGE in the Google Community Forum to see various threads that explain the issue in more detail. Basically, the more IMPORTRANGE formulas you have in your worksheet, the slower your overall productivity will be. The spreadsheet will either stop working or take a long time to process and therefore display your data.

How to link two Google spreadsheets without the IMPORTRANGE function

Al using IMPORTRANGE is one of the most common methods to link two different Google Sheets, there are other options as well:

Google Sheets API. This it is an advanced method that is not suitable for most users, as it requires programming skills to connect one spreadsheet to another. However, it is also possible to use this method. Fortunately, the other two methods on our list are suitable for non-techies.
Third-party plugins. There are several plugins available in Google Workspace. Marketplace that can help you expand native functionality. For example, Sheetgo is one of the plugins you can use to link two Google Sheets without formulas. For this reason, Sheetgo is even called an “Importrange alternative”.
Data Integration Solutions. These are specialized tools that can automatically connect various applications and automate data flows. . It is also possible to use them to link two Google Sheets. One such solution is Coupler.io, which is also available as a plugin. I recommend giving it a try as it is very easy to use. I’ll explain how to link two different Google Sheets with Coupler.io in the next section.

#6 – How to reference another sheet in Google Sheets without formulas

As we noted at the beginning, there is no native way to import data from one sheet or spreadsheet to another in Google Sheets. So to do this job, you’ll need a Google Sheets plugin or ETL tool. Coupler.io offers both options out of the box!

Coupler.io is a data automation and analytics platform. Provides ETL tool to automate data exports from multiple sources to Google Sheets, Excel, or BigQuery.

With Coupler.io’s Google Sheets integration, you can link sheets and spreadsheets; Let’s see how it works.

How to Import Data from Another Google Sheet or Spreadsheet

Name your importer and complete the three steps: source, destination, and schedule.

Don’t have time to read? Watch our YouTube video on installing Coupler.io and setting up a Google Sheets importer.

Source

Connect your Google account, then in your Google Drive, select a spreadsheet and a sheet to import data from.You can select multiple sheets if you want to merge data from them into a master view.

Optionally, you can specify a range to export data, for example, A1:Z9, if you don’t need to extract data from a full sheet.

Go to target settings.

Target

Sign in to your Google account, then select a file in your Google Drive account and a sheet to upload data. You can create a new sheet by entering a new name.

Optionally, you can change the first cell where to import your range of data (cell A1 is set by default) and change the import mode of your data: replace your old information or add new rows below the latest imported entries. You can also turn on the Last Updated Column feature if you want to add a column to the spreadsheet with information about the last updated date and time.

Click Save & Run to run the import immediately and link your Google Sheet to another sheet. If you want to automate importing data on a schedule, see the instructions in the next section.

You can also use Coupler.io as a Google Sheets plugin for faster access to the tool in your spreadsheet. . For this, install it from the Google Workspace Marketplace and configure it as we described above.

How to sync two Google Sheets on a schedule without formulas

Coupler.io allows you to easily sync two Sheets Google calculation on a custom schedule. Once you complete the steps outlined above and your Coupler.io importer is almost ready, you can specify your preferences for updates.

Enable automatic update of data and customize the schedule.

Select Interval (from 15 minutes to every month)
Select Days of the week
Select Time preferences
Schedule Time zone

At the end, click Save and Run to sync two Google Sheets. Now the latest information from the source spreadsheet will be automatically transferred to the destination sheet during the next scheduled refresh.

How to reference a cell in another workbook in Google Sheets with Coupler.io

Coupler.io allows you to not only reference another workbook in Google Sheets, but also to import an exact cell range that only fits within the specified range. For example, you want to extract data from the range A1:C8 of one workbook and insert it into the range C1:E8 of another workbook. To do this, set up as described above, but also specify the following parameters: