How To Filter Info From One Google Sheet To Another

There comes a fourth dimension in the life of every Google Sheets user when you lot demand to reference a certain data range from some other sheet, or even a spreadsheet, to create a combined main view of both. This volition let you consolidate data from multiple worksheets in a single ane.

Another frequent case may raise a requirement for a backup spreadsheet that would be copying values and format from the source file, merely not the formulas. Some of the users may besides desire their master document to update automatically, on a fix schedule.

So, if you are struggling to find the solution to the above tasks, go on reading this article. You'll discover tips on how to link data from other sheets and spreadsheets, equally well as discover alternative ways of doing so. At the finish, I will provide a full comparing of the approaches mentioned for you to be able to evaluate and choose from.

How to reference data from other sheets (tabs)

If Excel spreadsheets are in your focus, so head on to our blog post about how to link sheets in Excel.

Option 1: How to import data from one Google canvas to another

Use the instructions beneath to link information between Google sheets:

Open up a canvass in Google Sheets.
Place your cursor in the cell where yous want the imported data to show up.
Employ one of the formulas beneath:

=Sheet1!A1

where Sheet1 is the verbal name of your referenced sheet, followed by an exclamation mark, and A1 is a specified cell that yous desire to import information from.

='Sheet two'!A1

where y'all put the name of the sheet in single quotes if information technology includes spaces or other characters like ):;"|-_*&, etc.

In my case, the fix-to-use formula will look similar

='list of students'!B4

Example of linking sheets in Google Sheets

Notation: if yous want to import the range of cells from one sheet to another , just identify your cursor on the cell in your data destination worksheet that already contains i of the to a higher place-mentioned formulas (='Canvass two'!A1 or =Sheet1!A1). And then drag it in the management of your desired range. For example, if yous drag it down , the information from these cells volition automatically exist displayed in your spreadsheet. The same can be done in any other possible management of your current document.

Option 2: How to import data field from the current or another Google sail

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

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

To link data from the electric current canvass:

={A1:A3}

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

To pull Google Sheets information from some other sheet, use:

={Sheet1!A1:A3}

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

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

Option 3: How to import a column from 1 Google sheet to some other

To upload the entire column from another canvas:

={Sheet1!A:A}

Where Sheet1 is the name of your referenced canvas 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 example, the prepare-to-use formula will look like

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

Pick 4: How to import data from multiple sheets into one column

Let's review an example when one needs to link information from several columns in different sheets into one.

In my example, I have 3 different tabs with sales data: Sales i, Sales ii and Sales 3.

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

My task is to collect all customer names in the sheet called "All customers".

To practise information technology, I'll utilize this formula:

={   "All Customers";    FILTER('Sales 2'!C2:C, LEN('Sales 2'!C2:C) > 0);    FILTER('Sales 1'!C2:C, LEN('Sales i'!C2:C) > 0);   FILTER('Sales 3'!C2:C, LEN('Sales three'!C2:C) > 0) }

Where:

"All Customers" – is a given name of my cavalcade,
FILTER('Sales 1'!C2:C, LEN('Sales i'!C2:C)> 0) – this expression means that I have all data from cavalcade C of the "Sales 1", excluding the values that are equal or less than 0.

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

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 tin can change the names of my data source sheets (where I accept information from), and they will automatically be updated in the formula!

Come across how information technology works:

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

Option 5: Import information from i Google sheet to another based on criteria

Let's say, yous desire to filter your information set up past a specific criteria and import the filtered values into another canvas. You can do this using the FILTER office that was featured in the case above. Here is the syntax:

                =FILTER(data_set,criterium1, criterium2,...)

data_set – a range of cells to filter.
criterium – the criteria to filter the information set.

As an example, we're going to filter users by country, Australia, and import the results in another canvas.

Import data from one Google sheet to another based on criteria

Here is what our formula will look like:

=filter('Source data'!A2:A,'Source information'!C2:C="Commonwealth of australia")

Read near the Google Sheets FILTER function to discover more filtering options.

How to reference another spreadsheet (file) via IMPORTRANGE

To upload data from some other spreadsheet, follow these instructions:

Get to the spreadsheet you want to export data from. Copy its URL.
Open the sheet you desire to upload information to.
Place your cursor in the cell where you want your imported data to appear.
Utilize the syntax as described beneath:

=IMPORTRANGE("spreadsheet_url", "range_string")

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

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

