How to Link Data Between Multiple Spreadsheets

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.

OR

=’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:
See Also:  How to create a cover letter for my resume

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 ” )

FILTER formula example

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.

three different tabs with sales data: Sales 1, Sales 2 and Sales 3

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.

p>

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 Also:  Get started with WordPress

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”)

OR

=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.
See Also:  How to create a website free of cost no upgrade

#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

Sign up to Coupler.io, click Add Importer and select Google Sheets as source and target application.

google sheet source destination

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

4-Source-section
  • 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

google sheets destination
  • 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.

5 backup schedule

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:

  • Source Workbook Range – Here you will specify the range of cells from which to import data. In our example, A1:C8
range source google sheets
  • Cell Address/Range of the destination workbook – here you will specify the range of cells to import data into. In our example, C1:E8
range target Google Sheets

Click Save and Run and welcome your data into the specified cell range.

cell range Google Sheets Reference</figure

Importing data isn’t the only thing you can do with Coupler.io. This ETL solution also allows you to consolidate or join data from different Google spreadsheets and even sources. Let’s look at a simple example below.

#7 – Extract data from multiple sheets of a single Google Sheets document

We have a Google Sheets document with five Sheets containing deal data for different years: 2016, 2017, 2018, 2019, and 2020:

Five sheets containing sales data from different years: 2016, 2017, 2018, 2019 and 2020

Instead of manually copying the data of each sheet or create a complex IMPORTRANGE Formula, we can simply enumerate all these sheets when setting up a Google Sheets importer as follows:

multiple sheets

Click Save and Run and the sheet data they will be extracted to our destination sheet. What are the main benefits? You will get a column indicating which sheet a dataset belongs to.Also, the title rows of each sheet except the first one are skipped, so you get a smooth combination of data.

multiple sheets merge result

If you want to do the same with IMPORTRANGE, this is so your formula should look like this:

={IMPORTRANGE(“1XTBc1P49IPqZoWQldeOphvU2fa5gguBSW6poS8x5rW8″,”2016!A1:EU30”); IMPORT RANGE(“1XTBc1P49IPqZoWQldeOphvU2fa5gguBSW6poS8x5rW8″,”2017!A2:EU572”); IMPORTRANGE(“1XTBc1P49IPqZoWQldeOphvU2fa5gguBSW6poS8x5rW8″,”2018!A2:EU972”); IMPORT RANGE(“1XTBc1P49IPqZoWQldeOphvU2fa5gguBSW6poS8x5rW8″,”2019!A2:EU1243”); IMPORTRANGE(“1XTBc1P49IPqZoWQldeOphvU2fa5gguBSW6poS8x5rW8″,”2020!A2:EU204”)}

Data extracted from multiple sheets using IMPORTRANGE

It is important to specify exact data ranges like 2018!A2:EU972, otherwise you will get multiple blank rows between the data. And don’t expect to get your data right away – IMPORTRANGE works quite a long time. In our case, we had to wait a few minutes before the formula pulled the data.

Which option works best in Google Sheets to link to another sheet or tab?

They’ve put together a comparison table below that briefly explains the pros and cons of using the native functionality versus Coupler.io when connecting data between spreadsheets.

Native functionality of Coupler.io Google SheetsCoupler.ioGoogle Sheets Data Link TypeBig Data strong>Frequency of updatesTime to process calculationsPerformance in heavily formula-heavy spreadsheetsDecent Great!Permission/access management for importing data Automatic data backup

If interested or on comparing IMPORTRANGE vs. Coupler.io in terms of spreadsheet linking, see our blog post dedicated to IMPORTRANGE Google Sheets.

Google Sheets to Google Sheets is not the only integration provided By Coupler.io.

Can I import data into Google Sheets from another sheet, including the formatting?

