Back to Blog

How to Link Data Between Multiple Spreadsheets

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

Another frequent case may raise a requirement for a backup spreadsheet that would be copying 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 struggling to find 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 of doing so. At the end, I will provide a full comparison of the approaches mentioned for you to be able to evaluate and choose from.

How to reference data from other sheets (tabs)

Option 1

Use the instructions below to link data between Google sheets:

  • Open a sheet in Google Sheets.
  • Place your cursor in the cell where you want the imported data to show up.
  • Use one of the formulas below:
=Sheet1!A1

where Sheet1 is the name of your referenced sheet, followed by an exclamation mark, and A1 is a specified cell that you want to import data from.

Or 

='Sheet two'!A1

where you put the name of the sheet in single quotes if it includes spaces or other characters like ):;”|-_*&, etc.

In my case, the ready – to – use formula will look like

='list of students'!B4

Note: if you want to import the range of cells from one sheet to another , just place your cursor on the cell in your data destination worksheet that already contains one of the above-mentioned formulas (='Sheet two'!A1 or =Sheet1!A1). Then drag it in the direction of your desired range. For example, if you drag it down , the data from these cells will automatically be displayed in your spreadsheet. The same can be done in any other possible direction of your current document.

Option 2

Follow this guide to reference data from the current and other sheets:

  • Open a sheet in Google Sheets.
  • Place your cursor in the cell where you want the referenced data to show up.
  • Use one of the formulas below : 

To link data from the current sheet:

={A1:A3}

Where A1:A3 is the range of cells from your current active sheet. Use curly brackets for this argument.

To pull Google Sheets data from another sheet, use:

={Sheet1!A1:A3}

Where Sheet1 is the name of your referenced sheet and A1:A3 is a specified range of cells that you want to import data from. Use curly brackets for this argument.

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

To upload the entire column from another sheet:

={Sheet1!A:A}

Where Sheet1 is the name of your referenced sheet and A:A is a range that specifies that you will pull the data from the A column. Use curly brackets for this argument.

In my case, the ready-to-use formula will look like

={'list of students'!B1:E11}

How to reference another spreadsheet (file) via IMPORTRANGE

To upload data from another spreadsheet, follow these instructions: 

  • Go to the spreadsheet you want to export data from. Copy its URL.
  • Open the sheet you want to upload data to.
  • Place your cursor in the cell where you want your imported data to appear.
  • Use the syntax as described below: 
=IMPORTRANGE("spreadsheet_url", "range_string")

Where spreadsheet_url is a link to the spreadsheet that you copied earlier where you want to pull the information from. 

range_string is an argument that you put in quotes to define what sheet and range to upload data from. 

For example:

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

You may also use

=IMPORTRANGE(B19, "B2:C6")

if A2, in this case, entails the necessary spreadsheet URL to link data from.

Note: the use of IMPORTRANGE anticipates that your destination spreadsheet must get permission to pull data from another document (the source). Every time you want to import information from a new source, you will be required to allow this action to happen. After you provide access, anybody with edit rights in your destination spreadsheet will be able to use IMPORTRANGE to import data from the source. The access will be valid for the time a person who provided it is present in the data source For more about this Google Sheets function, read our IMPORTRANGE Tutorial.

In my case, my formula looks like this :

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

Or 

=IMPORTRANGE("spreadsheet_url","B2:C")

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

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

How to reference another spreadsheet (file) via Coupler.io

Coupler.io is a Google Sheets add-on to pull data from various sources, including other spreadsheets, CSV files, Airtable, and many more.

No time to read? Watch our YouTube video of how to install Coupler.io and set up a Google Sheets importer.

1. Install the add-on

To start using the tool, please go to the GSuite Marketplace and install the add-on.

  • Coupler.io will ask you for permission to start the installation. 
  • Then choose the account you want to continue using the add-on with.
  • Look through the rights that you give Coupler.io to allow data importing. 
  • Open a spreadsheet and navigate to the add -ons in the upper menu bar.
  • Locate Coupler.io and click Open Dashboard.

2. Add Google Sheets importer

Once you have installed the add-on, open a spreadsheet and navigate to the Add-ons menu. Locate Coupler.io and click Open Dashboard.

Click +Add Importer and select Google Sheets from the list. Set up the Google Sheets importer as follows:

Click +Add Importer and select Google Sheets from the list. Set up the Google Sheets importer as follows:

1. Fill out the “Title” field

Add the name of your data source.

2. Set up your data source

2.1. Add your Google Account
  • Click Connect.
  • Pick the Google Account you want to connect to.
  • Sign in to the chosen Google Account.
  • Coupler.io will need access to view your Google spreadsheets. Click Allow to grant access.
2.2. Fill out the “Spreadsheet URL” field

Type in the link to your data source where Coupler.io will pull the data from.

To get a link to a specific sheet in your Google Sheets spreadsheet, navigate to the sheet and copy its URL.

3. Set up your data destination

3.1. Fill out the “Sheet name” field

Name the sheet, which will be receiving data. 

4. Configure importer’s settings

4.1. Enable the Automatic data refresh
  • Select Interval
  • Select Days of week
  • Specify Time range

Check out more about the Automatic data refresh.

3. Link multiple sheets via one importer

One setting that is worth mentioning separately is the Sheet name(s) field. 

Click Show advanced in the Source section to expand it.

This field is used to link data from multiple sheets into one using a single importer. Therefore, it is better to make sure that the column headers (its titles) are identical across all sheets that are going to be merged.

Note: the titles of sheets to consolidate are to be entered from a new line. 

Please refer to our Knowledge base page to read about additional fields and settings.

4. Save & Run your Google Sheets importer

Click Save & Run to save the integration and run the initial import right away.

Comparing IMPORTRANGE vs. Coupler.io 

Below I have put together a comparison table that briefly explains the pros and cons of the use of IMPORTRANGE vs Coupler.io when connecting data between spreadsheets.

IMPORTRANGECoupler.io
Small data volumesGreat!Great!
Big data volumesIMPORTRANGE may show errors or keep loading data for a long time.Great!
Frequency of updatesGreat! 

Almost in real-time
Supports manual (any time) and automatic data refresh: once per 1 hour, 3 hours, 6 hours, 12
Time to process calculationsIMPORTRANGE is a formula and it takes some time to process calculations which may slow down the general performance of a spreadsheet.No calculations are performed on the spreadsheet side. Coupler.io pulls the static data over to your worksheet.
Performance in spreadsheets heavily loaded by formulasIf the total number of formulas in a spreadsheet (including IMPORTRANGE) draws nearer to fifty, the loading speed and the general performance of the document will deteriorate.Great!

It makes no difference for Coupler.io how many formulas you have in your spreadsheet. It will not slow down your worksheet.
Managing permissions / access to import dataGranting permissions is performed per every IMPORTRANGE formula separately, which makes it difficult to manage them in bulk.Great!

Managing account connections is available under Coupler.io GSheets importer settings. So, you just create one connection and use it across the entire document.
Automatic backup of dataIMPORTRANGE syncs the data source and data destination sheets, showing the live data in the latter. So, once the information in the source disappears, it gets automatically removed from your destination sheet as well.Great!

Coupler.io can automatically backup your data and keep it safe in a destination sheet.

It’s time to make a choice!

There is no one-size-fits-all solution, and you have to be careful when going one way or another. Whether you are looking to link sheets, spreadsheets, create combined views or backup documents , be sure to consider all the advantages and disadvantages of both and pick the right option for you to achieve the best result.

If you only have few records in your spreadsheet and little formulas, then you may want to go for IMPORTRANGE. But when you possess lots of data and there are multiple calculations in your document, then Coupler.io will be a more stable solution in this case.

Back to Blog

Comments are closed.

Access your data
in a simple format for free!

Start Free