For instance:

Apply "new students!B2:C" to name the sheet and range to get information from.
Use "A1:C10" to country a range of cells only. In this example, if y'all don't define the sheet to import from, the default behavior is to upload data from the first canvass in your spreadsheet.

Yous may likewise apply

=IMPORTRANGE(B19, "B2:C6")

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

Note: the use of IMPORTRANGE anticipates that your destination spreadsheet must become permission to pull data from another document (the source). Every time you want to import information from a new source, y'all volition exist required to allow this action to happen. Later on you provide access, anybody with edit rights in your destination spreadsheet volition be able to use IMPORTRANGE to import data from the source. The access will be valid for the time a person who provided information technology is present in the information source. For more about this Google Sheets office, 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 merely sheet I have in my spreadsheet.

Nevertheless, the IMPORTRANGE solution has several drawbacks. The one I would mention relates to a negative bear on on the overall spreadsheet performance. Y'all tin can google for IMPORTRANGE in the Google Customs forum to see a number of threads that explain the event in more item. Basically, the more IMPORTRANGE formulas you have in your worksheet, the slower the overall productivity volition be. The spreadsheet volition either stop working or require a lot of time to process, and therefore display, your data.

How to reference some other spreadsheet (file) via Coupler.io

Coupler.io is a tool that allows users to pull data from diverse sources, including other spreadsheets, CSV files, Airtable, and many more to Google Sheets, Excel, or BigQuery.

Sign upwardly to Coupler.io and start your data integration journey by creating your first importer. Click the "Add together importer" button, give a title to your importer and complete the three steps: source, destination, and schedule.

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

Source

Choose Google Sheets equally a source app from the list.
Connect to your Google account or pick one from the drop down list if you lot've already continued some.
Select a file on your Google Drive to transfer information from.
Select one or several sheets to export information.
- You lot can employ this field to link data from multiple sheets with the same structure into ane. Therefore, it is better to make sure that the column headers (its titles) are identical beyond all sheets that are going to exist merged. Enter the titles of sheets to consolidate from a new line.

Click "Jump to Destination Settings" if yous're okay that the entire data ready will exist imported from your sail (s). Even so, you can cull a range in the spreadsheet y'all want to export data from, i.due east. A1:Z9. To do this, click "Go along" and specify the range in the optional field.

Destination

Choose Google Sheets as the destination application yous want to relieve information.
Connect to your Google business relationship.
Select a file on your Google Drive to transfer data to.
Select an existing canvas, or enter a name to create a new one.
If you want to modify the start cell where to import your data range, specify your value in the Prison cell address field. The A1 cell is set by default.
Cull the import mode for your data: you can replace your previous information or suspend new rows under the terminal imported entries.

Toggle on the Final updated cavalcade feature, if yous want to add together a cavalcade to the spreadsheet with the information near the last date and time refresh.

Y'all can run the import right away if you click "Salvage and Run". If yous want to automate data import on a schedule, toggle on the Automated data refresh and customize the schedule.

Schedule

Select Interval (from fifteen minutes to every calendar month)
Select Days of the week
Select Fourth dimension preferences
Schedule Fourth dimension zone

In the end, click "Save and Run" to run your first importer.

Note: Yous can also apply Coupler.io every bit a Google Sheets add-on to accept faster access to the tool in your spreadsheet. For this install it from the Google Workspace Marketplace and set up as we described above.

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

How information technology works: Pull information from multiple sheets of a single Google Sheets dr.

We take a Google Sheets doc with five sheets that contain information about deals for different years: 2016, 2017, 2018, 2019, and 2020:

Five sheets that contain data about deals for different years: 2016, 2017, 2018, 2019 and 2020

Instead of manually copying data from each sheet or building a complex IMPORTRANGE formula, we tin but specify the names of sheets equally follows:

2017 2018 2019 2020

Click "Salve & Run" and the data from the sheets will be pulled into our destination sheet. What are the main benefits? Y'all'll get a cavalcade indicating which sheet a data set belongs to. Also, the title rows from each sheet except for the first 1 are skipped, so you get a smooth merge of data.

If you wanted to do the same using IMPORTRANGE, hither is what your formula should look like:

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

Data pulled from multiple sheets using IMPORTRANGE