Unfortunately, none of the above options will allow you to import the formatting of the sheet(s). ) cell(s) when referencing another Google Sheets workbook. The logic of IMPORTRANGE, FILTER, and other native Google Sheets options does not imply the actual transfer of data. They only reference and display data from the source cells. Coupler.io is the only option that copies the data from the source, but only imports the raw data without formatting. At the same time, you can use Coupler.io to link Excel files as well as Excel and Google Sheets.

BUT you can always use the benefits of Google Apps Script to create a custom function for your needs . . For example, the following script will allow you to transfer data from one sheet or spreadsheet to another:

function importTable() { // Source spreadsheet var srcSpreadSheet = SpreadsheetApp.openById(“insert-id-of-the -source- spreadsheet”); var scrSheet = srcSpreadSheet.setActiveSheet(srcSpreadSheet.getSheetByName(“insert-the-source-sheet-name”)); // Destination spreadsheet var destSpreadSheet = SpreadsheetApp.openById(“insert-id-of-destination-spreadsheet”); var destSheet = destSpreadSheet.setActiveSheet(destSpreadSheet.getSheetByName(“insert-the-destination-sheet-name”)); targetsheet.clear(); // Get data and format from source sheet var range = scrSheet.getRange(1, 1, 48, 32); var values ​​= range.getValues(); var background = range.getBackgrounds(); var bands = range.getBandings(); var mergedRanges = range.getMergedRanges(); var fontColor = range.getFontColors(); var font family = range.getFontFamilies(); var fontLine = range.getFontLines(); var fontSize = range.getFontSizes(); var fontStyle = range.getFontStyles(); var fontWeight = range.getFontWeights(); var horAlign = range.getHorizontalAlignments(); var textStyle = range.getTextStyles(); var vertAlign = range.getVerticalAlignments(); // Put data and format on the destination sheet var destRange = destSheet.getRange(1, 1, 48, 32); destRange.setValues(values); destRange.setBackgrounds(background); destRange.setFontColors(fontColor); destRange.setFontFamilies(fontFamily); destRange.setFontLines(fontLine); destRange.setFontSizes(fontSize); destRange.setFontStyles(fontStyle); destRange.setFontWeights(fontWeight); destRange.setHorizontalAlignments(horAlign); destRange.setTextStyles(textStyle); destRange.setVerticalAlignments(vertAlign); // Iterate to put merged ranges in place for (var i = 0; i < mergedRanges.length; i++) { destSheet.getRange(mergedRanges[i].getA1Notation()).merge(); } // Iterate to get the column width of the source target for (var i = 1; i < 18; i++) { var width = scrSheet.getColumnWidth(i); targetsheet.setColumnWidth(i, width); } // Iterate to get the row height of the source destination for (var i = 1; i < 27; i++){ var height = scrSheet.getRowHeight(i); destSheet.setRowHeight(i, height); } }

You have to go to Extensions > Application Script. Then insert the script into the Code.gs file and specify the required parameters:

  • IDs of the source and target spreadsheets
  • Names of the source and target spreadsheets destination

(If you are importing data between sheets, the ID of the source and destination worksheets will be the same)

Apps Script to import data into Google Sheets from another sheet, including formatting

When you’re ready, click Run and your data, including formatting, will be imported into the destination sheet.

Note: This solution may not be suitable for your project, so you’ll need to update the script according to your needs.

What to choose to reference data from other sheets or tabs

There is no one-size-fits-all solution and you have to be careful when going one way or the other. Whether you’re looking to link sheets, spreadsheets, create combined views, or back up documents, be sure to consider all the pros and cons of both and choose the appropriate option to achieve the best result.

If you only have a few records in your spreadsheet and small formulas, then you may want to go for a formula-based approach that includes the IMPORTRANGE provided by Google Sheets. This will work for regular reports or low-level analysis.

However, if you have a lot of data and there are multiple calculations in your document, you should opt for the data import method instead of referencing it. Coupler.io will be a more stable solution in this case. It will provide fail-safe data transfer and ensure that you have access to the data even if the data source is corrupted or unavailable. Choose wisely and good luck!

Back to Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *