[et_pb_section fb_built=”1″ _builder_version=”4.16″ da_disable_devices=”off|off|off” global_colors_info=”{}” theme_builder_area=”post_content” da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on”][et_pb_row _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” hover_enabled=”0″ global_colors_info=”{}” theme_builder_area=”post_content” sticky_enabled=”0″]<\/p>\n
The <\/span>IMPORTRANGE<\/span><\/a> Google Sheets function is <\/span>the only function that can transfer data between spreadsheets<\/b>. It lets you easily import data from one Google Sheets file to another without the need for manual work. Saving you time, and removing the risk of human error.<\/span><\/p>\n However, <\/span>IMPORTRANGE does have complex requirements and a few limitations<\/b>. It is an easy function for spreadsheet masters, but for inexperienced users it can be confusing.<\/span><\/p>\n In this article, we\u2019ll cover possible uses of IMPORTRANGE, how to implement it, as well as its pros and cons. We\u2019ll also see<\/span> how the Sheetgo add-on can be the perfect <\/b>IMPORTRANGE<\/b><\/a> alternative <\/b><\/a>to level up your spreadsheet data management.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”what” _builder_version=”4.27.6″ _module_preset=”default” hover_enabled=”0″ global_colors_info=”{}” theme_builder_area=”post_content” sticky_enabled=”0″]<\/p>\n IMPORTRANGE is a function that imports values from a range of cells from one spreadsheet to another<\/strong>. All you need is access to the source spreadsheet and its URL.<\/p>\n Given Google Sheets\u2019 cloud-based infrastructure, IMPORTRANGE is very simple. Every file has a unique URL, so you can reference individual files by including the file\u2019s \u201ckey\u201d (more on this later). You then add specificity by referencing a page and a cell range within this file.<\/p>\n IMPORTRANGE is great for one-time and sporadic data imports from one sheet to another<\/strong>. You can use it to carry out simple data transfers such as:<\/p>\n Despite all its power, it also has some drawbacks. For example, it\u2019s not recommended to perform multiple imports across multiple spreadsheets.<\/p>\n [\/et_pb_text][et_pb_video src=”https:\/\/www.youtube.com\/watch?v=yjEeix2Du30″ _builder_version=”4.24.1″ _module_preset=”default” min_height=”355.3px” custom_margin=”30px||30px||false|false” custom_padding=”||0px|||” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_video][et_pb_text module_id=”how” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n To learn how to use IMPORTRANGE in Google Sheets, it\u2019s important to understand its syntax and its required parameters.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”syntax” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =IMPORTRANGE(spreadsheet_url, range_string)<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As you can see, to import a range of data from another spreadsheet, you need only two things:<\/span><\/p>\n Both of these parameters are pretty self explanatory but they are still worth going over.<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n There are two ways to include this information:<\/span><\/p>\n Below is a complete URL with the spreadsheet key in bold.<\/span><\/p>\n https:\/\/docs.google.com\/spreadsheets\/d\/<\/span>1sqWKxq4mbsJVqy2mZbeV2H7MWF9TZ2V-yU6Ne0NGL0g<\/b>\/<\/span><\/p>\n If you want <\/span>to only copy the spreadsheet<\/b> key<\/strong> this is what the formula would look like:<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =IMPORTRANGE(\u201c1sqWKxq4mbsJVqy2mZbeV2H7MWF9TZ2V-yU6Ne0NGL0g\u201d, range_string)<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The range string is <\/span>the cell range that you want to import<\/b> from the source spreadsheet. You can specify the sheet name, or you can omit it to have IMPORTRANGE import the cell range from the first sheet.<\/span><\/p>\n As with the URL, <\/span>the range string must be included between quotation marks<\/b>. This is what the formula would look like with a specific sheet name within the range string:<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =IMPORTRANGE(\u201c1sqWKxq4mbsJVqy2mZbeV2H7MWF9TZ2V-yU6Ne0NGL0g\u201d, \u201cSheet2!A:B\u201d)<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”importrange” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n For this example, let\u2019s compare sales team performance in 2020 and in 2021 to generate a performance report.<\/span><\/p>\n First thing to do is to use IMPORTRANGE to transfer the 2020 sales performance column into the 2021 sales sheet. This will enable us to compare the data directly.<\/span><\/p>\n The screenshot below shows the 2020 data to transfer. The exact range we want to import is \u201cSales 2020!C2:C19\u201d.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2022\/06\/IMPORTRANGE-GS-1.png” alt=”IMPORTRANGE Google Sheets 1″ title_text=”IMPORTRANGE GS 1″ align=”center” _builder_version=”4.24.1″ _module_preset=”default” width=”100%” custom_margin=”30px||30px||true|false” border_radii=”on|12px|12px|12px|12px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In this case, the entire function should be:<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =IMPORTRANGE(\u201c1hrH0ix0aqO9okS4T5mL_Atte8D_iKOzmSF4h_weDDhA\u201d,\u201dSales 2020!C2:C19\u2033)<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The first time the function runs it will return an error and you will need to link your two sheets. Click <\/span>Allow access<\/b> to fix it.<\/span><\/p>\n The function imports data and displays it in the sheet, as shown below.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2022\/06\/IMPORTRANGE-GS-2.png” alt=”IMPORTRANGE Google Sheets 2″ title_text=”IMPORTRANGE GS 2″ align=”center” _builder_version=”4.24.1″ _module_preset=”default” width=”100%” custom_margin=”30px||30px||true|false” border_radii=”on|12px|12px|12px|12px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now that the two datasets are in one place, I can calculate key metrics for my performance report. For example I could analyze sales trends between 2020 and 2021.<\/span><\/p>\n Keep in mind that data transfers occur almost instantly for small datasets. But naturally, larger datasets will take longer.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”import” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n IMPORTRANGE can\u2019t import data from another sheet within the same spreadsheet.<\/b> This constraint can be frustrating, especially if you’re doing a lot of work within a single spreadsheet. Fortunately, there’s a few ways to overcome this limitation.<\/span><\/p>\n One of these alternatives is to use the FILTER function. The FILTER function enables you to extract specific data from a range based on specified criteria. This makes it a versatile tool for importing data from other sheets within the same spreadsheet.<\/span><\/p>\n Let\u2019s pretend the data we wanted to import in the example above was on the same spreadsheet.<\/span><\/p>\n We could transfer it to another sheet with this method. We would select the same range, then set the criteria for the filter function. This criteria would have to match all the cells, which could be achieved by bringing all cells that aren\u2019t empty.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =FILTER(‘Sales 2020’!C2:C19, ‘Sales 2020’!C2:C19 <> “”)<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n This isn\u2019t really what the FILTER function is meant for, but it is effective. To learn more about this powerful tool check out\u00a0<\/span>How to use the FILTER function in Google Sheets<\/span><\/a>.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”troubleshooting” _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n While IMPORTRANGE is a powerful function, it frequently returns errors. These issues can range from permission problems to incorrect data ranges.<\/span><\/p>\n Some common errors include:<\/span><\/p>\n To effectively troubleshoot and resolve these issues <\/span>read our detailed guide on IMPORTRANGE errors<\/span><\/a>.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”combining” _builder_version=”4.24.1″ _module_preset=”default” hover_enabled=”0″ global_colors_info=”{}” theme_builder_area=”post_content” sticky_enabled=”0″]<\/p>\n There are three functions that pair really well with this function: QUERY, FILTER and VLOOKUP. All of them allow you to alter the results of IMPORTRANGE to suit your needs.<\/span><\/p>\n We will go over them, but first we will explore when to use each combination.<\/span><\/p>\n <\/span><\/p>\n <\/span><\/p>\nWhat is IMPORTRANGE in Google Sheets?<\/h2>\n
\n
How to use IMPORTRANGE in Google Sheets<\/span><\/h2>\n
Syntax of IMPORTRANGE<\/h3>\n
\n
1. Spreadsheet URL<\/span><\/h4>\n
\n
2. Range string<\/span><\/h4>\n
IMPORTRANGE example<\/span><\/h3>\n
How to Import Data from Another Sheet<\/span><\/h2>\n
Troubleshooting IMPORTRANGE errors<\/span><\/h2>\n
\n
Combining IMPORTRANGE with other functions<\/span><\/h2>\n