Information technology's of import to specify verbal data ranges like 2018!A2:EU972, otherwise yous'll go multiple blank rows betwixt the information. And do not expect to get your data correct abroad – IMPORTRANGE works pretty long. In our case, we had to wait a few minutes before the formula pulled in the data.

Comparing IMPORTRANGE vs. Coupler.io

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

	IMPORTRANGE	Coupler.io
Small information volumes	Slap-up!	Bully!
Big data volumes	IMPORTRANGE may testify errors or keep loading information for a long time.	Great!
Frequency of updates	Neat! Almost in existent-time	Supports manual (whatsoever time) and automatic data refresh: once per i hour, 3 hours, 6 hours, 12
Fourth dimension to process calculations	IMPORTRANGE is a formula and it takes some time to process calculations which may slow down the full general performance of a spreadsheet.	No calculations are performed on the spreadsheet side. Coupler.io pulls the static information over to your worksheet.
Performance in spreadsheets heavily loaded by formulas	If the total number of formulas in a spreadsheet (including IMPORTRANGE) draws nearer to 50, the loading speed and the full general performance of the document will deteriorate.	Slap-up! It makes no difference for Coupler.io how many formulas yous take in your spreadsheet. It will non tedious downwards your worksheet.
Managing permissions / access to import information	Granting permissions is performed per every IMPORTRANGE formula separately, which makes it difficult to manage them in bulk.	Great! Managing business relationship connections is available under Coupler.io GSheets importer settings. And so, yous but create one connection and use information technology beyond the unabridged certificate.
Automatic backup of data	IMPORTRANGE 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 likewise.	Bully! Coupler.io tin automatically backup your data and keep it safe in a destination canvas.

Can I import information in Google Sheets from another canvas including formatting?

Unfortunately, neither of the options above volition let you import the formatting of the jail cell(s) yous reference to. The logic of IMPORTRANGE, FILTER and other Google Sheets native options does not entail the actual transfer of data. They but reference and display information from the source cells. Coupler.io is the only option that copies the data from the source, but it only imports the raw information without any formatting. At the same time, you can use Coupler.io to link Excel files, as well every bit Excel and Google Sheets.

Merely, you can always use the benefits of Google Apps Script to create a custom office for your needs. For case, the following script will let you 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-the-destination-spreadsheet");   var destSheet = destSpreadSheet.setActiveSheet(destSpreadSheet.getSheetByName("insert-the-destination-sheet-name"));   destSheet.clear();     // Become data and formatting from the source canvass   var range = scrSheet.getRange(1, 1, 48, 32);     var values = range.getValues();   var groundwork = range.getBackgrounds();   var banding = range.getBandings();   var mergedRanges = range.getMergedRanges();   var fontColor = range.getFontColors();   var fontFamily = 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 formatting in the destination sheet   var destRange = destSheet.getRange(1, 1, 48, 32);     destRange.setValues(values);   destRange.setBackgrounds(groundwork);   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 through to put merged ranges in place   for (var i = 0; i < mergedRanges.length; i++) {     destSheet.getRange(mergedRanges[i].getA1Notation()).merge();   }     // Iterate through to go the column width of the source destination   for (var i = 1; i < 18; i++) {     var width = scrSheet.getColumnWidth(i);     destSheet.setColumnWidth(i, width);   }     // Iterate through to become the row heighth of the source destination   for (var i = 1; i < 27; i++){     var height = scrSheet.getRowHeight(i);     destSheet.setRowHeight(i, height);   }   }

Yous demand to go Tools > Script editor. So insert the script in the Lawmaking.gs file and specify the required parameters:

ID of the source and destination spreadsheets
Names of the source and destination sheets

(If you're importing data betwixt sheets, the source and destination spreadsheet ID will be the same)

Apps Script for importing data in Google Sheets from another sheet including formatting

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

Note: This solution may not be a fit for your project, so yous'll need to update the script whatever you require.

It's time to make a choice!

In that location is no one-size-fits-all solution, and you take to be careful when going i way or some other. Whether you are looking to link sheets, spreadsheets, create combined views or fill-in documents, exist sure to consider all the advantages and disadvantages of both and selection the right selection for you to accomplish the best result.

If you only accept few records in your spreadsheet and little formulas, then you may want to get for IMPORTRANGE. Merely when you lot possess lots of information and there are multiple calculations in your document, then Coupler.io will be a more than stable solution in this case.

Back to Blog

Access your data
in a unproblematic format for gratis!

Start Free